Recursive Update trigger

Recursive Update trigger

Post by Jason Garlan » Mon, 16 Mar 1998 04:00:00



I'd like to change a "LastUpdatedBy" column on a table in an insert and
update trigger.

I know I can use SUSER_NAME(SUSER_ID) to obtain the user name.  But if I
Update the table in the trigger with this data won't it refire the trigger
again and go into an endless loop?

Any help would be appreciated.

Jason Garland

 
 
 

Recursive Update trigger

Post by Laszlo Kalma » Mon, 16 Mar 1998 04:00:00



> I'd like to change a "LastUpdatedBy" column on a table in an insert and
> update trigger.

> I know I can use SUSER_NAME(SUSER_ID) to obtain the user name.  But if I
> Update the table in the trigger with this data won't it refire the trigger
> again and go into an endless loop?

> Any help would be appreciated.

> Jason Garland

Jason,

Look at SQL Books Online (topic Nested Triggers), which says:

"A trigger does not call itself recursively. In other words, a trigger
does not call itself in response to a second update to the same table
within the trigger. For example, if an update trigger on one column of a
table results in an update to another column, the update trigger
activates only once rather than repeatedly."

I tried it on test table, and it seems to work.

Regards,

Laszlo Kalmar
Schwabo Bankcard Ltd., Hungary

 
 
 

Recursive Update trigger

Post by Kalen Delane » Mon, 16 Mar 1998 04:00:00


Nope, triggers will not call themselves recursively.

--
Kalen Delaney
MCSE, SQL Server MCT, MVP


>I'd like to change a "LastUpdatedBy" column on a table in an insert and
>update trigger.

>I know I can use SUSER_NAME(SUSER_ID) to obtain the user name.  But if I
>Update the table in the trigger with this data won't it refire the trigger
>again and go into an endless loop?

>Any help would be appreciated.

>Jason Garland

 
 
 

1. transactional replication with queued updating and recursive trigger feature

Hi all,
We've set new replication scheme (using queued updating model) between 2 dbases
with recursive triggers option set to on on both of them.
During initialization phase, we've received an error message:
"RecursiveTrigger" option must be set to false.

I've checked the BOL and found zero occurences about that topic.

So my question is:

Is it really necessary to disable recursive triggers if I plan to use
replication with enabled updating at subscribers?

thx a lot
vrata
we r using mssql2k

2. Need Help with Character String Manipulation in 5.0

3. puzzling issue with inserted, updated and IF UPDATE(column) in an update trigger

4. Online 4.11 on Openserver 5.0.4

5. Recursive Trigger Question

6. US-NY-MANUFACTORING FUNCTIONAL

7. Recursive Trigger ???

8. Analysis Services Data Folder on Network Share

9. Prob with recursive/nested and cascading delete triggers?

10. Recursive Trigger

11. Recursive delete trigger in self referenced table

12. How can make a Trigger recursive

13. Recursive Trigger?