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

Sure you can create such an trigger, and "Reject" would be a rollback.
Keep in mind, that a trigger fires once per transaction, not for each effected recordset and a ROLLBACK here means rollback the complete transaction.

Better create procedure(s) which checks the data first before you INSERT/UPDATE.

Answer 3

Thanks Olaf!

Unfortunately I cannot add a procedure to do this (its bi-directional replication from an iSeries database, I don't want to confuse things, but it has to be a trigger)

Each transaction on the table is only a single update or insert so I dont think the rollback will be a problem.

Is this the correct way to attempt it?

CREATE TRIGGER dbo.trg_MyTrigger
   ON  dbo.Mytable
   INSTEAD OF INSERT,UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

IF Condition IS True
    BEGIN
        ROLLBACK TRANSACTION
    END
    ELSE
        COMMIT TRANSACTION

END
GO

Answer 4

You need a After trigger:
CREATE TRIGGER dbo.trg_MyTrigger
ON dbo.Mytable
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;
--your logic
IF Condition IS True
BEGIN
ROLLBACK TRANSACTION
END

END

Answer 5

Hi @zoe Ohara
There are two types of DML triggers: AFTER triggers & INSTEAD OF triggers
Both of them work for insert, delete and update operations.And I recommend using the AFTER trigger in this case.

CREATE TRIGGER tri_AfterInsert ON dbo.Mytable  
AFTER INSERT,UPDATE   
AS  
IF Condition IS True  
BEGIN  
   PRINT 'Information that does not meet the conditions'  
   ROLLBACK TRANSACTION    
END  

If you want to use INSTEAD trigger,then refer to this article for more details : Using INSTEAD OF triggers in SQL Server for DML operations.

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.