SQL Server – How to Continue Operation After Custom Logic in Instead of Trigger

sql servertrigger

I do not think what I am trying to do is possible, but I could not find any documentation on this. I have an INSTEAD OF UPDATE, INSERT, DELETE that always inserts data into a history table before performing the DML operation. So I created triggers that performed my insert into the history table and then I created 3 IF statements to figure out if the original operation was an INSERT UPDATE or DELETE operation to continue the original operation.

Is there a way to perform my history insert and then tell SQL Server to continue with the original INSERT, UPDATE or DELETE without writing those myself? See my code below. We are using the standard edition of SQL Server, so no CDC to help with logging changes. Thank you.

    SET NOCOUNT ON;

    -- ========================================================
    -- STEP 1: ARCHIVE. Get the records that are being inserted 
    -- or deleted and put them in the history table
    -- ========================================================

    -- INSERT INTO HISTORY TABLE HERE

    -- ========================================================
    -- STEP: CONTINUE. After archiving, perform the requested
    -- manipulation
    -- ========================================================

    IF(EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted))
    BEGIN
      -- Perform insert into production table
    END

    IF(EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted))
    BEGIN
      -- Perform update on production table
    END

    IF(NOT EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted))
      -- Perform delete on production table    
END

Best Answer

Posting this as an answer to address the core question:

Is there a way to perform my history insert and then tell SQL Server to continue with the original INSERT, UPDATE or DELETE without writing those myself?

No, it is not possible to have an INSTEAD OF trigger just perform the original DML for you. This is why they are called INSTEAD OF and not BEFORE - they replace the original action with whatever logic you want to perform, which may or may not include DML against the original target table, depending on the scenario (you may want to prevent the insert instead of rolling it back, you may be migrating to a new table and using this as an interim solution, you may have distributed or otherwise not-directly-updateable views, etc).

If you feel you have a valid use case for INSTEAD OF, then you will have to manually code the DML they're replacing. I don't think you do, so, as the others have suggested, use an AFTER trigger.

Related Solutions

Sql-server – Prevent record from being inserted in the table in After Trigger

I do not see this as the most desirable implementation. Using a Foreign Key constraint to enforce correctness is (in my opinion) much better than writing code to handle such issues.

However, regarding your question, Yes, TableA can have both an INSTEAD OF trigger and an AFTER trigger. (In fact it can have several AFTER triggers.)

https://msdn.microsoft.com/en-us/library/ms189799.aspx outlines some rules, including:

If constraints exist on the trigger table, they are checked after the INSTEAD OF trigger execution and before the AFTER trigger execution. If the constraints are violated, the INSTEAD OF trigger actions are rolled back and the AFTER trigger is not fired.
An AFTER trigger is executed only after the triggering SQL statement has executed successfully. This successful execution includes all referential cascade actions and constraint checks associated with the object updated or deleted. An AFTER trigger will not recursively fire an INSTEAD OF trigger on the same table.

How to commit transaction on an after update event trigger

Why do you expect B to be updated, when you inserted a row with a different id? Actually, your INSERT ALL does not insert anything into B.

The above works fine after fixing your trigger and sample data:

create or replace trigger updB  
after update on B  
for each row  
declare  
v varchar2(5);  
begin  
  v := :new.val;
end;  
/

This is how you reference the new val in the trigger, not with a select. Your original trigger would run into the infamous ORA-04091 "table is mutating" error.

SQL> insert all into A values ('a', 'false') into b values ('a', 'false') select * from dual;

2 rows created.

SQL> commit;

Commit complete.

SQL> update A set val = 'true';
Cek cek: false

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from a;

ID         VAL
---------- ----------
a          true

SQL> select * from b;

ID         VAL
---------- ----------
a          true

For the output:

Your updA trigger runs in an autonomous transaction. When you select val from A, your original update is still not finished (commited), you will see the old value. Fixing your trigger for A:

create or replace trigger updA  
after update on A  
for each row  
declare  
pragma autonomous_transaction;  
cek varchar2(5);  
begin  
commit;  
  cek := :new.val;  
dbms_output.put_line('Cek cek: ' || cek);  
update B set val = :new.val where id = :new.id;  
commit;  
end;  
/

Best Answer

Related Solutions

Sql-server – Prevent record from being inserted in the table in After Trigger

How to commit transaction on an after update event trigger

Related Question