0

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
1

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.

  • Thank you, and that is what I expected to hear. I just wanted to makes sure I was not missing anything before I started rewriting the code. – jmzagorski Sep 22 '14 at 13:52
1

You are defining an ordinary AFTER trigger. So use an AFTER trigger, not an INSTEAD OF trigger. The cases where you want to use 'instead of' triggers are, basically, none.

  • 2
    I don't understand your objection to instead of triggers. In cases where you will have a high rate of failure, you would rather write to a table then roll it back, with all the additional costs involved, than not do anything at all? I certainly agree that the OP's problem is not an ideal use case, but that doesn't mean there are zero use cases - even if you don't count the use case with views. – Aaron Bertrand Sep 22 '14 at 13:46
  • I thought about that in the beginning but something with the old code and application were preventing me from doing that. Maybe it is time to revisit the AFTER trigger if that is the only way. Thank you – jmzagorski Sep 22 '14 at 13:47
1

You could just use a FOR trigger instead of an INSTEAD OF trigger. The records, as they were before the DML statement, are still in the deleted table (for updates and deletes). Everything will still be contained in an implicit transaction.

In my opinion, INSTEAD OF triggers are really handy when you don't want to run the original DML, or on views.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.