SQL Server Trigger Call Count

insertsql servertrigger

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?

Best Answer

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

Related Solutions

SQL Server – Launch Python Script from Insert Trigger

Don't make your user transaction wait for the (hopefully!) successful completion of the Python script. Your entire transaction sits there and waits for this external process to run, try to send mail, etc. I doubt the e-mail really has to go out that instant - especially given you can't control any delays it has as it gets routed to the recipient's inbox anyway. Why not just run the process more frequently, if timing is so important?

Please give this tip a look-through.

If you really, really, really want to do this the wrong way, you can just enable xp_cmdshell and fire away.

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

Now, assuming the user has access to xp_cmdshell and/or the SQL Server service account can see the folder where the python script is stored, you should be able to do this from within your trigger:

EXEC master..xp_cmdshell N'C:\Python27\python.exe C:\source\NotifyAgents.py';

As an aside, you should state in your question that you aware that this is a very bad thing^TM, but you are not concerned with that, for whatever reason. I still don't think you're going to get as real time as you expect, even if you do fire this from the trigger. Have you considered database mail instead of python?

Using COUNT in Triggers to Update Another Table

This trigger does the job:

create trigger trx_updates_atrig
after insert on trx_updates for each row begin
DECLARE updatecount INT;
  set updatecount = ( select count(*) from trx_updates where request = new.request );
  if updatecount=4
    then
      update trx_request set finished=NOW() where id = new.request;
  end if;
end//

SQLFiddle Demo.

Best Answer

Related Solutions

SQL Server – Launch Python Script from Insert Trigger

Using COUNT in Triggers to Update Another Table

Related Question