CODEWITHSUNDEEP
sql-server
Jan Krakora
Jan Krakora

Reputation: 2610

What is the proper way to write insert triggers in SQL Server?

My question is a little bit theoretical because I don't have any concrete working example. But I think it's worth to answer it.

What is the proper way to write insert-triggers in SQL Server?

Let's say I create a trigger like this (more or less pseudocode)

CREATE TRIGGER MY_TRIGGER 
ON MY_TABLE
FOR INSERT AS 
    DECLARE @myVariable;            
    DECLARE InsertedRows CURSOR FAST_FORWARD FOR SELECT A_COLUMN FROM INSERTED; 

    OPEN InsertedRows;
    FETCH NEXT FROM InsertedRows INTO @NewOrderCode;

    ...

        INSERT INTO ANOTHER_TABLE (                    
            CODE,
            DATE_INSERTED
        ) VALUES (                     
            @myVariable,
            GETDATE()       
        );

    ...etc

Now what if someone else create another trigger on the same table and that trigger would change some columns on inserted rows? Something like this

CREATE TRIGGER ANOTHER_TRIGGER 
ON MY_TABLE
FOR INSERT AS 
    UPDATE MY_TABLE 
    SET A_COLUMN = something 
    WHERE ID IN (SELECT ID FROM INSERTED);

...etc

Then my trigger (if fired after the another trigger) operates on wrong data, because INSERTED data are not the same as the real inserted data in the table which have been changed with the other trigger right?

Summary:

Trigger A updates new inserted rows on table T, trigger B then operates on dirty data because the update from trigger A is not visible in the INSERTED pseudo table which trigger B operates on. BUT if the trigger B would operate directly on the table instead of on the pseudo table INSERTED, it would see updated data by trigger A.

Is that true? Should I always work with the data from the table itself and not from the INSERTED table?

Upvotes: 0

Views: 214

Answers (4)

Ken Clement
Ken Clement

Reputation: 768

My Personal Guide to SQL Trigger Happiness

  1. The trigger should be light and fast. Expensive triggers make for a slow database for EVERYBODY (and not incidentally unhappiness for everybody concerned including the trigger author)
  2. One trigger operation table combo please. That is at most one insert trigger on the foo table. Though the same trigger for multiple operations on a table is not necessarily bad.
  3. Don't forget that the inserted and deleted tables may contain more than a single row or even no rows at all. A happy trigger (and more importantly happy database users and administrators) will be well-behaved no matter how many rows are involved in the operation.
  4. Do not Not NOT NOT ever use cursors in triggers. Server-side cursors are usually an abuse of good practice though there are rare circumstances where their use is justified. A trigger is NEVER one of them. Prefer instead a series of set-oriented DML statements to anything resembling a trigger.
  5. Remember there are two classes of triggers - AFTER triggers and INSTEAD OF triggers. Consider this when writing a trigger.
  6. Never overlook that triggers (AFTER or INSTEAD OF) begin execution with @@trancount one greater than the context where the statement that fired them runs at.
  7. Prefer declarative referential integrity (DRI) over triggers as a means of keeping data in the database consistent. Some application integrity rules require triggers. But DRI has come a long way over the years and functions like row_number() make triggers less necessary.

Upvotes: 1

marc_s
marc_s

Reputation: 755033

One thing you should absolutely avoid in a trigger is using a CURSOR!

A trigger should be very nimble, small, fast - and a cursor is anything but! After all, it's being executed in the context of the transaction that caused it to fire. Don't delay completion of that transaction unnecessarily!

You need to also be aware that Inserted will contain multiple rows and write your trigger accordingly, but please use set-based techniques - not cursors and while loops - to keep your trigger quick and fast.

Don't do heavy lifting, time-consuming work in a trigger - just updating a few columns, or making an entry into another table - that's fine - NO heavy lifting! and no e-mail sending etc!

Upvotes: 3

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

I'd usually recommend against having multiple triggers. For just two, you can, if you want to, define what order you want them to run in. Once you have a few more though, you have no control over the order in which the non-first, non-last triggers run.

It also increasingly makes it difficult just to reason about what's happening during insert.

I'd instead recommend having a single trigger per-table, per-action, that accomplishes all tasks that should happen for that action. If you're concerned about the size of the code that results, that's usually an indication that you ought to be moving that code out of the trigger all together - triggers should be fast and light.

Instead, you should start thinking about having the trigger just record an action and then use e.g. service broker or a SQL Server job that picks up those records and performs additional processing. Importantly, it does that within its own transactions rather than delaying the original INSERT.

I would also caution against the current code you're showing in example 1. Rather than using a cursor and inserting rows one by one, consider writing an INSERT ... SELECT statement that references inserted directly and inserts all new rows into the other table.

Upvotes: 4

eftpotrm
eftpotrm

Reputation: 2281

Triggers are transactional. If you tried to do a circular update as you've described, it should result in a deadlock - the first update will block the second from completing.

While looking at this code though, you're trying to cursor through the INSERTED pseudo-table to do the inserts - nothing in the example requires that behaviour. If you just insert directly from the full INSERTED table you'd get a definite improvement, and also less firings of your second trigger.

Upvotes: 0

Related Questions