SQL Server 2000 Triggers
9. Triggers
Triggers allow for more sophisticated validation to be defined against a table than can be provided through the field level Rules. They may also be used to ensure referential integrity, to cascade changes throughout related records, and to determine the changes made to a field and perform appropriate action.
Three triggers may be defined against each table which operate when a new record is inserted, modified, or deleted respectively. Triggers are programmed to prevent insert, update, and delete anomalies that may occur when updating the database.
SQL Server also allows the definition of Primary and Foreign Keys to enforce referential integrity at the database level.
An example of an insert anomaly might be the creation of an Invoice record which does not have a valid foreign key relationship with the Customer table. If this were to occur there would be no means of determining the address for the Invoice.
Update anomalies, which should be checked when inserting a record, often enforce business rules as well as referential integrity issues. An Invoice date cannot be changed after it has been issued, or the total outstanding amount cannot exceed the credit limit for the customer.
Delete anomalies often involve referential integrity issues where a record may not be deleted if there are related records in another table. This is usually performed by setting up referential integrity with primary and foreign keys.
Triggers are created with the MANAGE-TRIGGERS window for the appropriate database using a programmable form of SQL known as Transact SQL. Transact SQL is an extension of SQL that allows program flow and the use of variables to determine the actions performed for a trigger.
A typical trigger will perform some validation and rollback the transaction with appropriate error messages of there is a violation of the database validation rules. More sophisticated triggers may update values or delete records in other database tables. Typical examples are given below and further information on the available syntax for Transact SQL can be obtained from the reference manual.
Multiple triggers can be defined on a table and the sequence of execution controlled with the sp_settriggerorder system stored procedure.
Trigger Program Structure
Triggers are created with the MANAGE-TRIGGER menu which brings up the trigger window.
CREATE TRIGGER [owner.]trigger_name
ON [owner.]table_name
FOR {INSERT, UPDATE, DELETE}
[WITH ENCRYPTION]
AS sql_statements
Creating a Trigger with the SQL Executive
Triggers employ two conceptual tables, 'deleted' and 'inserted' to allow for Transact SQL to determine the values in a record before and after a transaction. An Insert transaction will create a record in the 'inserted' table before completing a transaction, a delete transaction will create a record in the 'deleted' table, and an Update will place the old values for the record in the 'deleted' table and the new values in the 'inserted' table.
These virtual tables may, in some circumstances, contain more than one record. It is therefore necessary to structure any processing to process all the potential records The system variable @@rowcount indicates the number of records to process.
The examples use set based processing to cater for several records. Some processing may require the use of Cursors as described in the Stored Procedures section..
Program flow can be controlled with an IF statement normally used in conjunction with a select statement that returns a single value. The next Transact SQL statement is executed if the condition is true. If more than one line of Transact SQL is required for a particular condition they must be enclosed in a BEGIN...END construct.
The UPDATE() function is often used to indicate if a field has been changed by the application. If the field has not been changed the function returns a false value and the program flow will skip the next line or BEGIN..END section.
A count of the records is usually made to test if any records have failed the required business rules.
The IF EXISTS (SELECT….) clause is more efficient than IF (SELECT COUNT(*) …) >0 because processing stops after the first record is found.
Error messages need to be returned to the application if appropriate. Transact SQL allows for a PRINT command to display error messages but the RAISERROR command is better for creating a smooth interface with the application. SQL Server error numbers above 50,000 are reserved for applications and the RAISERROR command can generate an error number in the calling application as well as supplying error text.
If a rule causes a transaction to fail it should be rolled back with the ROLLBACK TRANSACTION command. SQL Server will not update the database and the application will need to perform appropriate actions to recover from the error or attempt to resubmit the changes.
Triggers should be commented well and cross-referenced with the data dictionary documentation. Comments are enclosed with a forward slash and asterisk as shown in the example.
Trigger functionality often needs to be implemented both as INSERT and as UPDATE triggers to prevent incorrect data from being entered.
Typical syntax for a trigger is shown below.
CREATE TRIGGER truOrderUpdate ON tabOrders FOR UPDATE AS
IF UPDATE( fieldname )
BEGIN
/* This is a Comment */
IF EXISTS (SELECT * FROM inserted WHERE expression ) > 0
BEGIN
...
Transact SQL Statements...
...
RAISERROR 52001 'This is an error message!'
ROLLBACK TRANSACTION
END
END
Remember to place BEGIN and END statements after a program flow statement if more than one Transact SQL statement is required within the IF statement.
Field Level Validation
Some field validation can be performed using a Rule. The advantage of a rule is that it is defined independently of the field and may be applied consistently to several fields in the database.
Update triggers may be used to check field values in a similar manner to a rule. The form of the Transact SQL is as follows:
CREATE TRIGGER truorder ON dbo.taborder
FOR UPDATE
AS
BEGIN
IF EXISTS (SELECT * FROM inserted
WHERE freight > 50.0 )
BEGIN
ROLLBACK TRANSACTION
RAISERROR 52001 'Freight must be less than £ 50 !'
END
END
Notice that the Transact SQL is designed to work with more than one record in the inserted table.
Record Level Validation
The trigger is more flexible than a rule and can check other values in the record or in other tables as well as performing complete queries and complex sequences of operations.
The following example of an Update Trigger prevents the Freight field from being greater than the Limit field in the same record.
IF UPDATE(Freight) OR UPDATE(Limit)
BEGIN
IF EXISTS (SELECT * FROM inserted WHERE Freight>Limit)
BEGIN
ROLLBACK TRANSACTION
RAISERROR 52002 'Freight must be less than Limit!'
END
END
Avoid Null values in numeric fields as they will not default to zero for validation purposes. Define a zero default for the field.
Checking Values against another Table
Triggers may also be used to check values against another table.
Inserting a new Invoice Item record may require a stock check against the relevant product record. Changes to the line item are prevented in the Update trigger so this check is only required on Insert.
Preventing Changes to a Field
The UPDATE() function will indicate if a particular field has been updated during a transaction. A typical trigger would check if a field has been updated and rollback the transaction after error handling.
This is particularly useful for primary and foreign key values which should not be changed after inserting the record as shown in the following trigger code:
IF UPDATE(Order_ID)
BEGIN
ROLLBACK TRANSACTION
RAISERROR 52003 'No update allowed on Order Identifier!'
END
Security may be defined at the field level to prevent certain users from updating certain fields.
Referential Integrity Checks
SQL Server 6.0 implements referential integrity checks automatically when Foreign Keys are defined against the corresponding Primary Key. The integrity check is mandatory and it is not possible to enter empty values against a foreign key field if the relationship is optional.
Implementation of referential integrity in database triggers is also possible and its the method employed before SQL Sever 6.0. Greater control is possible over error messages and optional relationships or cascading deletes.
Referential Integrity checks are easy to implement with Foreign Keys and this should be used wherever possible. The referential integrity is checked by the server before the triggers are fired and the programmer may prefer that all errors are processed together. In this case referential integrity must be implemented as triggers.
Referential integrity often involves the setting up of several triggers. A typical scenario for a mandatory relationship between parent and child tables would involve an insert trigger on the child table to check the foreign key, an insert trigger on the parent table to ensure unique primary keys, a delete trigger on the parent table to avoid orphaned child records, and update triggers on both the parent and child tables to ensure that the primary and foreign key values are not updatable.
Checking a Foreign Key
Triggers may be used to check referential integrity when entering a new record. This usually involves ensuring that the new record has a corresponding occurrence in another table.
Foreign Keys may be implemented with a trigger that allows empty values or checks the referential integrity of the entered value. The trigger needs also to be defined on Update or changes to the field prevented.
CREATE TRIGGER triOrders ON tabOrders FOR INSERT AS
BEGIN
/* Check Customer-Orders Integrity */
IF (SELECT COUNT(*) FROM inserted, tabCustomers
WHERE (inserted.Customer_ID = tabCustomers.Customer_ID)
<> (SELECT COUNT(*) FROM inserted))
BEGIN
RAISERROR 52005 'Orders must have a valid or blank Customer!'
ROLLBACK TRANSACTION
END
END
It is easier to define Foreign keys with the Foreign Key feature of the Manage Tables window.
Ensuring Unique Candidate Keys
Primary keys are usually enforced by defining an index that is unique however this functionality may also be performed with a trigger for candidate keys where values may also be left blank.
/* Check that Primary Key is Unique */
IF EXISTS (SELECT id FROM inserted, taborder
WHERE inserted.id = tabOrder.id )
BEGIN
RAISERROR 51004 'Order Identifier is not Unique!'
ROLLBACK TRANSACTION
END
SQL Server will check the unique index before checking the insert trigger and the trigger validation error will not occur. Unique primary key indexes are the recommended approach for ensuring the uniqueness of a key value.
Checking Referential Integrity on Delete
Referential integrity checks on deletion prevent records from being deleted if there are dependent records in related tables. An example is shown below where the Order table has many Order_Details records linked on the common Order_ID field.
CREATE TRIGGER trdorder ON taborder FOR DELETE AS
BEGIN
/* Check Order Item Integrity */
IF EXISTS (SELECT id FROM deleted, taborderitem
WHERE deleted.id = taborderitem.order )
BEGIN
ROLLBACK TRANSACTION
RAISERROR 52004 'Order Items Exist!'
END
END
Cascading Delete
Delete validation usually prevents deletion of a parent record if any child records exist in a related table. It is possible for the deletion trigger to automatically delete the child records when the parent record is deleted.
This Cascading Delete prevents orphaned child records existing in the database and removes the need for the application to delete the child records before attempting to delete the parent record.
CREATE TRIGGER trdorder ON taborder FOR DELETE AS
BEGIN
/* Cascading Delete to ensure Order_Item Integrity */
DELETE taborderitem FROM deleted, taborderitem
WHERE taborderitem.order = deleted.id
END
Further validation is often required. The select statement might only delete order detail records if there were no outstanding shipments to be performed and rollback the transaction if this were not possible. In general, application level deletion of the child records before deletion of the parent record allows greater control and these deletions could be placed into a single transaction with a Rollback to prevent the possibility of data anomalies.
A similar approach can be used to allow for changes to a primary key to cascade down and update foreign key values in child tables, this is called cascade update. This approach is not recommended and can be circumvented by using a second candidate primary key in the parent table for the application to change and providing automatic primary keys that are used by the application.
Updating another Table
Triggers may be used to change values in related tables. Our example will automatically update the YTD_SALES field in the Titles table which indicates the number of titles sold so far this year.
Skilful use of triggers allow for calculated fields to be stored in the database and updated outside of application control. Remember these fields should not be updatable by the application. Insert and update triggers may be used to check values against another table as well as performing foreign key validation.
Calculated field may sometimes be implemented as Views but there are some restrictions on SQL Server views.
The Insert Trigger on the Sales table will automatically add the sales quantity onto the YTD_SALES fields for the appropriate titles. Notice that the trigger logic can process several inserted records at one time.
CREATE TRIGGER trisales ON dbo.sales
FOR INSERT
AS
BEGIN
UPDATE titles
SET titles.ytd_sales = titles.ytd_sales + inserted.qty
FROM titles, inserted
WHERE titles.title_id = inserted.title_id
END
In this instance referential integrity is handled with a foreign key constraint in the database and does not need to be implemented at the trigger level.
The Delete Trigger must subtract the value of the deleted Sales from the Titles records:
CREATE TRIGGER trdSales ON dbo.sales
FOR DELETE
AS
BEGIN
UPDATE titles
SET titles.ytd_sales = titles.ytd_sales - deleted.qty
FROM titles, deleted
WHERE titles.title_id = deleted.title_id
END
The Update Trigger needs to handle the difference between the original and the new sales record:
CREATE TRIGGER truSales ON dbo.sales
FOR UPDATE
AS
BEGIN
UPDATE titles
SET titles.ytd_sales = titles.ytd_sales - deleted.qty + inserted.qty
FROM titles, deleted, inserted
WHERE titles.title_id = deleted.title_id AND titles.title_id = inserted.title_id
END
Finally, updating the YTD_SALES can be prevented in a trigger in the Update trigger of the TITLES table:
CREATE TRIGGER truTitles ON dbo.titles
FOR UPDATE
AS
BEGIN
IF UPDATE( ytd_sales )
BEGIN
RAISERROR 52010 'YTD_SALES may not be updated!'
ROLLBACK TRANSACTION
END
END
Be careful with more complex validations in conjunction with altering table values. An UPDATE command issued after a ROLLBACK TRANSACTION will still update the database. The GOTO command may be useful here in sending trigger processing to the end of a trigger.