CODEWITHSUNDEEP
sql-server-2008triggers
hollyx
hollyx

Reputation: 99

Interrupt a trigger execution without a box message (tsql)

I am writing a trigger for controlling a column. The script works as I want but my problem is in the raiseerror. I want the trigger to work without showing the error message to the user.

Can anyone who knows what is the equivalent of raiseerror without showing the error message to the user?

I tried with rollback transaction which gve me another error message instead, and I tried with return which did not interrupt the execution of the trigger.

This is my trigger: 

DECLARE @val varchar(9)
SELECT @val= [DC_Piece] 
from INSERTED 
where INSERTED [DC_Domaine]=0 and INSERTED.[DC_IdCol]=6

IF UPDATE([DC_Piece])
BEGIN
IF NOT EXISTS( select [DO_PIECE] 
from DOCEN 
where  @val= [DO_Piece] and [DO_Domaine]=0 and [DO_Type]=6)
    RAISERROR('STOP',11,1)
END

Please help me

Upvotes: 0

Views: 220

Answers (1)

marc_s
marc_s

Reputation: 754508

You need to completely rewrite your trigger to take into account that it will be called once per statement (NOT per row!) and the Inserted and Deleted pseudo tables can contain multiple rows which you should consider.

So try something like this:

CREATE TRIGGER trg_abort_insert
ON dbo.YourTableNameHere
AFTER UPDATE
AS
    -- check if any of the DC_Piece columns have been updated
    IF EXISTS (SELECT * 
               FROM Inserted i  
               INNER JOIN Deleted d ON i.PrimaryKey = d.PrimaryKey  -- link the two pseudo tables on primary key
               WHERE i.DC_Piece <> d.DC_Piece   -- DC_Piece has changed
                 AND i.DC_Domaine = 0
                 AND i.DC_IdCol = 6)
        -- if your conditions are met --> just roll back the transaction
        -- nothing will be stored, no message is shown to the user 
        ROLLBACK TRANSACTION                 
END

Upvotes: 1

Related Questions