none
Trigger and INSERTED table RRS feed

  • Question

  • I have been working with Interbase for a long time, and now T-SQL trigger is confusing for me. For those who know Interbase, the question is simple : in an Interbase trigger I have simple and quick access to a certain column's old and new values - that is, the value before and after an insert for instance - by prepending the column's name with old. and new., but I cannot find a simple way to do this in T-SQL ? I see, there are two special tables, the INSERTED and DELETED, but the concept of these are really not clear. Are these tables contain only changed row's column values regarding a specific - the currently running - instance of a tigger, or for all SQL operations at a given time ?

    My problem basicly is that a database might have a high volume of sql operations, inserts for example, and therefore many overlapping instance of a trigger may be running at the same time. I mean, while a trigger is executing, another insert operation may be executed and therefore another tirrger instance started (thinking about an after insert trigger). Am I right ? If yes, the question is if the INSERTED table is a global one, containing all changes made to a database, or contains only a trigger instance specific rows ? Based on the above, another question is, if the INSERTED table contains only one row or more ?

    And what happens when a trigger itself modifies some columns. Are these modifications - made by the trigger - appears in the INSERTED table ?

    Eventually, what is the correct way of using INSERTED and DELETED tables, when my purpose is to check the value of a column in the last insrted row, and based on that, I may need to update other columns in the same row ?

    Thanks, in advance



    Sunday, April 24, 2011 11:05 AM

Answers

  • I don't know Interbase, but from what you say, there does not seem to be any radical differences. Yes, the syntax is different, but maybe not much beyond that.

    "inserted" and "deleted" are virtual tables. That is, they don't exist physically, and they are only accessible in the direct scope of a trigger. You cannot refer to them from stored procedures, nor from dynamic SQL. They are completely local to the trigger. That is, if there are parallel processes updating the same table, and several instance of the trigger running, there is no conflict. Furthermore, if there is a trigger on table A that performs an update on table B which has a trigger which updates table A, the same process now have two isolated instances of "inserted" and "deleted".

    The table has exactly the same columns as the parent tables.

    Up SQL 2000, they were implemented with help of the transaction log. From SQL 2005, they are instead implemented from the version store in tempdb. The version store is also used to implement snapshot isolation and a few more things.

    Triggers can certainly affect performance, both because of the version store, and because of the code inside the trigger. Since triggers always
    executes in the context of a transaction, there is all reason to avoid lengthy executions that could hamper concurrency.

    One thing which is important to understand is that in SQL Server a trigger fires once per statement. It's very common to see people post trigger code where they read variables from inserted, as if only one row as affected. But you should always write your triggers to handle multi-row operations.

    There are two kinds of triggers in SQL Server: AFTER triggers and INSTEAD OF triggers. Unfortunately, there are no BEFORE triggers. INSTEAD OF triggers are kind of special - they're mainly intended for views - since they fire in place of the statement. This means that you must redo the statement, which in many cases is a barrier against using them.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Lac Monday, April 25, 2011 12:55 PM
    Sunday, April 24, 2011 3:57 PM

