0

We have a table that has a trigger defined on it
Sometimes, some application runs disable trigger command, then does something, then runs enable trigger to enable it back

Scanning the GIT, developers said they do not see this code (disable trigger tr_TriggerName) anywhere in repositories
My goal is to capture which app does that and when

Is there a corresponding event in Extended Events that can help me capture these events, along with information:

  • which login that runs it
  • whole batch of SQL, as it runs more than just disable / enable trigger in a batch

I've tried to search for "enable","trigger","ddl" within events library, but did not find anything, unless I am missing something

If there is better way to do it (not Extended Events), let me know

update: ChatGPT suggested using "ddl_command_start" event, but I can't find it in events library, probably it exists in SQL 2019 but not in SQL 2017

update 2: tried to use object_altered event as below answer suggested, scripted code is below:

CREATE EVENT SESSION [Trigger_Enable_Disable] ON SERVER 
ADD EVENT sqlserver.object_altered(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.server_principal_name,sqlserver.session_server_principal_name,sqlserver.sql_text)
WHERE ([database_name]=N'MyDatabase' AND [object_name]=N'MyTrigger'))
ADD TARGET package0.event_file(SET filename=N'X:\ExtendedEvents\Trigger_Enable_Disable.xel',max_file_size=(102400))
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=OFF,STARTUP_STATE=ON)

But it does not capture data when I manually disable enable that trigger

Improve this question
0

1 Answer 1

Reset to default
3

When I create this Extended Event, it will grab the changes of the trigger:

CREATE EVENT SESSION [TestObjectChanges] ON SERVER 
ADD EVENT sqlserver.object_altered(SET collect_database_name=(1)
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username))

enter image description here

Improve this answer
7
  • tried this event "object_altered", but it did not work for me. Updated the question.
    – Aleksey Vitsko
    Jan 11 at 19:59
  • Remove the object trigger name. The alter is on the table.
    – Peter
    Jan 11 at 20:31
  • So instead of MYTrigger, use MyTable
    – Peter
    Jan 11 at 20:32
  • 1
    alright, I will try that and let you know
    – Aleksey Vitsko
    Jan 11 at 21:50
  • It should work as you see my printscreens 😝
    – Peter
    Jan 11 at 22:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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