Lesson 18. Creating and Working with Triggers
A trigger is specialized type of stored procedure, which functions like an event procedure and that runs when data changes. You can create triggers that execute in response to inserts, updates, and deletes. Developers use triggers to enforce business rules and even to perform tasks such as inserting data into an audit log. This lesson explains:
How to create triggers
How to work with Insert, Update, and Delete triggers
What an Instead Of trigger is and why you’d want to use one
Downsides of triggers
Creating Triggers
Use the following steps to create or modify a trigger:
1. Click to expand the node for the table to which you want to add the trigger. The Triggers node appears (see Figure 18.1).
2. Right-click the Triggers node and select New Trigger. A new query window appears, enabling you to type the text for the trigger (see Figure 18.2).
3. Type the T-SQL that comprises the trigger.
4. Execute the CREATE TRIGGER statement (see Figure 18.3). After refreshing the list of triggers, it appears under the list of triggers associated with that table (see Figure 18.4).
The syntax for a trigger is as follows:
CREATE TRIGGER TriggerName
On TableName
FOR [INSERT], [UPDATE], [DELETE]
AS
--Trigger Code
The trigger shown in Figure 18.4 prints the full name associated with the person who was added to the table. Figure 18.5 shows the results of executing an INSERT INTO statement that inserts data into the Person table.
Creating an Insert Trigger
In the previous section, you saw a simple example of an insert trigger. Insert triggers are used to determine what happens after a row is inserted into a table. Insert triggers are often used to validate data being inserted or to insert information about the inserted row into an audit log.
The example we are going to explore inserts information into an audit log every time a row is inserted into the Person table. To work with this example, you first need to create the audit log table. The TSQL statement that follows creates this table:
CREATE TABLE tblAuditLog
(
BusinessEntityID int,
EmployeeName varchar(100),
AuditAction varchar(100),
AuditTimeStamp datetime
)
The insert trigger looks like this:
CREATE TRIGGER InsertPerson
ON Person.Person
FOR INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @BusinessEntityID int
DECLARE @FullName varchar(100)
DECLARE @AuditAction varchar(100)
SELECT @BusinessEntityID = BusinessEntityID
FROM inserted
SELECT @FullName =
(SELECT FirstName + ' ' + MiddleName +
' ' + LastName
FROM inserted)
SELECT @AuditAction = 'Row Inserted'
INSERT INTO tblAuditLog
(BusinessEntityID, EmployeeName,
AuditAction, AuditTimeStamp)
VALUES
(@BusinessEntityID, @FullName,
@AuditAction, GetDate())
END
This trigger is a more sophisticated and utilitarian version of the trigger introduced in the first section of the lesson. It first declares variables that will be populated with information about the inserted row. It then establishes the values of those variables based on the data being inserted. Finally, it inserts the contents of the variables, along with the current date and time, into the tblAuditLog table. Figure 18.6 shows the TSQL used to insert rows into the Person table. Figure 18.7 shows the resulting data added to the audit log.
Creating an Update Trigger
Whereas an Insert trigger executes when data is inserted into a table, an Update trigger executes any time data in an existing row is modified. The following is an example of a trigger that inserts data into an audit log whenever the user updates a row:
CREATE TRIGGER [Person].[UpdatePerson]
ON [Person].[Person]
FOR UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @BusinessEntityID int
DECLARE @FullName varchar(100)
DECLARE @AuditAction varchar(500)
If UPDATE(FirstName)
BEGIN
SELECT @AuditAction = 'First Name Updated, '
END
If UPDATE(MiddleName)
BEGIN
SELECT @AuditAction = @AuditAction + 'Middle Name Updated, '
END
If UPDATE(LastName)
BEGIN
SELECT @AuditAction = @AuditAction + 'Last Name Updated, '
END
SET @AuditAction = LEFT(@AuditAction, LEN(@AuditAction) - 1)
INSERT INTO tblAuditLog
(BusinessEntityID, EmployeeName,
AuditAction, AuditTimeStamp)
SELECT i.BusinessEntityID,
i.FirstName + ' ' + i.MiddleName +
' ' + i.LastName,
@AuditAction,
GetDate()
FROM inserted i, deleted d
WHERE i.BusinessEntityID = d.BusinessEntityID
END
This example inserts data into an audit log whenever the user modifies data in the Person table. The trigger uses the UPDATE function to determine which data was modified. While doing so, it builds a string that indicates which fields were updated. Finally, the trigger inserts the BusinessEntityID, the full name, the string containing the fields that were updated, and the current date into the tblAuditLog table. Figure 18.8 shows the TSQL used to update rows in the Person table. Figure 18.9 shows the resulting data added to the audit log.
Creating a Delete Trigger
A Delete trigger executes in response to a record being deleted. The following is an example of a trigger that inserts data into an audit log when a row is deleted:
CREATE TRIGGER [Person].[DeletePerson]
ON [Person].[Person]
AFTER DELETE
AS
BEGIN
DECLARE @BusinessEntityID int
DECLARE @FullName varchar(100)
DECLARE @AuditAction varchar(500)
SET @AuditAction = 'Record Deleted'
INSERT INTO tblAuditLog
(BusinessEntityID, EmployeeName,
AuditAction, AuditTimeStamp)
SELECT d.BusinessEntityID,
d.FirstName + ' ' + d.MiddleName +
' ' + d.LastName,
@AuditAction,
GetDate()
FROM deleted d
END
This example inserts data into an audit log when a record is deleted from a table. Notice that it inserts the BusinessEntityID, employee first, middle, and last name, the contents of the AuditAction variable, and the current date into the audit log. The resulting rows appear in Figure 18.10.
Another use of a Delete trigger is to abort a deletion when certain conditions are met. Here’s an example:
CREATE TRIGGER [Production].[NoDeleteActive]
ON [Production].[Product]
FOR DELETE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ErrorMessage varchar(100)
SELECT @ErrorMessage = 'Active Product Cannot be Deleted'
DECLARE @Discontinued datetime
SELECT @Discontinued =
(SELECT DiscontinuedDate FROM deleted)
IF @Discontinued IS NULL
BEGIN
ROLLBACK TRAN
RAISERROR (@ErrorMessage,
16, 1)
END
END
This trigger evaluates to see whether the product the user is attempting to delete is active (DiscontinuedDate is null). If it is, SQL Server aborts the delete process and displays an error message. Figure 18.11 illustrates the error message that occurs when an attempt is made to delete a record with no value in the DiscontinuedDate.
FIGURE 18.11 Error message that occurs when a record without a value in the DiscontinuedDate field is deleted.
Downsides of Triggers
Many developers avoid triggers entirely. Probably the biggest disadvantage of triggers is that although they appear as a node under the table that they are associated with, they get buried in your database and are difficult to debug and troubleshoot. Triggers also slow down database operations. Furthermore, they often lock data for relatively long periods of time, increasing the chance of concurrency problems. For these reasons, many developers opt to utilize stored procedures and functions to replace the role of triggers in the applications that they build. In reality, triggers have their place. The examples shown in this lesson provide practical uses of triggers.
Summary
Triggers are cousins of stored procedures. They are “special” stored procedures that execute automatically in response to data being inserted, updated, or deleted. Although they do have their limitations, triggers are an excellent tool to use when you want to insert data into an audit log when a table’s data is in any way modified. You can also use triggers to control the insert, update, and delete processes.
Q&A
Q. Explain what a trigger is.
A. A trigger is like an event procedure that runs when data changes. You create triggers that respond to inserts, updates, and deletes.
Q. Explain why developers use triggers.
A. Developers use triggers to enforce business rules and even to perform tasks such as inserting data into an audit log.
Q. Describe some disadvantages of triggers.
A. Triggers can be difficult to troubleshoot, can increase locking conflicts, and can degrade performance when modifying data.
Workshop
Quiz
1. List three types of triggers.
2. You can use a trigger to cancel the deletion of a record (true/false).
3. You can use a trigger to increment and decrement a value such as an inventory balance when a process modifies data (true/false).
4. Triggers only execute when the user of a database modifies its data (true/false).
Quiz Answers
1. Insert, Update, Delete.
2. True.
3. True.
4. False. One of the benefits of triggers is that they execute regardless of how the data is modified. In other words, a trigger executes even as the result of a stored procedure modifying data.
Activities
Create three triggers on the Sales.SalesOrderDetail table. Have all three triggers (Insert, Update, and Delete) add data to a table called tblSalesAuditLog. First create tblSalesAuditLog. Include SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID, UnitPrice, and LineTotal. When any process inserts data into the SaleOrderDetail table, add data from the appropriate fields into the audit log. When any process updates data within the SalesOrderDetail table, add the modified values (not the original values) into the tblSalesAuditLog table. Finally, when a process deletes table data, insert the deleted data into the tblSalesAuditLog table.