Introducing Triggers


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:

  • Data Manipulation Language (DML) triggers. These triggers fire when the INSERT, UPDATE, and DELETE operations are executed against the data table.

  • Data Definition Language (DDL) triggers, which are new for SQL Server 2005. These triggers fire when the data table structure changesnot when the data changes. For example, when the CREATE, ALTER, or DROP statements are executed, corresponding DDL triggers can be created to fire.

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 Trigger

Suppose 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 Trigger

Data 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.





Hitchhiker's Guide to Visual Studio and SQL Server(c) Best Practice Architectures and Examples
Hitchhikers Guide to Visual Studio and SQL Server: Best Practice Architectures and Examples, 7th Edition (Microsoft Windows Server System Series)
ISBN: 0321243625
EAN: 2147483647
Year: 2006
Pages: 227

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net