Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 what statement fired my trigger?

Author  Topic 

Maa421
Starting Member

2 Posts

Posted - 2008-08-18 : 11:09:23
I would like to find out if there is a system variable or something that contains the T-SQL that fired a trigger.

I'm not hopeful, but maybe someone out there has an idea.

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-08-18 : 11:16:45
well, not really. However the trigger will run in the same "context" as the statement that fired it. So if you can identify the SPID that is currently running the trigger code (ie: using SP_WHO2 active), then you could then use DBCC INPUTBUFFER(<spid>) to see the statement that was issued.

What question are you trying to answer or problem are you trying to solve? Perhaps there is another way.

Be One with the Optimizer
TG
Go to Top of Page

Maa421
Starting Member

2 Posts

Posted - 2008-08-18 : 11:28:56
I have an update trigger that runs very quickly for set operations on many records. However, it is extremely slow when performing several individual updates on the same records (in a batch).

I would like to find out exactly what code is running during the slow processing.

Thanks
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-08-18 : 12:01:36
How do you know it is the trigger code as opposed to the user update statements that is the performance hog?

If you have a dev/test environment you could place some print statements - in the batch as well as the trigger - at each statement to print the step and a timestamp. That way you can see what specific statement(s) is taking a long time. While it is running also check sp_who2 output for blocking.

Be One with the Optimizer
TG
Go to Top of Page
   

- Advertisement -