SQL Profile -- Trigger

2 Sep 2005 4:18 PM

Ed

Hi,
Is there anyway I can capture the name of the trigger that is fired?
I tried SQL:StmtCompleted and SQL:BatchCompleted but both did not show the
name of trigger that was fired during the Insert/Update/Delete...

Thanks

Ed

2 Sep 2005 5:02 PM

Trey Walpole

Capture the SP:StmtCompleted event (under Stored Procedures in the event
tab in the GUI).
In the Text data column, the name appears in a CREATE TRIGGER statement
when the trigger fires. The event will capture the object_id of the
trigger, but not the name.

Ed wrote:
Show quote

> Hi,
> Is there anyway I can capture the name of the trigger that is fired?
> I tried SQL:StmtCompleted and SQL:BatchCompleted but both did not show the
> name of trigger that was fired during the Insert/Update/Delete...
>
> Thanks
>
> Ed

2 Sep 2005 5:44 PM

Ed

yes... that's it
how about if i update the data not going through the SP?

Show quote

"Trey Walpole" wrote:

> Capture the SP:StmtCompleted event (under Stored Procedures in the event
> tab in the GUI).
> In the Text data column, the name appears in a CREATE TRIGGER statement
> when the trigger fires. The event will capture the object_id of the
> trigger, but not the name.
>
>
> Ed wrote:
> > Hi,
> > Is there anyway I can capture the name of the trigger that is fired?
> > I tried SQL:StmtCompleted and SQL:BatchCompleted but both did not show the
> > name of trigger that was fired during the Insert/Update/Delete...
> >
> > Thanks
> >
> > Ed
>

2 Sep 2005 6:11 PM

Trey Walpole

Trigger events are captured as SP:Stmtxxx events in a trace

Ed wrote:
Show quote

> yes... that's it
> how about if i update the data not going through the SP?
>
> "Trey Walpole" wrote:
>

2 Sep 2005 5:08 PM

David Portas

OBJECT_NAME(@@PROCID)

--
David Portas
SQL Server MVP
--

2 Sep 2005 5:12 PM

David Portas

Sorry, I think I answered the wrong question. SP:StmtCompleted is the
answer. You can use the OBJECT_NAME function to retrieve the name of
the trigger from the object id captured by Profiler.

--
David Portas
SQL Server MVP
--

2 Sep 2005 5:46 PM

David Gugick

Ed wrote:

> Hi,
> Is there anyway I can capture the name of the trigger that is fired?
> I tried SQL:StmtCompleted and SQL:BatchCompleted but both did not
> show the name of trigger that was fired during the
> Insert/Update/Delete...
>
> Thanks
>
> Ed

Unfortunately, SQL Server while treating triggers much like stored
procedures, does not trigger SP:Starting/Completed events for triggers.
However, you will see SP:StmtStarting/Completed events for each
statement fired in the trigger and can use the ObjectID column to
resolve the object name. You'll also see the name of the trigger as the
first line in the TextData column and can probably filter on this value.

--
David Gugick
Quest Software
www.imceda.com
www.quest.com