SQL Server - Inserted Table

Question

I am checking the output of the inserted / deleted table using the following trigger, how can I pass on the intercepted UPDATE command to the server after verifying columns?

CREATE TRIGGER Test1_LastUpdate ON Test1
INSTEAD OF UPDATE 
AS
    SELECT * FROM Inserted
    SELECT * FROM Deleted
GO

EDIT: I am looking for a solution that will not need to be changed after schema updates.

CREATE TRIGGER Test1_LastUpdate2 ON Test1
INSTEAD OF UPDATE
AS
    --COMMIT UPDATE THAT WAS INTERCEPTED
END

Answer 1

The only way you "pass on the intercepted UPDATE command to the server after verifying columns" is by performing the UPDATE yourself.

Option 1 - ROLLBACK

However, you have now said that you don't want to have to add more columns to the trigger when those columns are added to the table. So you have an alternate option of simply rolling back any change that is invalid. That might look something like this:

CREATE TRIGGER TR_Sample_U ON dbo.Sample -- No AFTER trigger needed here!
AS
IF EXISTS ( --check for disallowed modifications
   SELECT *
   FROM
      Inserted I
      INNER JOIN Deleted D
         ON I.SampleID = D.SampleID
   WHERE
      I.Something <> D.Something
      AND I.UpdateDate = D.UpdateDate
)
ROLLBACK TRAN;

Option 2 - Perform UPDATE in trigger

However, if you need more control over what the update actually entails, such as needing to modify a value before it is committed, you would have to perform the update yourself. For example:

CREATE TRIGGER TR_Sample_U ON dbo.Sample
INSTEAD OF UPDATE
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;

UPDATE S
SET S.Value = I.Value + '+'
FROM
   dbo.Sample S
   INNER JOIN Inserted I
      ON S.SampleID = I.SampleID
;

This is a trivial example that doesn't do any checking, but you get the idea--when you perform an update on the Sample table, you will see that the value acquires an extra + character--your update was intercepted and the Inserted value (representing the proposed change after update) was modified before being committed.

See a SQL Fiddle Demo of this in action.

The only thing to watch out for is recursion:

1. Direct Recursion

   When your update could cause other triggers to run that modify the same base table--then you can get ping-ponging between them, until the maximum nest level is reached and the entire transaction is rolled back. So be aware of possible ping-ponging between triggers.

2. Indirect Recursion

   You probably don't have to worry about this one, because the database-level RECURSIVE TRIGGERS option is off by default in SQL Server. However, if it is on, you can get the same trigger firing based on the new update.

These be ameliorated by, variously:

• Checking TRIGGER_NESTLEVEL inside a trigger, and exiting the trigger if already nested deeply enough.

• To avoid direct recursion only, combine the triggers.

• In some particular cases strategically assigning which trigger will run first/last may fix the problem. You can't specify absolute order, but you can choose the first one and the last one.

Note that the ping-pong problem applies to any type of trigger, INSTEAD OF or AFTER, that modifies its own base table, or is involved in a chain of updates through another table (that has a trigger that modifies another table ...) that eventually comes back to modify the base table.

Option 2B - Preprocess the AFTER UPDATE trigger.

I call this option 2B because it is really option 2, but with an enhancement. If you don't want to have to manually update the trigger every time you add columns to the table (a sentiment I wholly agree with) you could automate this. Create a stored procedure that can create an appropriate trigger that observes all the validation that you need. You could put the base code for this validation into a table, then in the SP select it into variables, add in the SQL script updating the columns for the final update by mining the information in the INFORMATION_SCHEMA.COLUMNS view, then finally rewrite the trigger. This could furthermore be attached to a DDL trigger, so that it is 100% automated: you would add or remove a column from the base table, the DDL trigger would fire, and rewrite the DML trigger for you.

That sounds like a lot of work, but if you designed it to be data-driven it could be generalized to work with any table in your entire database, which could be of great value, depending on your usage scenarios.

Answer 2

In order to retrieve the UPDATE statement use SQLprofiler. The inserted table is snapshot of what is being inserted or update (here are stored the new values ). The deleted table is snapshot of what were the values in you are updating/deleting. You trigger will be executed only instead of update commands against table Test1 and you can see what are you updating in inserted.

Check this article about triggers.

Answer 3

Well this is one option...

/*
        Create  Table ExampleTable (LastRefreshed DateTime)
        Go
        Insert  ExampleTable
        Select  GetDate()
*/

Begin   Tran

If      Object_ID('tempdb..#check') Is Not Null Drop Table #check
Create  Table #check (InsertedVal DateTime, DeletedVal DateTime)

Update  ExampleTable
Set     LastRefreshed = GetDate()
Output  Inserted.LastRefreshed As InsertedVal, Deleted.LastRefreshed As DeletedVal Into #check

Select  *
From    #check

If      Exists (Select  1
                From    #check
                Where   InsertedVal > DeletedVal)
Begin
        Rollback Tran
End
Else
Begin
        Commit Tran
End

This creates a table with a DateTime record. The update attempts to update it to "now", but it is run within a transaction and dumps it's inserted and deleted records to a temp table to work with. After the update you can do whatever checks you want against the table data to determine whether you want to commit or rollback your changes. I have this written to always rollback for example purposes.