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 | 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 shows the script that creates the ProductAudit table. 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 .
