|
|
There are times when, for data integrity purposes, you need to maintain certain rules. These could be business rules, such as conditions that need to be met before allowing updates of certain fields. This is when triggers come in handy.
A trigger is a special kind of stored procedure that executes automatically when a change occurs in a database record. There are three specific types of events that triggers are mainly concerned with: INSERT, UPDATE, and DELETE. Triggers allow you to run code automatically that can perform any required check, change other required data, or even reject the change if certain conditions are not met.
Being specific to the type of operation being performed on a record, UPDATE, INSERT, and DELETE are powerful tools when it comes to the maintenance of referential integrity and enforcement of business rules. Triggers can be defined on tables or views. In SQL Server 2000, there is also a new kind of trigger called INSTEAD OF. These triggers are executed instead of the triggering action. For example, instead of doing an INSERT, UPDATE, or DELETE operation on the table, the control is passed to the INSTEAD OF trigger, which is then responsible for performing the appropriate action. INSTEAD OF triggers can be defined on views referencing multiple tables (these views are normally not updateable), so each of the referenced tables can be updated appropriately.
A trigger is essentially a stored procedure, so it can contain all the logic any stored procedure can handle. It is a good programming practice to code logic inside stored procedures. Then, inside the triggers, you use the stored procedure as needed. Since triggers are a special kind of stored procedure, they have the same advantages in terms of performance and speed as stored procedures. They also have some additional benefits, including enforcing business rules and automatically running routines.
Triggers are used to enforce business rules. The rules are enforced on the database and independent of client applications. This means that the rules are always enforced, no matter what the client application does. You can even have different client applications, such as a web application client and a Windows Form application client. If the rules change in the future, they can be modified without having to change the client application.
Triggers can also be used to automatically run certain routines when there is a change to a table. These are usually for auditing purposes, and the routine stores any important changes in an audit trail table. For example, it might be required that an audit trail is kept whenever an employee salary is changed. Triggers can also be used to automatically send e-mails to users whenever certain changes occur.
As with stored procedures, the main tool for creating triggers is the SQL Analyzer. However, you can also view existing triggers from the Enterprise Manager. To do this, simply right-click on a table and select All Tasks, Manage Trigger from the pop-up menu. This will bring up the Trigger Properties window.
Just like stored procedures, you also need to know T-SQL before you can create a trigger. There are some T-SQL statements that are only applicable to triggers. Let’s have a look at the T-SQL syntax for managing triggers.
The full SQL syntax for creating, changing, and deleting triggers is shown below.
Create a trigger:
CREATE TRIGGER < trigger_name >
ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [DELETE] [,] [INSERT]
[,] [UPDATE] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
< trigger_logic > [ ...n ]
Modify a trigger:
ALTER TRIGGER < trigger_name >
ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [DELETE] [,] [INSERT]
[,] [UPDATE] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
< trigger_logic > [ ...n ]
Delete a trigger:
DROP TRIGGER < trigger_name >
< trigger_name >: The name of the trigger. The name can also include spaces, but if so, it must be enclosed in [ ]. Trigger names must follow the same conventions as identifiers.
WITH ENCRYPTION: SQL Server stores the text for creating the trigger in system tables. This attribute option instructs the SQL Server to encrypt the system table columns containing the text for the trigger. This prevents the trigger from being published as part of the SQL Server replication. It also prevents the database user from being able to see the code for the trigger.
AFTER: This is new in SQL Server 2000. It is used to specify that the trigger is fired only when all operations specified in the triggering SQL statement have executed successfully. This trigger will only execute after all referential cascade actions and constraint checks succeed.
| Note |
Using the FOR keyword has the same effect as using the AFTER keyword. AFTER triggers cannot be defined on views. |
INSTEAD OF: This specifies that the trigger is executed instead of the triggering SQL statement and, therefore, overrides the actions of the triggering statements. At most, there can only be one INSTEAD OF trigger per INSERT, UPDATE, or DELETE statement defined on a table or view. However, it is possible to define views on views where each view has its own INSTEAD OF trigger.
| Warning |
INSTEAD OF triggers are not allowed on updateable views with the check option set. |
{ [DELETE] [,] [INSERT] [,] [UPDATE] }: These are the keywords that specify which data modification statements, when attempted against this table or view, will activate the trigger. At least one option must be specified. You can have any combination of these in the trigger definition.
| Note |
For INSTEAD OF triggers, the DELETE option is not allowed on tables that have a referential relationship specifying a cascade action ON DELETE. Also, the UPDATE option is not allowed on tables that have a referential relationship specifying a cascade action ON UPDATE. |
WITH APPEND: This is used to add additional triggers of an existing type. WITH APPEND is used for older versions of SQL Server and the clause is not needed to add an additional trigger in SQL 2000. In future versions it will not be supported; thus, you should not use it.
NOT FOR REPLICATION: Prevents the trigger from being executed when a replication process modifies the table
< trigger_logic >: This includes the SQL statements and logic for the trigger. There are logical tables and special logical statements that you can use in triggers. These are deleted and inserted logical tables, and the logical clause IF UPDATE (column).
deleted and inserted: These are logical (conceptual) tables. They are structurally similar to the table on which the trigger is defined and hold the old values or new values of the rows that may be changed by the action. For example, to retrieve all values in the deleted table, use SELECT * FROM deleted.
IF UPDATE (column): Tests for an INSERT or UPDATE action to a specified column and is not used with DELETE operations. More than one column can be specified. Because the table name is specified in the ON clause, there is no need to include the table name before the column name in an IF UPDATE clause. IF UPDATE will return the TRUE value in INSERT actions because the columns have either explicit values or implicit (null or default) values inserted.
SQL Server also allows recursive invocation of triggers when the recursive triggers setting is enabled in the database.
Recursive triggers allow two types of recursion to occur:
Indirect recursion
Direct recursion
With indirect recursion, an application updates table one, which fires trigger one, updating table two. Trigger two then fires and updates table one, and so on.
With direct recursion, the application updates a table, which fires a trigger, updating the table itself. Because the table was updated again, the trigger fires again, and so on.
| Note |
The above behavior occurs only if the recursive triggers setting of sp_dboption is enabled. There is no defined order in which multiple triggers specified for a given event are executed. Each trigger should be self-contained. |
Disabling the recursive triggers setting only prevents direct recursions. To disable indirect recursion as well, set the nested triggers server option to 0 using sp_configure.
If any of the triggers do a ROLLBACK TRANSACTION, regardless of the nesting level, no further triggers are executed.
Triggers can be nested to a maximum of 32 levels. If a trigger changes a table on which there is another trigger, the second trigger is activated and can then call a third trigger, and so on.
| Warning |
If any trigger in the chain sets off an infinite loop, exceeding the nesting level, the trigger is canceled. To disable nested triggers, set the nested triggers option of sp_configure to 0 (off). The default configuration allows nested triggers. If nested triggers is off, recursive triggers is also disabled, regardless of the recursive triggers setting of sp_dboption. |
Now that you know how to create triggers, let’s see an example.
When ordering new products, you want to make sure that no orders are placed for discontinued products. To do this, you must create an UPDATE/INSERT trigger on the Order Details table.
 |
