Question about triggers

Question

Question about triggers

AlexSQLForums

SSChampion

Points: 14277
More actions
March 24, 2008 at 2:34 pm

#71721

Hi All
I have 2 tables
Employees
IDM
Employees contains 2 triggers.
1 insert and 1 update which insert or update IDM.
Whenever a new employee is created in Employees table IDM table also creates a record via trigger.
Novell writes password and email into IDM table from e-directory.
Now IDM table has a update trigger which updates Employees table.
Here is the logic of the trigger
1. Alter trigger to disable 2 triggers in Employees table.
2. An update statement to update existing rows in Employees table.
3. Alter trigger to enable 2 triggers in Employees table.
when i test the triggers in query analyzer everything works fine. I can easily change the username or password or email address in IDM and the triggers work fine, the records are updated in Employees table
But when we run a test from Novell we cannot update IDM table.
Also once i disable the IDM update trigger everything works and novell is able to update the IDM.
Do these triggers somehow block update statements?
Here is the code for the update trigger in IDM
CREATE TRIGGER tr_idm_update ON dbo.IDM
For UPDATE
AS
--trun off triggers
alter table employees
disable trigger trg_insert_idm
alter table employees
disable trigger trg_update_idm
Update Employees
set LDAPuserID = inserted.userid,
[EmployeeEmailAddress] = inserted.[EmailAddress],
EmployeeIntranetPassword = inserted.[Password]
from Employees, inserted
where Employees.EmployeeID = inserted.employeeID
--trun on triggers
alter table employees
enable trigger trg_insert_idm
alter table employees
enable trigger trg_update_idm
Alex S

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ksullivan SSCrazy Points: 2346 More actions · Answer 1

Why are you disabling your employee table triggers? Won't that make it possible for records to be added & updated that never get your idm table? Are you trying to avoid indirect recursion? Isn't there a setting for that (see BOL)?

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

I don't believe that you can disable the trigger in a trigger. The problem with Novell doing it could be rights.

However the solution here is probably not to disable the triggers, but perhaps let them run and then execute something else that undoes the changes you don't want, or even use an INSTEAD OF TRIGGER to avoid this one.

The big issue is that triggers are supposed to fire every time. Why don't you want it here and if you don't want it here, then perhaps you should not have a trigger at all.

AlexSQLForums SSChampion Points: 14277 More actions · Answer 3

I'm disabling triggers because if i don't they will keep firing indefinitely since both tables IDM and Employees contain triggers which update each other. But the IDM trigger only updates a few columns.

I will try to use Instead of Trigger.

Thanks

Alex S

mrpolecat SSCertifiable Points: 7014 More actions · Answer 4

If there is a link between the tables have your trigger insert where the key does not exist, then it can fire every time but will only insert a new record when necessary.

AlexSQLForums SSChampion Points: 14277 More actions · Answer 5

ksullivan (3/24/2008)
Why are you disabling your employee table triggers? Won't that make it possible for records to be added & updated that never get your idm table? Are you trying to avoid indirect recursion? Isn't there a setting for that (see BOL)?

ksullivan i turned off nested triggers and removed alter statements and it works.

Thanks for pointing out indirect recursion.

Alex S

ksullivan SSCrazy Points: 2346 More actions · Answer 6

You're welcome. Thanks for letting us know it worked for you.