All replies

  • Forum post is a little bit to short to answer all your questions, I would recomend reading about DML triggers here: http://msdn.microsoft.com/en-us/library/ms187834.aspx

    and if something is unclear, ask on forums


    With kind regards
    Krystian Zieja
    http://www.projectnenvision.com
    Follow me on twitter
    My Blog
    Sunday, April 24, 2011 11:37 AM
  • I have already run through it, but did not find neither a relieving answer nor a good example...

    But does my question, wether the inserted table is global or trigger instance specific is too complicated to answer shortly ? Could be ideed, I just wonder why...

    Sunday, April 24, 2011 11:57 AM
  • INSERTED table is not global, so when your trigger is being ran the INSERTED contains values from your insert statement, not from other users (sessions) insert statements.
    With kind regards
    Krystian Zieja
    http://www.projectnenvision.com
    Follow me on twitter
    My Blog
    Sunday, April 24, 2011 12:11 PM
  • Thanks Krystian, that was really  helpful. And what happens, if the insertion rate of a process (batch) is faster than the trigger execution time would allow ? Will the INSERTED table continously grow during a given trigger  runtime ?
    Sunday, April 24, 2011 12:49 PM
  • The best practive for processing batches would be probably to disable triggers (the process would be faster). However if you cannot do it, becasue of business logic and you have to have insert trigger defined, it will act like this the trigger will execute for each insert statement the trigger will fire, and the trigger will have a local copy of INSERTED table (with values from only that insert statement), so the INSERTED table won't grow, but you will have many "local copies" of INSERTED table each with values from only that insert statement that invoked the trigger.

    As a side note:

    Please keep in mind that trigger fires once per each insert statement. It does not matter if insert statment inserts one row (in that case INSERTED table will have only one row) or one insert statement insert multiple rows (in that case INSERTED table will have multiple rows, but still only rows from the insert statement that invoked the trigger)


    With kind regards
    Krystian Zieja
    http://www.projectnenvision.com
    Follow me on twitter
    My Blog
    Sunday, April 24, 2011 2:13 PM
  • To add to what Krystian said, the DELETED  and INSERTED tables have before and after copies of the affected rows. Note that if a row is "touched" by a DML statement, that row will be in the special tables even if nothing was changed.

    Update triggers can have data in both tables, insert triggers only have data in the INSERTED table, and delete triggers only have data in the DELETED table.

    RE your question of what happens when a trigger itself modifies some columns... Within a trigger, you cannot modify the INSERTED or DELETED tables. If you want to make additional modifications, either to the table for which the trigger fired or other tables, you do that with a DML statement within the trigger. If those DML statements affect a table for which there is an appropriate trigger, that trigger will fire and have its own private copies of INSERTED and DELETED. You can nest triggers up to 32 levels, but anything approaching that would probably be considered bad practice.

    The initial DML statement, and all subsequent trigger executions caused by it, are in a single transaction.

    HTH


    Vern Rabe
    Sunday, April 24, 2011 3:25 PM
  • I don't know Interbase, but from what you say, there does not seem to be any radical differences. Yes, the syntax is different, but maybe not much beyond that.

    "inserted" and "deleted" are virtual tables. That is, they don't exist physically, and they are only accessible in the direct scope of a trigger. You cannot refer to them from stored procedures, nor from dynamic SQL. They are completely local to the trigger. That is, if there are parallel processes updating the same table, and several instance of the trigger running, there is no conflict. Furthermore, if there is a trigger on table A that performs an update on table B which has a trigger which updates table A, the same process now have two isolated instances of "inserted" and "deleted".

    The table has exactly the same columns as the parent tables.

    Up SQL 2000, they were implemented with help of the transaction log. From SQL 2005, they are instead implemented from the version store in tempdb. The version store is also used to implement snapshot isolation and a few more things.

    Triggers can certainly affect performance, both because of the version store, and because of the code inside the trigger. Since triggers always
    executes in the context of a transaction, there is all reason to avoid lengthy executions that could hamper concurrency.

    One thing which is important to understand is that in SQL Server a trigger fires once per statement. It's very common to see people post trigger code where they read variables from inserted, as if only one row as affected. But you should always write your triggers to handle multi-row operations.

    There are two kinds of triggers in SQL Server: AFTER triggers and INSTEAD OF triggers. Unfortunately, there are no BEFORE triggers. INSTEAD OF triggers are kind of special - they're mainly intended for views - since they fire in place of the statement. This means that you must redo the statement, which in many cases is a barrier against using them.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Lac Monday, April 25, 2011 12:55 PM
    Sunday, April 24, 2011 3:57 PM
  • ANSI/ISO OLD and NEW are the rough equivalents of INSERTED and DELETED tables. This came from the original Sybase SQL Server. But T-SQL doe snot have row-level triggers and BEFORE triggers, just AFTER and INSTEAD OF triggers.

    >> my purpose is to check the value of a column in the last inserted row, and based on that, I may need to update other columns in the same row ? <<

    Last inserted? SQL is a set-oriented language, so I insert sets of rows all at once.  From the narrative, I would put these updates into a procedure that has an INSERT INTO or MERGE statement in it. A skeleton like this:

    INSERT INTO Foobar (special_col, a, b, ..)
    SELECT @in_special_col,
    CASE WHEN special_col = 42 THEN 10 ELSE a END AS a,
    CASE WHEN special_col = 24 THEN -12 ELSE b END AS b, ..
    FROM Foobar;

    Now put the constraints you need in the DDL. Triggers are procedural; SQL programmer always try to use declarative code instead.


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    Sunday, April 24, 2011 5:05 PM
  • Thanks for the answers for all of you, they were really useful. I can see now, that the INSERTED and DELETED tables works as I supposed originally. Actually I experienced a really strange behaviour of a database and therefore I thought maybe my trigger is not using these tables in the right way. Apparently, there must be something else happening...
    Monday, April 25, 2011 1:06 PM