Triggers                                                                     

A trigger is an object contained within an SQL Server database that is used to execute a batch of SQL code whenever various operations are performed on a table, such as inserts, updates, or deletes. Triggers are associated with a single table, and are automatically executed internally by SQL Server.  As the name suggests, a trigger is “fired” whenever an INSERT, UPDATE, or DELETE SQL command is executed against a specific table.

Triggers are "attached" to a table and allow us to setup our database in such a way that whenever a record is added, updated, or deleted from a table, then SQL server will automatically execute a batch of SQL code after that table modification takes place. The most common use of a trigger is to enforce business rules in the database.


Virtual Tables

Triggers make use of virtual tables, which hold the items that are about to be inserted or deleted.  A trigger provides access to the data in the virtual tables, and that fact can be used to manipulate all kinds of logic.  "UPDATE" triggers have access to two virtual tables: Deleted (which contains all of the fields and values for the records before they were updated), and Inserted (which contains all of the fields and values for the records after they have been updated).

Triggers make use of two special tables called inserted and deleted. The inserted table contains the data referenced in an INSERT before it is actually committed to the database. The deleted table contains the data in the underlying table referenced in a DELETE before it is actually removed from the database. When an UPDATE is issued both tables are used. More specifically, the new data referenced in the UPDATE statement is contained in inserted and the data that is being updated is contained in deleted.


Trigger Types

There are three types of conditions that will initiate a trigger, so there are three main types of triggers you can create: Insert, Update, and Delete. You can have multiples of each of those (or all of them) assigned on a single table.

Insert triggers are fired whenever an insert operation is performed on a table. You'll often see these used to enforce referential integrity in situations where another type of constraint isn't advisable. You'll also see them used to populate another table during an insert.

An update trigger will fire each time any row (or rows!) is updated. Update triggers can also be used to check field constraints and relationships.

Insert and update triggers are particularly useful because they can enforce referential integrity constraints and ensure that your data is valid before it enters the table.

Delete Triggers are fired whenever a delete operation is performed on a table.  Delete triggers are typically used for two reasons. The first reason is to prevent deletion of records that will cause data integrity problems if they indeed are deleted. An example of such records are those used as foreign keys to other tables.  The second reason for using a Delete trigger is to perform a cascading delete operation that deletes children records of a master record.

A trigger only fires once for each associated statement.


Example 1 from http://www.devbuilder.org/asp/dev_article.asp?aspid=16 

Start by opening Enterprise Manager. In this example we will create our trigger against the “authors” table of the "pubs" database, so drill down through the tree view in the left pane until you can see the "“authors" table of the "pubs" database in the right pane, like this:

Selecting the authors table of the pubs database

Next, right click on the "authors" table and choose All Tasks -> Manage Triggers... this will open the trigger properties window, which allows us to create a new trigger:

The trigger properties window

Delete all the text in the text box; we won’t need it because we’re creating our trigger from absolute scratch. All triggers are created using the "CREATE TRIGGER" command. The syntax of the "CREATE TRIGGER" command is shown below:

CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ]
[ ...n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [ ...n ]
} ]
sql_statement [ ...n ]
}
}


Enter the following SQL code into the text box:

CREATE TRIGGER trig_addAuthor
ON authors
FOR INSERT
AS

-- Get the first and last name of new author
DECLARE @newName VARCHAR(100)
SELECT @newName = (SELECT au_fName + ' ' + au_lName FROM Inserted)

-- Print the name of the new author
PRINT 'New author "' + @newName + '" added.'


Click on the "OK" button. We have just created a new trigger named "trig_addAuthor", which is attached to the "authors" table of the "pubs" database. Whenever a new record is added to the "authors" table, SQL Server will automatically execute our trigger.

Let’s discuss the actual SQL code that makes up the trigger:

CREATE TRIGGER trig_addAuthor
ON authors


These two lines tell SQL server that we want to create a new trigger object named "trig_addAuthor", which will be attached to the "authors" table.

FOR INSERT

