Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Trigger to raise error, prevent delete, and audit attempt

I'm attempting to create a trigger which does three things: 1. Prevents the deletion of data on a particular table 2. Generates an error message for the user 3. Logs the data which was attempted to be deleted along with session information from SQL.

This is the code I have so far:

CREATE TRIGGER [dbo].[MyTable_prevent_delete_and_audit]
ON [dbo].[MyTable]
INSTEAD OF DELETE AS
       BEGIN
          DECLARE @SESSIONINFO nvarchar(200)
          SELECT @SESSIONINFO = (RTRIM(LTRIM(CAST(login_time as nvarchar(20)))) + ' ' 
            + RTRIM(LTRIM(hostname)) + ' ' + RTRIM(LTRIM(program_name)) + ' ' 
            + RTRIM(LTRIM(cmd)) + ' ' + RTRIM(LTRIM(loginame))) from sys.sysprocesses WHERE spid = @@SPID
          INSERT INTO [dbo].[MyTable_AUDIT] ([Field1],[Field1],[SESSIONINFO])
             SELECT [Field1],[Field1],@SESSIONINFO FROM deleted
          RAISERROR ('Removing MyTable entries prevented by trigger.  Contact your administrator', 16, 1)
       END
       RETURN
GO

The code above is able to prevent the deletion, and raise the error for the user. However, nothing is ever entered into the audit table. If I comment out the RAISEERROR line the trigger correctly adds items to the audit table, but of course the data is deleted. I'm thinking I've got to be missing something simple (is deleted not available if I'm raising an error?), or misunderstanding some element of the concept. Please show me the error of my ways! :)

Edit: Aaron Bertrand has the correct answer, I have to commit my audit data before raising the error. The RAISERROR essentially rolls everything back, including the audit:

CREATE TRIGGER [dbo].[MyTable_prevent_delete_and_audit]
ON [dbo].[MyTable]
INSTEAD OF DELETE AS
       BEGIN
          DECLARE @SESSIONINFO nvarchar(200)
          SELECT @SESSIONINFO = (RTRIM(LTRIM(CAST(login_time as nvarchar(20)))) + ' ' 
            + RTRIM(LTRIM(hostname)) + ' ' + RTRIM(LTRIM(program_name)) + ' ' 
            + RTRIM(LTRIM(cmd)) + ' ' + RTRIM(LTRIM(loginame))) from sys.sysprocesses WHERE spid = @@SPID
          INSERT INTO [dbo].[MyTable_AUDIT] ([Field1],[Field1],[SESSIONINFO])
             SELECT [Field1],[Field1],@SESSIONINFO FROM deleted
          COMMIT TRANSACTION;
          RAISERROR ('Removing MyTable entries prevented by trigger.  Contact your administrator', 16, 1)
       END
       RETURN
GO
like image 441
cninsd Avatar asked Oct 02 '22 05:10

cninsd


1 Answers

Try committing the INSERT before raising the error. Otherwise, raising the error rolls back everything the trigger did and everything the statement that invoked the trigger did.

INSERT INTO [dbo].[MyTable_AUDIT] ([Field1],[Field1],[SESSIONINFO])
  SELECT [Field1],[Field1],@SESSIONINFO FROM deleted;

COMMIT TRANSACTION;

RAISERROR ('Removing MyTable entries prevented by trigger. ...', 16, 1);
like image 158
Aaron Bertrand Avatar answered Oct 11 '22 00:10

Aaron Bertrand