Introducing Triggers
A database trigger is a special kind of stored procedure that is run automatically by the
database-or in trigger terms, fired-after a specified INSERT, UPDATE, or DELETE
statement is run against a specified database table. Triggers are very useful for doing
things such as auditing changes made to column values in a table.
A trigger can also fire instead of an INSERT, UPDATE, or DELETE. For example,
instead of performing an INSERT to add a row to the Products table, a trigger could raise
an error if a product with the same ProductID already existed in the table.
As mentioned, triggers are very useful for auditing changes made to column values. In
this section, you'll see an example of a trigger that will audit changes made to the
Products table.
Also, when an UPDATE statement modifies the UnitPrice column of a row in the
Products table, a row will be added to the ProductAudit table. Finally, when a DELETE
statement removes a row from the Products table, a row will be added to the
ProductAudit table.
Before you see the triggers, you'll need to create the ProductAudit table. Listing 4.6
shows the ProductAudit.sql script that creates the ProductAudit table.
Listing 4.6: PRODUCTAUDIT.SQL
/*
ProductAudit.sql creates a table that is used to
store the results of triggers that audit modifications
to the Products table
*/
USE Northwind
CREATE TABLE ProductAudit (
ID int IDENTITY(1, 1) PRIMARY KEY,
Action nvarchar(100) NOT NULL,
PerformedBy nvarchar(15) NOT NULL DEFAULT User,
TookPlace datetime NOT NULL DEFAULT GetDate()
)
The IDENTITY clause creates an identity for the ID primary key column of the
ProductAudit table. An identity automatically generates values for a column. The identity
for the ID column starts with the value 1, which is incremented by 1 after each INSERT.
The Action column stores a string that records the action performed, for example,
'Product added with ProductID of 80'. The PerformedBy column stores the name of the
user who performed the action; this is defaulted to User, which returns the current user.
The TookPlace column stores the date and time when the action took place; this is
defaulted using the GetDate() function, which returns the current date and time.
In the following sections, you'll learn how to create and use the following triggers:
• InsertProductTrigger Fires after an INSERT statement is performed on the
Products table.
• UpdateUnitPriceProductTrigger Fires after an UPDATE statement is performed
on the Products table.
• DeleteProductTrigger Fires after a DELETE statement is performed on the
Products table.
First off, let's examine InsertProductTrigger.
Creating InsertProductTrigger
You create a trigger using the CREATE TRIGGER statement. Listing 4.7 shows the
InsertProductTrigger.sql script that creates the trigger InsertProductTrigger, which audits
the addition of new rows to the Products table.
Listing 4.7: INSERTPRODUCTTRIGGER.SQL
/*
InsertProductTrigger.sql creates a trigger that fires
after an INSERT statement is performed on the
Products table
*/
CREATE TRIGGER InsertProductTrigger
ON Products
AFTER INSERT
AS
-- don't return the number of rows affected
SET NOCOUNT ON
-- declare an int variable to store the new
-- ProductID
DECLARE @NewProductID int
-- get the ProductID of the new row that
-- was added to the Products table
SELECT @NewProductID = ProductID
FROM inserted
-- add a row to the ProductAudit table
INSERT INTO ProductAudit (
Action
) VALUES (
'Product added with ProductID of ' +
CONVERT(nvarchar, @NewProductID)
)
There are several things you should notice about this CREATE TRIGGER statement:
• The AFTER INSERT clause specifies that the trigger is to fire after an INSERT
statement is performed.
• SET NOCOUNT ON prevents the trigger from returning the number of rows
affected. This improves performance of the trigger.
• You can retrieve column values for the INSERT statement that caused the trigger
to fire by performing a SELECT against the special inserted table. For example,
you can retrieve all the columns of a newly added row using SELECT * FROM
inserted. The trigger code retrieves the ProductID column of the new row from the
inserted table.
• The INSERT statement that adds a row to the ProductAudit table supplies a value
only for the Action column. This is because the ID, PerformedBy, and TookPlace
column values are set automatically by SQL Server.
You can also create, edit, and delete triggers using Enterprise Manager. You do this by
first clicking the Tables node in the Databases folder, then clicking the right mouse
button on the table you want to modify, and then selecting All Tasks ➣ Manage
Triggers. Figure 4.8 shows InsertProductTrigger in Enterprise Manager. You'll notice I've
added some comments to the start of the code that indicates what the trigger does.
Figure 4.8: Using Enterprise Manager to view a trigger
The Object Browser of Query Analyzer also allows you to view, modify, and delete
triggers.
Tip You can delete a trigger using the DROP TRIGGER statement, and you can modify a
trigger using the ALTER TRIGGER statement.
Testing InsertProductTrigger
To test InsertProductTrigger, all you have to do is to add a row to the Products table
using an INSERT statement. For example:
INSERT INTO Products (
ProductName, SupplierID, UnitPrice
) VALUES (
'Widget', 1, 10
)
You can check that InsertProductTrigger fired by retrieving the rows from the
ProductAudit table using the following SELECT statement:
SELECT *
FROM ProductAudit
The row added to the ProductAudit table by InsertProductTrigger as a result of
performing the previous INSERT statement is shown in Table 4.8.
Table 4.8: ROW ADDED TO THE ProductAudit TABLE BY InsertProductTrigger
ID ACTION PERFORMEDBY TOOKPLACE
1 Product added with
ProductID of 80
dbo 2002-07-18 13:55:12.620
Creating and Testing UpdateUnitPriceProductTrigger
The UpdateUnitPriceProductTrigger trigger fires after an UPDATE statement is
performed on the UnitPrice column of the Products table. If the reduction of the unit price
of a product is greater than 25 percent, then a row is added to the ProductAudit table to
audit the change. Listing 4.8 shows the UpdateUnitPriceProductTrigger.sql script.
Listing 4.8: UPDATEUNITPRICEPRODUCTTRIGGER.SQL
/*
UpdateUnitPriceProductTrigger.sql creates a trigger
that fires after an UPDATE statement is performed on the
the UnitPrice column of the Products table.
If the reduction of the unit price of a product is
greater than 25% then a row is added to the ProductAudit table
to audit the change.
*/
CREATE TRIGGER UpdateUnitPriceProductTrigger
ON Products
AFTER UPDATE
AS
-- don't return the number of rows affected
SET NOCOUNT ON
-- only run the code if the UnitPrice column
-- was modified
IF UPDATE(UnitPrice)
BEGIN
-- declare an int variable to store the
