Introducing Triggers

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
Nội dung trích xuất từ tài liệu:
Introducing TriggersIntroducing TriggersA database trigger is a special kind of stored procedure that is run automatically by thedatabase-or in trigger terms, fired-after a specified INSERT, UPDATE, or DELETEstatement is run against a specified database table. Triggers are very useful for doingthings 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 raisean 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. Inthis section, youll see an example of a trigger that will audit changes made to theProducts table.Also, when an UPDATE statement modifies the UnitPrice column of a row in theProducts table, a row will be added to the ProductAudit table. Finally, when a DELETEstatement removes a row from the Products table, a row will be added to theProductAudit table.Before you see the triggers, youll need to create the ProductAudit table. Listing 4.6shows 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 NorthwindCREATE 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 theProductAudit table. An identity automatically generates values for a column. The identityfor 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 theuser 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 isdefaulted using the GetDate() function, which returns the current date and time.In the following sections, youll 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, lets examine InsertProductTrigger.Creating InsertProductTriggerYou create a trigger using the CREATE TRIGGER statement. Listing 4.7 shows theInsertProductTrigger.sql script that creates the trigger InsertProductTrigger, which auditsthe 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 InsertProductTriggerON ProductsAFTER INSERTAS -- dont 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 byfirst clicking the Tables node in the Databases folder, then clicking the right mousebutton on the table you want to modify, and then selecting All Tasks ➣ ManageTriggers. Figure 4.8 shows InsertProductTrigger in Enterprise Manager. Youll notice Iveadded some comments to the start of the code that indicates what the trigger does.Figure 4.8: Using Enterprise Manager to view a triggerThe Object Browser of Query Analyzer also allows you to view, modify, and deletetriggers.Tip You can delete a trigger using the DROP TRIGGER statement, and you can modify a trigger using the ALTER TRIGGER statement.Testing InsertProductTriggerTo test InsertProductTrigger, all you have to do is to add a row to the Products tableusing 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 theProductAudit table using the following SELECT statement:SELECT *FROM ProductAuditThe row added to the ProductAudit table by InsertProductTrigger as a result ofperforming the previous INSERT statement is shown in Table 4.8.Table 4.8: ROW ADDED TO THE ProductAudit TABLE BY InsertProductTriggerID ACTION PERFORMEDBY ...