Post reply

History of trigger

  • 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

  • March 14, 2011 at 2:11 am

    #1298217

    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

  • March 14, 2011 at 3:27 am

    #1298227

    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

  • March 14, 2011 at 3:39 am

    #1298232

    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

  • March 14, 2011 at 4:41 am

    #1298252

    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.

  • March 14, 2011 at 4:56 am

    #1298254

    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!

  • March 14, 2011 at 5:19 am

    #1298259

    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

  • March 14, 2011 at 5:59 am

    #1298272

    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

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

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