Can a trigger "undo / undo" the triggering action?

Question

Can a trigger "undo / undo" the triggering action?

Navigation

#1 by (1 votes)

5

Example scenario

I have a telefones table where I have a trigger statement that is triggered when executing the insert statement.

Doubt

Can I use the trigger to check a condition and upon return, "cancel" the action on the triggering table? li>
Would you like to set the "attention / error" message that SQL will return?

( An example would be extremely important )

sql-server trigger

asked by anonymous 06.09.2018 / 01:42

1 answer

How does an annonymous innerclass arrow an instance private variable? Passing a list of arguments to a Jasper Reports query

score 1 · Accepted Answer

By the description you gave the ideal would be to use a trigger of type instead of :

ALTER TRIGGER tr_tabela ON tabela
INSTEAD OF INSERT AS
BEGIN
    SET NOCOUNT ON;

    IF -- Sua condição para o erro aqui
    BEGIN
        RAISERROR('Mensagem de erro.', 16, 1);
    END
    ELSE -- Insere 
    BEGIN
        INSERT tabela
        SELECT i.*
          FROM inserted i;
    END;
END;
GO

In the case of the non-executable example above the insertion will only be performed in the table if the IF condition is not satisfied.

INSTEAD OF

Specifies that the DML trigger will be executed instead of the trigger SQL statement, overriding the actions of the trigger statements. INSTEAD OF can not be specified for DDL or logon triggers.

At most, an INSTEAD OF trigger per INSERT, UPDATE, or DELETE statement can be set in a table or view. However, you can set views on views, where each has its own INSTEAD OF trigger.

The INSTEAD OF triggers are not allowed in updatable views that use WITH CHECK OPTION. SQL Server generates an error when an INSTEAD OF trigger is added to a specified updateable WITH CHECK OPTION. The user should remove this option using ALTER VIEW before setting the INSTEAD OF trigger.