0

Is there are way to find how many times trigger is called in a particular session? Our ETL application is inserting records like each row instead of statement (ie) If 100 records are to be inserted I think ETL issuing 100 insert statements instead of inserting in a single batch. So trigger is also called 100 times. I just want to confirm that trigger is called 100 times.

I have a idea to alter the trigger to have counter and store it in a table to find the number of trigger calls.

Is there a native way to find this?

2

You can set up an Extended Event trace and capture event type 'sp_statement_completed'. Filter the trace by object_type='trigger'. You can also pass your trigger name to narrow down further. Following definition will give you a starting point. Adjust file name, location, size and max_file_size based on your need.

CREATE event session [CaptureTriggerExecution] ON server ADD event sqlserver.sp_statement_completed( WHERE (
  [package0].[equal_uint64]([object_type],(21076)) 
  AND 
  [object_name]=N'''My Trigger''' 
) 
) ADD target package0.event_file(SET filename=N'CaptureTriggerExecution', 
  max_file_size=(10)) WITH (max_memory=4096 kb, 
event_retention_mode=allow_single_event_loss, 
max_dispatch_latency=30 seconds, 
max_event_size=0 kb, 
memory_partition_mode=none, 
track_causality=ON, 
startup_state=OFF)
GO
|improve this answer|||||
0

Solution would be to create a logging table, and alter trigger's code - add a line that would insert 1 row into logging table each time trigger is called

You can have columns in the logging table -

create table TriggerLog (

ID                   int primary key identity, 
ExecDate             datetime, 
CallerSessionID      int, 
CallerLogin          varchar(50))

Add below code to the trigger

-- log trigger run to table
insert into TriggerLog (ExecDate, CallerSessionID, CallerLogin)
select getdate(), @@SPID, original_login()

And then you can query your logging table and see how often trigger is run
You can even add columns and code to the trigger to log how many records are inserted by ETL process per 1 run

There can be performance impact of adding logging, but only if your trigger is called very frequently (tens or hundreds calls per second)
Also you might need to grant ETL process (login that runs ETL tasks) permissions to insert into your logging table

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.