Here, we have specified that our trigger will be executed whenever an "INSERT" command is executed against the "authors" table. Other possible options include "UPDATE" and "DELETE", which would be triggered when one/more rows in the "authors" table were either updated or deleted.

You can handle more than one type of query in one trigger. For example, to handle both "INSERT" and "UPDATE", we would use "FOR INSERT, UPDATE".

AS

DECLARE @newName VARCHAR(100)
SELECT @newName = (SELECT au_fName + ' ' + au_lName FROM Inserted)


Any code after the "AS" keyword is actually executed when the trigger is called. It’s important to note that this part of the trigger can contain any code that a standard stored procedure could contain. You can also call stored procedures using the "EXEC" command from within the body of the trigger. See example.

A new variable named "newName" has been created. "newName" is a variable length character value that can hold a maximum of one hundred characters. The next line assigns the value of an SQL query to the "newName" variable.

SELECT au_fName + ' ' + au_lName FROM Inserted

This SQL command retrieves the au_fName and au_lName fields from the "Inserted" table. The "Inserted" table is a virtual table that contains all of the fields and values from the actual "INSERT" command that made SQL Server call the trigger in the first place.

Let's take a look at the design of the actual "authors" table in the "pubs" database. Right click on it and choose Design Table:

The schema of the authors table

A typical "INSERT" query to add a record to the "authors" table might look like this:

INSERT INTO authors(au_id, au_lname, au_fname, phone, address, city, state, zip, contract) VALUES('172-85-4534', 'Doe', 'John', '123456', '1 Black Street', 'Doeville', 'CA', '90210', 0)

When SQL server processes this "INSERT" command, it creates a new virtual table, which contains all nine of the fields in the "INSERT" command. This table is named "Inserted", and is passed to the trig_addAuthor trigger. The table is named "Inserted" because it contains all of the newly added fields and values from our "INSERT" command.

If we created a trigger that was activated when we deleted a record from the "authors table (using the "FOR DELETE" syntax), then the virtual table would contain all of the fields and values from the deleted record(s), and would be named "Deleted".

Likewise, if we created a trigger for when an authors details were updated (using the "FOR UPDATE" syntax), then both the "Inserted" and "Deleted" virtual tables would be created and available from within the trigger. The "Deleted" table would contain all of the fields and values for the row(s) before they were updated, and the "Inserted" table would contain the new row(s) with the updated fields and values.

When dealing with triggers, you must understand how they actually operate on the data contained within their virtual tables. Let’s say that we run an "UPDATE" command on the "authors" table, which has a trigger attached to it. The "UPDATE" command might affect more than one row.

When this is the case, the "UPDATE" trigger is called for each row that was affected by the update command. So, at any one time, each trigger only deals with one row. <incorrect!>

However many rows were affected by the INSERT, UPDATE, or DELETE is how many rows will be in the trigger table.  So you must code the trigger to handle multiple rows.

PRINT 'New author "' + @newName + '" added.'

Lastly, we print the "newName" variable, which now contains the full name of the new author that has just been added.

To test our new trigger, start Query Analyzer and connect to your database server. Enter the following code into the SQL query pane:

USE pubs
GO

SET NOCOUNT ON

INSERT INTO authors(au_id, au_lname, au_fname, phone, address, city, state, zip, contract)
VALUES('172-85-4534', 'Doe', 'John', '123456', '1 Black Street', 'Doeville', 'CA', '90210', 0)


Click the "Run" button, or press the F5 function key to execute our "INSERT" statement. SQL Server will add the new record to the "authors" table, automatically calling our "trig_addAuthor" trigger once it's done. This is shown in the example below:


The results of adding a new record to the authors table


Example 2 from http://www.devbuilder.org/asp/dev_article.asp?aspid=16 

Let's take a look at "UPDATE" and "DELETE" triggers. Here's an "UPDATE" trigger:

CREATE TRIGGER trig_updateAuthor
ON authors
FOR UPDATE
AS