-- =============================================
-- Drop trigger if it already exists
-- =============================================
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'Check Product Order Details'
AND type = 'TR')
DROP TRIGGER [Check Product Order Details]
GO
-- =============================================
-- Create trigger [Check Product Order Details]
-- =============================================
CREATE TRIGGER [Check Product Order Details]
ON [Order Details]
FOR INSERT, UPDATE
AS
BEGIN
-- =============================================
-- Do the Discontinued check only if ProductID
-- has been modified
-- =============================================
IF UPDATE(PRODUCTID)
BEGIN
DECLARE @DISCOUNTINUED BIT
DECLARE @PRODUCTID integer
-- =============================================
-- Set discontinued on as default, so that if it
-- does not exist, there will also be an error.
-- This is for illustration only. This test is
-- already done in the foreign key constraint
-- =============================================
SET @DISCOUNTINUED = 1
-- =============================================
-- Get the Discontinued and ProductID
-- Note the use of logical table "inserted" to
-- work out the ProductID
-- =============================================
SELECT @DISCOUNTINUED = Discontinued,
@PRODUCTID = P.ProductID
FROM Products P INNER JOIN Inserted I
ON P.ProductID = I.ProductID
-- =============================================
-- IF product is discontinued, then RAISE an
-- error and rollback transaction to prevent saving
-- =============================================
IF @DISCOUNTINUED = 1
BEGIN
RAISERROR
(' The Product(ID: %d) is discontinued or does not
exist.', 16, 1, @PRODUCTID)
ROLLBACK TRANSACTION
END
END
END
GO
|
The above script example demonstrates the use of a simple trigger. You can test the trigger by changing the value of the ProductID in an existing row in the Order Details table to that of a discontinued product. Figure C-4 shows the resulting error message that is generated if the product is discontinued.
|
|