When implementing a SQL Server database, developers have yet another tool (beyond constraints) to handle a litany of issues having to do with referential and data integrity management, business rules, and many other demands placed on their database systemsTriggers. Basically, a trigger is a stored procedure that's fired automatically. Triggers are associated with database tables and come in two flavors in SQL Server 2005:
Any trigger can execute a ROLLBACK command that stops the operation being requested. This means if the trigger logic discovers that there is something wrong, it can prevent the change from taking place. Let's step through a couple of simple examples. Creating a DML TriggerSuppose your company decides that the maximum discount you're going to offer this week is 65%. While some of your applications might offer more, you want to make sure that no new customers are added with a higher discount rate. To accomplish this, you can add a trigger to the Customers table that fires on UPDATE or INSERT operations and that looks like Figure 2.87. Figure 2.87. DML Trigger to manage maximum discount permitted.Notice that the trigger fires on either INSERT or UPDATE operations. Sure, you can create three different triggersone for each action. If the trigger logic determines that the UPDATE or INSERT should not be allowed, it executes a ROLLBACK TRANSACTION to undo the change. A unique feature of the DML trigger is the ability to access the row(s) being changed via the INSERTED and DELETED pseudo tables. These can be essential when your trigger logic needs to compare two or more columns or generate a new value to place in a columnoverriding the value that would be written to the database. The DELETED pseudo table can be inspected as well. It contains the row(s) that are about to be dropped from the database. As in the movie Robocop, you could roll back a DELETE if the customer being rubbed out is in the list of company executives. Creating a DDL TriggerData Definition Language (DDL) triggers have a much broader scope. Once created, they monitor all indicated changes on the database level or on the entire SQL Server instance. No, this means you can't create DDL triggers for a specific table. In the example shown in Figure 2.88, I create a DDL trigger that prevents any table from being dropped if any customers still owe us money. A bit contrived, but still.... Figure 2.88. DDL trigger to prevent anyone from dropping tables. |