Introducing Triggers | Mastering the SAP Business Information Warehouse: Leveraging the Business Intelligence Capabilities of SAP NetWeaver

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   -- ProductID   DECLARE @MyProductID int   -- declare two money variables to store the   -- old unit price and the new unit price   DECLARE @OldUnitPrice money   DECLARE @NewUnitPrice money   -- declare a float variable to store the price   -- reduction percentage   DECLARE @PriceReductionPercentage float   -- get the ProductID of the row that   -- was modified from the inserted table   SELECT @MyProductID = ProductID   FROM inserted   -- get the old unit price from the deleted table   SELECT @OldUnitPrice = UnitPrice   FROM deleted   WHERE ProductID = @MyProductID   -- get the new unit price from the inserted table   SELECT @NewUnitPrice = UnitPrice   FROM inserted   -- calculate the price reduction percentage   SET @PriceReductionPercentage =     ((@OldUnitPrice -@NewUnitPrice) / @OldUnitPrice) * 100   -- if the price reduction percentage is greater than 25%   -- then audit the change by adding a row to the PriceAudit table   IF (@PriceReductionPercentage > 25)   BEGIN     -- add a row to the ProductAudit table     INSERT INTO ProductAudit (       Action     ) VALUES (       'UnitPrice of ProductID #' +         CONVERT(nvarchar, @MyProductID) +       ' was reduced by ' +         CONVERT(nvarchar, @PriceReductionPercentage) +         '%'       )     END   END

There are a couple of things you should notice about this CREATE TRIGGER statement:

The AFTER UPDATE clause specifies that the trigger is to fire after an UPDATE statement is performed.
You can retrieve the old column values before the UPDATE was applied from the deleted table, and you can retrieve the new column values after the UPDATE was applied from the inserted table.

To test UpdateUnitPriceProductTrigger, all you have to do is to reduce the value of the UnitPrice column for a row in the Products table using an UPDATE statement. For example, the following UPDATE statement multiplies the UnitPrice by 0.70 for the row with a ProductID of 80 (this reduces the UnitPrice of that row by 30 percent):

 UPDATE Products SET UnitPrice = UnitPrice * 0.70 WHERE ProductID = 80

The row added to the ProductAudit table as a result of performing this UPDATE statement is shown in Table 4.9. This row is added by UpdateUnitPriceProductTrigger.

Table 4.9: ROW ADDED TO THE ProductAudit TABLE BY UpdateUnitPriceProductTrigger
ID	ACTION	PERFORMEDBY	TOOKPLACE
2	UnitPrice of ProductID #80 was reduced by 30%	dbo	2002-07-18 17:26:37.590

Creating and Testing DeleteProductTrigger

The DeleteProductTrigger trigger fires after a DELETE statement is performed on the Products table. This trigger adds a row to the ProductAudit table to audit the change. Listing 4.9 shows the DeleteProductTrigger.sql script.

Listing 4.9: DELETEPRODUCTTRIGGER.SQL

 /*   DeleteProductTrigger.sql creates a trigger that fires   after a DELETE statement is performed on the   Products table */ CREATE TRIGGER DeleteProductTrigger ON Products AFTER DELETE AS   -- don't return the number of rows affected   SET NOCOUNT ON   -- declare an int variable to store the   -- ProductID   DECLARE @NewProductID int   -- get the ProductID of the row that   -- was removed from the Products table   SELECT @NewProductID = ProductID   FROM deleted   -- add a row to the ProductAudit table   INSERT INTO ProductAudit (     Action   ) VALUES (     'Product #' +       CONVERT(nvarchar, @NewProductID) +       ' was removed'   )

To test DeleteProductTrigger, all you have to do is to remove a row from the Products table using a DELETE statement. For example, the following DELETE statement removes the row with the ProductID of 80:

 DELETE FROM Products WHERE ProductID = 80

The row added to the ProductAudit table as a result of performing this DELETE statement is shown in Table 4.10. This row is added by DeleteProductTrigger.

Table 4.10: ROW ADDED TO THE ProductAudit TABLE BY DeleteProductTrigger
ID	ACTION	PERFORMEDBY	TOOKPLACE
3	Product #80 was removed	dbo	2002-07-18 17:35:53.510