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
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);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With