DECLARE @oldName VARCHAR(100)
DECLARE @newName VARCHAR(100)

IF NOT UPDATE(au_fName) AND NOT UPDATE(au_lName)
BEGIN
RETURN
END

SELECT @oldName = (SELECT au_fName + ' ' + au_lName FROM Deleted)
SELECT @newName = (SELECT au_fName + ' ' + au_lName FROM Inserted)

PRINT 'Name changed from "' + @oldName + '" to "' + @newName + '"'


This trigger will automatically be executed whenever we update one/more records in the "authors" table. It starts by creating two new variables: oldName and newName. The "UPDATE" function is used to check whether or not the "au_fName" and "au_lName" fields have been updated by the "UPDATE" query that executed the "trig_updateAuthor" trigger. If both fields haven't, then the trigger returns control to SQL server.

Recall that "UPDATE" triggers have access to two virtual tables: Deleted (which contains all of the fields and values for the records before they were updated), and Inserted (which contains all of the fields and values for the records after they have been updated). We get the value of the users name before the update from the "Deleted" table and store it in the "oldName" variable.

The updated name is stored in the "newName" variable, and is extracted from the virtual table, "Inserted". Lastly, both the authors name before and after the update query are printed.

So, if we ran an update query (through Query Analyzer) like this:

UPDATE authors
SET au_lName = 'Black'
WHERE au_id = '172-32-1176'


... then Query Analyzer would display the following text in the results pane:

Name changed from "John Doe" to "John Black"

Update triggers can also be used to check field constraints and relationships. The "contract" field of the "authors" table is a bit field representing whether or not this author has a contract with their publisher. The publisher may require notification of when an author who is on contract is removed from the "authors" table.

We could create a "DELETE" trigger on the "authors" table that would do this for us automatically:

CREATE TRIGGER trig_delAuthor
ON authors
FOR DELETE
AS

DECLARE @isOnContract BIT
SELECT @isOnContract = (SELECT contract FROM Deleted)

IF(@isOnContract = 1)
    BEGIN
        -- Code to notify publisher goes here
        EXEC master..xp_sendmail @recipients =  "publisher@clearinghouse.com",
                  @message = "The following author is on contract but has been removed from the authors table.",
                  @query = "SELECT au_fName + ' ' + au_lName FROM Deleted"
                  @subject = "author removed"

    END


The "DELETE" trigger follows the same format and keyword syntax as the "INSERT" and "UPDATE" triggers. The only difference is that the "DELETE" trigger has access to the virtual table "Deleted", which contains all of the deleted rows from the "DELETE" command that triggered the "trig_delAuthor" trigger in the first place.


Example 3 from http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=59&rl=1

Here is another simple example using the pubs database. We'll put on an insert trigger that concatenates the first and last names and then inserts them into another table. First we'll need a table to hold the results:

CREATE TABLE [dbo].[TriggerTest]
(
[au_id] [int] NULL,
[au_name] [varchar] (50)
)
GO

The trigger syntax for an insert on the authors table follows.  Each time data is inserted into the authors table, the TriggerTest table gets a new row.  In-line comments (with two dashes) are included:

-- Start of command
CREATE TRIGGER tr_InsertConcatName
-- The table name that causes the trigger to fire
ON authors
-- The type of trigger I want
FOR INSERT
AS
-- Two variables to hold the id's and names
DECLARE @AuthorID VARCHAR(11)
DECLARE @ConcatName VARCHAR(50)

-- The following lines make use of the inserted virtual table called Inserted,
-- setting the values of the variables to the data the user
-- sends.
SELECT @AuthorID = (SELECT au_id FROM Inserted)
SELECT @ConcatName = (SELECT au_lName + ', ' + au_fName FROM Inserted)
-- And now use those variables to insert data into
-- the TriggerTest table
INSERT INTO TriggerTest values (@AuthorID, @ConcatName)

Notice the use of the virtual table. Each time data is inserted into the authors table, the TriggerTest table also gets a new row:

