SQL Trigger to Ignore DB snapshot

  • GonnaCatchIT

    SSCrazy

    Points: 2609

    Dear All,

    I have a trigger on Create database command which logs into audit tables.

    I want filter out this trigger for Snapshot DB creations. Is it possible?.

    Basically no trigger while creating snapshot DB's, but only for actual DB's.

    Snapshot command:

    'Create Database.. As snapshot on'

    Thanks.

  • Site Owners

    SSC Guru

    Points: 80385

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • frederico_fonseca

    SSChampion

    Points: 14053

    you can't prevent the trigger from executing but you can control what it does by looking at the event data and filtering if the command is a create snapshot one

    within your trigger do something as follows

    DECLARE @CommandText nvarchar(2000);
    SELECT @CommandText = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','NVARCHAR(2000)');
    if (@CommandText not like '%snapshot%of%')
    begin
    -- do your audit code here
    end
  • GonnaCatchIT

    SSCrazy

    Points: 2609

    Thanks Fred, I will work around this.

Viewing 4 posts - 1 through 4 (of 4 total)

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