Posted by: rcosic | 14/05/2009

SQL Triggers

trigger

This is one of those ‘reminding’ posts about the technology you should already know and master …

Alas, what can be said more about triggers in SQL Server 2005/2008?

First, I have to frankly admit,  that the general opinion about the triggers is that you should use them with caution. That means, only if you have a good reason for it. For example, to make auditing, or to enforce constraints if you can’t perform it via check constraints or other way.

Nesting of triggers is possible (one trigger calls another), and recursive triggers also may work (one trigger calls itself by n-times). You must be careful when using recursing though, because there is a limit of 32 recursive calls, and you should code some sort of a recursion termination check.

ALTER DATABASE EkobitPilot SET RECURSIVE_TRIGGERS ON;

Creating a trigger is easy. Only thing you should do is to decide to which object (a table, or even a view) you want to attach it, and on which operation you want to fire it (INSERT, UPDATE, or DELETE). You can choose multiple actions, but then you should take care of it inside the trigger’s code.

CREATE TRIGGER [schema_name].[table_name]
ON [schema_name].[table_name]
FOR [operation]
AS ...

Also, normally trigger fires AFTER the operation finishes (as part of an implicit transaction), but you can make it fire INSTEAD OF the operation. For example, instead of inserting row, you can override it with an update operation. This is useful if you want, for example, to implement versioning mechanism, as I’ve explained it in my previous posts.

CREATE TRIGGER [schema_name].[table_name]
ON [schema_name].[table_name]
AFTER [operation]
AS ...

Useful constant is @@ROWCOUNT, which tells you the number of affected rows (or number of potential rows being updated in case of INSTEAD OF trigger). For figuring out which operation occured (INSERT, UPDATE, or DELETE), you should inspect two special tables : inserted and deleted – they contain snapshots with new and old values of columns.

When you design a trigger, pay attention to the fact that a trigger fires per operation, not per row. That means, it will not fire four times if you update four rows, but rather only one time! You should join the inserted and/or deleted tables with your tables instead of just read the values from the SELECT statement.

The whole trigger-snippet is presented below:

CREATE TRIGGER dbo.tCustomerAfterOperation
   ON dbo.Customer
   AFTER INSERT, DELETE, UPDATE -- executes AFTER the operation
   -- INSTEAD OF INSERT/... -- executes INSTEAD OF an operation!
AS
BEGIN
   SET NOCOUNT ON;
           
   -- determining if the operation affected some rows
   IF (@@ROWCOUNT > 0)
   BEGIN
 
       -- simple check-up for determining which operation took place
       IF EXISTS(SELECT * FROM inserted)
       BEGIN
           IF EXISTS(SELECT * FROM deleted)
              PRINT 'Update operation identified';
           ELSE
              PRINT 'Insert operation identified';
       END
       ELSE
              PRINT 'Delete operation';
 
       -- handling versioning of table (only for a single row) - ERROR!
       UPDATE Customer
       SET RealId = (SELECT CustomerId FROM inserted)
       WHERE CustomerId = (SELECT CustomerId FROM inserted);
 
       -- handling versioning of table (for multiple rows)
       UPDATE c
       SET RealId = c.CustomerId
       FROM Customer c, inserted i
       WHERE c.CustomerId = i.CustomerId;
 
      -- identifying that CustomerBonus has changed (auditing example)
      -- Note: it should be placed inside update operation code block
      IF UPDATE(CustomerBonus)
      BEGIN
         INSERT INTO AuditTable (ObjectType, ObjectIdentifier, OldValue,
             NewValue, ModifiedBy, ModifiedDate, Description)
         SELECT 'Customer', d.CustomerId, d.CustomerBonus,
             i.CustomerBonus, SUSER_SNAME(), GETDATE(),
             'CustomerBonus has been changed for ...'
         FROM inserted i
         INNER JOIN deleted d ON i.CustomerId = d.CustomerId;
      END;
  END;
          
END
GO

You can find out which column has changed its value by calling UPDATE() function. Another way is to call UPDATED_COLUMNS() function which returns boolean values determining updated columns in shape of flags. I think it is not well designed, and I prefer the first way.

SQL Server 2005 added a possibility to create DDL Triggers. That means, you can control or inspect the information about doing some DDL operation, like CREATE TABLE, ALTER COLUMN, or DELETE LOGIN. This is quite useful if you want to enforce some constraints regarding best practices in database design. For example, you can enforce the policy which denies the creation of a table without defining its primary key:

CREATE TRIGGER tCreateTableWithPrimaryKey
ON DATABASE
FOR CREATE_TABLE
AS ...
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: