et’s talk about when and why we use triggers and the technical details of how to write a trigger.

To recap, a trigger is a stored piece of code that the database executes in response to the modification of data. People who program front-ends are familiar with the concept of “event driven” programming. A trigger is the database version of it. Three examples of when a trigger would be useful include: to implement “cascading updates,” create audit trails, or maintain summary tables.

Triggers can also be used to enforce rules that other referential integrity constructs cannot implement. A check constraint can only refer to columns of the same row. However, it cannot check values of other rows, tables, or even other databases. So, for example, one cannot in a check constraint ensure that the salary field of the employee table is less than the value of the maxsalary field in the department table. However, one can easily code this in a trigger. Similarly, in a trigger one can even obtain the max salary value from another database or, for example, compute it by obtaining the average salary for the department and adding 15% (this is an example of a rule based on other rows of the same table).

A trigger can also enforce a rule based on the previous value of the field. A rule such as “the new salary can be at most 15% above the old salary” cannot be implemented with any constraint types. As we shall see when we demonstrate how to code triggers, a trigger allows you to view both the old and new values of the data being modified.

Although a trigger can enforce business rules, one should realize that not everyone agrees about where to place business rules. In a three-tier approach (presentation, business rules, data), the business rules would typically not be implemented at the database level but in the middle tier. In addition, in many instances the examples given will be coded using a stored procedure. Instead of coding a trigger that creates an audit trail, the procedure that modifies the data will contain the code to maintain the audit trail.

The benefit of a trigger is most relevant in situations in a less controlled environment where the data is being accessed through many methods and front-ends. Code in a trigger will always be executed by the database when the data is modified. In a less than perfect world, a stored procedure might be avoided by the user coding the modification directly instead of executing the stored procedure designed for the task. (How many of you have “power users” who insist on full access so they can do their “own thing” in Access?)

Let’s now move on to coding a trigger.

Remember that when a trigger is executed, it is after the modification has taken place and referential integrity (primary key, foreign key, etc) has been checked, but before the transaction is committed.

While the trigger is executing, SQL Server makes two tables available. The structure of both tables (for example, the columns and data types) are exactly the same as the table being modified. The inserted table contains the values of the data being modified after the modification. The deleted table contains the values of the data before the modification. Following is a table that illustrates the role each table plays according to the event being handled.

To gain a better understanding of how the tables are used, run the following little script from isql.

create table test_trigger(	empname char(30),	salary int)gocreate trigger tr_test_trigger  on test_trigger for insert,update,delete as	select * from inserted	select * from deletedgo--then insert some rows ..insert into test_trigger(empname,salary) values ("John Doe", 25000)insert into test_trigger(empname,salary) values ("Fred Flinstone", 30000)-- then update some rowsupdate test_triggerset salary = salary + 5000-- then delete some rowsdelete from test_trigger

After each modification, two result sets will be returned. The first will be the contents of the inserted table, and the second will be the contents of the deleted table.

In the next segment, I will show you how to write some of the examples we’ve talked about and will demonstrate an easy method of writing and debugging them. Lastly, we’ll discuss the enhancements in SQL Server version 7.