Re: which statement fire the trigger ?

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: David Gugick (davidg-nospam_at_imceda.com)
Date: 01/12/05 

Date: Wed, 12 Jan 2005 01:48:40 -0500

Vinod Kumar wrote:
> In 2000? Is this possible? What are the Events I need to include?
>
>
> "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
> news:eIoDvtD%23EHA.1296@TK2MSFTNGP10.phx.gbl...
>> You can use profiler.
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>>
>> "TLV" <tlv@cassava.net> wrote in message
>> news:%23FFtfRD%23EHA.2112@TK2MSFTNGP10.phx.gbl...
>>> Hi ,
>>>
>>> Is there any way to get the sql statement that fire a trigger ?
>>>
>>> Thank you in advance ,
>>> TLV

Triggers do not have specific events in Profiler. The statements in the
trigger show up as SP:StmtStarting/Completed events, but without
SP:Starting/Completed events. You'll need to capture SQL:StmtStarting,
RPC:Starting, SP:StmtCompleted or Starting. Here's an example:

create table test_trig(col1 int)

create trigger testtrig_ins on test_trig
for insert
as
If (select count(*) from inserted) > 1
rollback tran

insert into test_trig values (5)

SQL:StmtStarting insert into test_trig values (5)
SP:StmtCompleted -- testtrig_ins create trigger testtrig_ins on
test_trig
SP:StmtCompleted -- testtrig_ins If (select count(*) from inserted) > 1
SP:StmtCompleted
SQL:StmtCompleted insert into test_trig values (5)
SQL:BatchCompleted insert into test_trig values (5) 

-- 
David Gugick
Imceda Software
www.imceda.com

Relevant Pages

  • Re: Auditing changes made to table design (syscolumns table)
    ... With out using profiler the only way I know of is to get this info from the ... Keep in mind I know the database and the tables I would ... I attempted to write a trigger to log when a change was made to the ... > certain criteria against every transaction. ...
    (microsoft.public.sqlserver.security)
  • Re: Updating Subscriber Question
    ... Through profiler I have determined the process is hanging up in the update ... trigger when the sp_MSget_publisher_rpc procedure is called. ... MSsubscription_properties table is queried to get the login id for the ... "Paul Ibison" wrote: ...
    (microsoft.public.sqlserver.replication)
  • Re: inserted in Trigger auswerten
    ... Ich habe mich mit dem Profiler ... Meine Trigger funktionieren übrigens gut. ... Prev by Date: ... Next by Date: ...
    (microsoft.public.de.sqlserver)
  • Re: Can SQL Profiler Track a Field in a Table?
    ... Profiler can track the execution of statements, but cannot be used to track ... then I would suspect faulty trigger logic. ... MyDate is supposed to update with a getdatevalue. ...
    (microsoft.public.sqlserver.server)
  • Re: Trigger - Instead of Update
    ... Andrew J. Kelly ... > incide the trigger because it not longer exists. ... > GFID INT NOT NULL FOREIGN KEY REFERENCES GFON DELETE CASCADE ON ... >> UniqueId FROM inserted) ...
    (microsoft.public.sqlserver.programming)