CODEWITHSUNDEEP
sql-serversql-server-2005triggers
Rahul
Rahul

Reputation: 21

How to check the log when Trigger is turned On and Off

I want to check when my trigger is turned on and off in SQL Server 2005 database. I have created a trigger which inserts entry into new table whenever there is change in specific column. But I received a complaint from client saying that log is not maintained for all updated records in Log Table.

I feel it may be due to trigger is turned off while updating some records so it may not have been recorded in log table.

So how to see log record of whether trigger is turned on or off?

Thanks in advance.

Upvotes: 0

Views: 2296

Answers (2)

Sohrab Saleh
Sohrab Saleh

Reputation: 1

I put this answer for whom may search for this question. You can use this statement to see more detail and the last modify date and time of trigger.

 SELECT *
 FROM sys.triggers
 WHERE name = 'your-trigger-name-here'

Upvotes: 0

marc_s
marc_s

Reputation: 754378

Not quite clear what you're looking for.

Do you want to be able to check at any given time whether the trigger is on or off? In that case, use this statement:

SELECT is_disabled
FROM sys.triggers
WHERE name = 'your-trigger-name-here'

If you want to track when a given trigger is disabled, you could write a DDL trigger that catches the ALTER_TRIGGER event and makes a note of who and when a given trigger was disabled.

Upvotes: 1

Related Questions