Trigger to reject or accept an update or insert

Question

Hi!

I have a requirement where I need to create a BEFORE INSERT, UPDATE trigger on a table. It needs to check a condition (various values don't already exist etc.) and then either ACCEPT or REJECT the transaction. Is this possible?

I would normally use a constraint on the table for this purpose, but for complex reasons that I wont go into I cant do this and need to use a trigger instead.

Thanks,

Zoe

Answer 1

If you want to do this as an INSTEAD OF trigger, the INSERT trigger should look like this outline:

CREATE TRIGGER MyTrigger ON MyTable INSTEAD OF INSERT AS

IF NOT EXISTS (SELECT * FROM inserted) RETURN

IF <bad condition is true>
BEGIN
    RAISERROR('Blue murder!', 16, 1)  
    ROLLBACK TRANSACTION
    RETURN
END

INSERT tbl (col1, col2, col3, ....)
   SELECT col1, co2, col3, ...
   FROM  inserted

Since repeating the INSERT statement is kind of boring and opens for a maintenance problem, most people do this as an AFTER trigger. But if you expect the bad condition to appear often, it cannot be denied that the INSTEAD OF trigger is more efficient.

Note: Never do COMMIT TRANSACTION in a trigger, that will not end well!

Answer 2

Thanks all!

I've gone with Erlands answer as it allowed me to use the INSTEAD OF trigger