INSERT INTO authors (au_id, au_lname, au_fname, contract)
VALUES ('123-45-6789', 'Woody', 'Buck', 1
)

And now I'll select the data from that TriggerTest table to see if all this worked:

SELECT * FROM TriggerTest
GO
------------------
123-45-6789 Woody, Buck
----------


Example 4 from http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=59&rl=1

If someone updates the values in the authors table the information in the TriggerTest table will be inaccurate.  This can be addressed using the next type of trigger we have at our disposal, the Update Trigger. Once again, let's take a look at an example:

-- I start the trigger creation
CREATE TRIGGER tr_UpdateConcatName
-- Here's the operation type
FOR UPDATE

AS
-- I'll set up the variables,
-- This one holds the id
DECLARE @AuthorID VARCHAR(11)
-- and this one holds the name
DECLARE @NewConcatName VARCHAR(50)
-- Now I'll make use of the second virtual table, setting
-- the variables to values from that table.
SELECT @AuthorID = (SELECT au_id FROM Inserted)
SELECT @NewConcatName = (SELECT au_lName + ', ' + au_fName FROM Inserted)
-- And now I'll use those variables to update the data in
-- the TriggerTest table
UPDATE TriggerTest
SET au_name = @NewConcatName
WHERE au_id = @AuthorID

Did you notice that I mentioned that I used the second virtual table? In an update operation, what really happens is a delete operation and then an insert operation. That means there will be a virtual table holding the delete operations (called deleted) and one holding the insert operations (called inserted). I'll use the inserted table to create the new values to keep my other table in sync, since it contains the data I want. It's important to keep this in mind, since you may need to use the older set of values in your programming logic for the comparisons.

Let's try this out:

UPDATE authors
SET au_fname = 'Greg'
WHERE au_id = '123-45-6789'
GO
SELECT * from TriggerTest
-----------
Woody, Greg
-----------


Example 5 from http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=59&rl=1

The delete trigger is often used to enforce cascading deletes. You can perform the same thing by setting up a cascading delete operation on the table, but sometimes you want to reserve that logic for a trigger.

For now, I'll set up a delete trigger to remove records from my TriggerTest table when they are removed from the authors table. Once again, watch for the comments:

-- I start the create command
CREATE TRIGGER tr_DeleteConcatName
-- and here is the table name
ON authors
-- the operation type goes here
FOR DELETE

AS
-- I just need one variable this time
DECLARE @AuthorID VARCHAR(11)
-- Now I'll make use of the deleted virtual table
SELECT @AuthorID = (SELECT au_id FROM Deleted)
-- And now use that value to delete the data in the TriggerTest Table
DELETE FROM TriggerTest
WHERE au_id = @AuthorID

Running this SQL Statement:

DELETE FROM authors
WHERE au_lname = 'Woody
'

Erases the entry from the TriggerTest table.


Closing Comments

Be careful with triggers, since they are fired for each operation. Every insert, update, or delete causes the code to run, which creates overhead. To be sure, there are times when a trigger is the only way to go, but be warned that they do have a performance impact.

Before triggers came along, if we had a table that needed to be updated and we wanted to perform some actions after that update, then we would have to "hard code" the extra SQL into our application. This meant that if we wanted to change the code later down the track, then each client would need the updated version of our application. This is both annoying and time consuming. When triggers are used correctly, they can save a lot of development work. One of the main benefits of using triggers is that they are stored in a central repository (the database), meaning that they are accessible from all client applications / web pages that can connect to the database.


Note: To access a trigger after saving it, right click the table that the trigger is associated with, click All Tasks and then Manage Triggers, click the list box associated with "Name:" and select the trigger.


Adapted from

Rusik, D. (2004). "Using Triggers In MS SQL Server," http://www.devbuilder.org/asp/dev_article.asp?aspid=16 

Wells, G. (2001). "An Introduction to Triggers -- Part I," 'http://www.sqlteam.com/item.asp?ItemID=3850

Woody, B. (2005). "Database Objects: Triggers," http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=59&rl=1