Post reply

Trigger with Eventdata, Rollback not working

  • March 6, 2017 at 12:25 am

    #321061

    When the recovery model is changed the trigger below is fired: an error is raised but the action will be executed. Is a rollback possible with eventdata triggers ? I understood, that it should work. Now I am not sure if I have a mistake in the code or not. Thanks for your advice.

    CREATE TRIGGER [TR_PreventDBChanges1]
    ON ALL Server
    FOR ALTER_Database
    AS

    DECLARE
    @EventTime datetime
    , @ServerName varchar(200)
    , @DatabaseName varchar(200)
    , @TSQL varchar(2000)
    , @event XML

    DECLARE @audit TABLE (
                  [DatabaseName] [varchar](200) NULL,
                  [TSQL] [varchar](2000) NULL
                  )

    SELECT @event = EVENTDATA()

    SELECT @DatabaseName = @event.value('(/EVENT_INSTANCE/DatabaseName)[1]','sysname' )
    SELECT @TSQL = @event.value('(/EVENT_INSTANCE/TSQLCommand)[1]','varchar(2000)' )

    INSERT @audit (DatabaseName, TSQL) SELECT @DatabaseName, @TSQL

    IF (SELECT TSQL FROM @audit WHERE TSQL Like '%SET RECOVERY%') IS NOT NULL
    BEGIN

          RAISERROR ('Recovery model changes are not allowed',1,1)
                    ROLLBACK;

    END

    GO

  • March 6, 2017 at 5:14 am

    #1931764

    According to this blog http://itsalljustelectrons.blogspot.ch/2016/05/SQL-Server-Alter-Database-Oddities.html there is not way to roll it back. The final part of the article is not clear. A recovery model chenge for a database member of an availability group won't work anyway as it has to be full recovery. 
    My test are done with a normal database.

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

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