Trigger to reject or accept an update or insert

zoe Ohara 286 Reputation points
2022-04-07T11:17:53.137+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 102.3K Reputation points
    2022-04-07T22:04:16.293+00:00

    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!

    1 person found this answer helpful.
    0 comments No comments

5 additional answers

Sort by: Most helpful
  1. LiHong-MSFT 10,046 Reputation points
    2022-04-08T03:07:48.817+00:00

    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.

    1 person found this answer helpful.
    0 comments No comments

  2. Olaf Helper 41,411 Reputation points
    2022-04-07T11:42:10.81+00:00

    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.

    0 comments No comments

  3. zoe Ohara 286 Reputation points
    2022-04-07T14:02:51.89+00:00

    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
    

  4. Jingyang Li 5,891 Reputation points
    2022-04-07T16:33:21.05+00:00

    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

    0 comments No comments