History of trigger

Question

History of trigger

panwar.jt

Default port

Points: 1418
More actions
March 14, 2011 at 1:28 am

#236736

Hi All,
Is there any way to know the Disable/Enable history of any trigger. I mean can we check where A trigger is disabled and by whom and when enabled again and by whom.
Thanks
KD

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You could check the default trace, I'm not sure if enabling/disabling triggers is in there. It's also not a long-term history. SQL rolls the files over once they get too big and it only keeps 5.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

panwar.jt Default port Points: 1418 More actions · Answer 2

Thanks a lot,

I am not part of DBA team so i can't view that folder.

Is there any other way to track this history.

Thanks

KD

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

select * from fn_trace_gettable( <trace filename> , <number of rollover files to read> )

You can get the name by querying sys.traces

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

panwar.jt Default port Points: 1418 More actions · Answer 4

Thanks,

Query is running but I have two concern.

1-- TextData column is null

2-- If I am diable/enable triger that event is not captured in default trace.

Lowell SSC Guru Points: 323518 More actions · Answer 5

just confirming: looks like the disable of a trigger does not count as a DDL event, so it is not tracked int eh default trace; i created both a database trigger and a trigger on a table, then disabled them;

the creates/drops appear in the trace, but no mention of the disable event;

i think you'd have to set up a new trace and capture the event going forward; there's nothing to do about past events if it's not in a trace....

well maybe with a third party log reader, AND the database is in full recovery, AND you have access to any transaction logs...but that's about $1000 dollars, is the finger pointing worth that much?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Lowell (3/14/2011)
just confirming: looks like the disable of a trigger does not count as a DDL event, so it is not tracked int eh default trace; i created both a database trigger and a trigger on a table, then disabled them;
the creates/drops appear in the trace, but no mention of the disable event;

🙁

It's not an event that fires any DDL trigger either...

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

panwar.jt Default port Points: 1418 More actions · Answer 7

Thanks to both of you for quik responce.

Actualy I already have a DDL trigger that capture all DDL in database.

But issue with this is that anyone can disable this trigger and fire some and enable, So in that case I cant capture some events.

So I am asking if I can track the trigger history.

Ant way I also caputre any event from profiler.

Thanks

KD