|
|
Transact-SQL User's Guide |
Triggers are used to maintain referential integrity, which assures that vital data in your database¾such as the unique identifier for a given piece of data¾remains accurate and can be used as the database changes. Referential integrity is coordinated through the use of primary and foreign keys.
The primary key is a column or combination of columns whose values uniquely identify a row. The value cannot be NULL and must have a unique index. A table with a primary key is eligible for joins with foreign keys in other tables. Think of the primary key table as the master table in a master-detail relationship. There can be many such master-detail groups in a database.
You can use the sp_primarykey procedure to mark a primary key. This marks the key for use with sp_helpjoins and adds it to the syskeys table.
For example, the title_id column is the primary key of titles. It uniquely identifies the books in titles and joins with title_id in titleauthor, salesdetail, and roysched. The titles table is the master table in relation to titleauthor, salesdetail, and roysched.
The "Diagram of the pubs2 Database" shows how the pubs2 tables are related. The "Diagram of the pubs3 Database" provides the same information for the pubs3 database.
The foreign key is a column or combination of columns whose values match the primary key. A foreign key does not have to be unique. It is often in a many-to-one relationship to a primary key. Foreign key values should be copies of the primary key values. That means no value in the foreign key should exist unless the same value exists in the primary key. A foreign key may be null; if any part of a composite foreign key is null, the entire foreign key must be null. Tables with foreign keys are often called detail tables or dependent tables to the master table.
You can use the sp_foreignkey procedure to mark foreign keys in your database. This flags them for use with sp_helpjoins and other procedures that reference the syskeys table.
The title_id columns in titleauthor, salesdetail, and roysched are foreign keys; the tables are detail tables.
In most cases, you can enforce referential integrity between tables using the referential constraints described under "Specifying Referential Integrity Constraints", because the maximum number of references allowed for a single table is 200. If a table exceeds that limit, or has special referential integrity needs, use referential integrity triggers.
Referential integrity triggers keep the values of foreign keys in line with those in primary keys. When a data modification affects a key column, triggers compare the new column values to related keys by using temporary work tables called trigger test tables. When you write your triggers, you base your comparisons on the data that is temporarily stored in the trigger test tables.
Adaptive Server uses two special tables in trigger statements: the deleted table and the inserted table. These are temporary tables used in trigger tests. When you write triggers, you can use these tables to test the effects of a data modification and to set conditions for trigger actions. You cannot directly alter the data in the trigger test tables, but you can use the tables in select statements to detect the effects of an insert, update, or delete.
The following trigger fragment uses the inserted table to test for changes to the titles table title_id column:
if (select count(*)
from titles, inserted
where titles.title_id = inserted.title_id) !=
@@rowcount
An update is, effectively, a delete followed by an insert; the old rows are copied to the deleted table first; then the new rows are copied to the trigger table and to the inserted table. The following illustration shows the condition of the trigger test tables during an insert, a delete, and an update:
Figure 16-1: Trigger test tables during
insert, delete, and update operations
When setting trigger conditions, use the trigger test tables that are appropriate for the data modification. It is not an error to reference deleted while testing an insert or inserted while testing a delete; however, those trigger test tables will not contain any rows.
Note: A given trigger fires only once per query. If trigger actions depend on the number of rows affected by a data modification, use tests, such as an examination of @@rowcount for multirow data modifications, and take appropriate actions.
The following trigger examples will accommodate multirow data modifications where necessary. The @@rowcount variable, which stores the "number of rows affected" by the most recent data modification operation, tests for a multirow insert, delete, or update. If any other select statement precedes the test on @@rowcount within the trigger, use local variables to store the value for later examination. All Transact-SQL statements that do not return values reset @@rowcount to 0.
When you insert a new foreign key row, make sure the foreign key matches a primary key. The trigger should check for joins between the inserted rows (using the inserted table) and the rows in the primary key table, and then roll back any inserts of foreign keys that do not match a key in the primary key table.
The following trigger compares the title_id values from the inserted table with those from the titles table. It assumes that you are making an entry for the foreign key and that you are not inserting a null value. If the join fails, the transaction is rolled back.
create trigger forinsertrig1
on salesdetail
for insert
as
if (select count(*)
from titles, inserted
where titles.title_id = inserted.title_id) !=
@@rowcount
/* Cancel the insert and print a message.*/
begin
rollback transaction
print "No, the title_id does not exist in
titles."
end
/* Otherwise, allow it. */
else
print "Added! All title_id's exist in titles."
In the above example, @@rowcount refers to the number of rows added to the salesdetail table. This is also the number of rows added to the inserted table. The trigger joins titles and inserted to determine whether all the title_id's added to salesdetail exist in the titles table. If the number of joined rows, which is determined by the select count(*) query, differs from @@rowcount, then one or more of the inserts is incorrect, and the transaction is canceled.
This trigger prints one message if the insert is rolled back and another if it is accepted. To test for the first condition, try the following insert statement:
insert salesdetail
values ("7066", "234517", "TC9999", 70, 45)
To test for the second condition, enter this statement:
insert salesdetail
values ("7896", "234518", "TC3218", 75, 80)
When you delete a primary key row, you should delete corresponding foreign key rows in dependent tables. This preserves referential integrity by ensuring that detail rows are removed when their master row is deleted. If you do not delete the corresponding rows in the dependent tables, you could end up with a database that had detail rows that could not be retrieved or identified. To properly delete the dependent foreign key rows, use a trigger that performs a cascading delete.
When a delete statement on titles is executed, one or more rows leave the titles table and are added to deleted. A trigger can check the dependent tables¾titleauthor, salesdetail, and roysched¾to see if they have any rows with a title_id that matches the title_ids removed from titles and is now stored in the deleted table. If the trigger finds any such rows, it removes them.
create trigger delcascadetrig
on titles
for delete
as
delete titleauthor
from titleauthor, deleted
where titleauthor.title_id = deleted.title_id
/* Remove titleauthor rows that match deleted
** (titles) rows.*/
delete salesdetail
from salesdetail, deleted
where salesdetail.title_id = deleted.title_id
/* Remove salesdetail rows that match deleted
** (titles) rows.*/
delete roysched
from roysched, deleted
where roysched.title_id = deleted.title_id
/* Remove roysched rows that match deleted
** (titles) rows.*/
In practice, you may want to keep some of the detail rows, either for historical purposes (to check how many sales were made on discontinued titles while they were active) or because transactions on the detail rows are not yet complete. A well-written trigger should take these factors into consideration.
Preventing Primary Key DeletionsThe deltitle trigger supplied with the pubs2 database prevents the deletion of a primary key if there are any detail rows for that key in the salesdetail table. This trigger preserves the ability to retrieve rows from salesdetail:
create trigger deltitle
on titles
for delete
as
if (select count(*)
from deleted, salesdetail
where salesdetail.title_id =
deleted.title_id) > 0
begin
rollback transaction
print "You cannot delete a title with sales."
end
In this trigger, the row or rows deleted from titles are tested by being joined with the salesdetail table. If a join is found, the transaction is canceled.
Similarly, the following restricted delete prevents deletes if the primary table, titles, has dependent children in titleauthor. Instead of counting the rows from deleted and titleauthor, it checks to see if title_id was deleted. This method is more efficient for performance reasons because it checks for the existence of a particular row rather than going through the entire table and counting all the rows.
Recording Errors That OccurThe following trigger uses the raiserror command for the error message 35003. raiserror sets a system flag to record that the error occurred. Before trying this example, add error message 35003 to the sysusermessages system table:
sp_addmessage 35003, "restrict_dtrig - delete failed: row exists in titleauthor for this title_id."
The trigger is as follows:
create trigger restrict_dtrig
on titles
for delete as
if exists (select * from titleauthor, deleted where
titleauthor.title_id = deleted.title_id)
begin
rollback transaction
raiserror 35003
return
end
To test this trigger, try the following delete statement:
delete titles
where title_id = "PS2091"
The following example cascades an update from the primary table titles to the dependent tables titleauthor and roysched.
create trigger cascade_utrig
on titles
for update as
if update(title_id)
begin
update titleauthor
set title_id = inserted.title_id
from titleauthor, deleted, inserted
where deleted.title_id = titleauthor.title_id
update roysched
set title_id = inserted.title_id
from roysched, deleted, inserted
where deleted.title_id = roysched.title_id
update salesdetail
set title_id = inserted.title_id
from salesdetail, deleted, inserted
where deleted.title_id = salesdetail.title_id
end
To test this trigger, suppose that the book Secrets of Silicon Valley was reclassified to a psychology book from popular_comp. The following query updates the title_id PC8888 to PS8888 in titleauthor, roysched, and titles.
update titles
set title_id = "PS8888"
where title_id = "PC8888"
Since a primary key is the unique identifier for its row and for foreign key rows in other tables, an attempt to update a primary key should be taken very seriously. In this case, you need to protect referential integrity by rolling back the update unless specified conditions are met.
It is best to prohibit any editing changes to a primary key, for example by revoking all permissions on that column. However, if you want to prohibit updates only under certain circumstances, use a trigger.
Restricted Update Trigger Using Date FunctionsThe following trigger prevents updates to titles.title_id on the weekend. The if update clause in stopupdatetrig allows you to focus on a particular column, titles.title_id. Modifications to the data in that column cause the trigger to go into action. Changes to the data in other columns do not. When this trigger detects an update that violates the trigger conditions, it cancels the update and prints a message. If you would like to test this one, substitute the current day of the week for "Saturday" or "Sunday".
create trigger stopupdatetrigRestricted Update Triggers with Multiple Actions
on titles
for update
as
/* If an attempt is made to change titles.title_id
** on Saturday or Sunday, cancel the update. */
if update (title_id)
and datename(dw, getdate())
in ("Saturday", "Sunday")
begin
rollback transaction
print "We do not allow changes to "
print "primary keys on the weekend."
end
You can specify multiple trigger actions on more than one column using if update. The following example modifies stopupdatetrig to include additional trigger actions for updates to titles.price or titles.advance. In addition to preventing updates to the primary key on weekends, it prevents updates to the price or advance of a title, unless the total revenue amount for that title surpasses its advance amount. You can use the same trigger name because the modified trigger replaces the old trigger when you create it again.
create trigger stopupdatetrig
on titles
for update
as
if update (title_id)
and datename(dw, getdate())
in ("Saturday", "Sunday")
begin
rollback transaction
print "We do not allow changes to"
print "primary keys on the weekend!"
end
if update (price) or update (advance)
if exists (select * from inserted
where (inserted.price * inserted.total_sales)
< inserted.advance)
begin
rollback transaction
print "We do not allow changes to price or"
print "advance for a title until its total"
print "revenue exceeds its latest advance."
end
The next example, created on titles, prevents update if any of the following conditions is true:
This example assumes that the following error messages have been added to sysusermessages:
sp_addmessage 35004, "titles_utrg - Update Failed: update of primary keys %1! is not allowed."
sp_addmessage 35005, "titles_utrg - Update Failed: %1! not found in authors."
The trigger is as follows:
create trigger title_utrg
on titles
for update as
begin
declare @num_updated int,
@col1_var varchar(20),
@col2_var varchar(20)
/* Determine how many rows were updated. */
select @num_updated = @@rowcount
if @num_updated = 0
return
/* Ensure that title_id in titles is not changed. */
if update(title_id)
begin
rollback transaction
select @col1_var = title_id from inserted
raiserror 35004 , @col1_var
return
end
/* Make sure dependencies to the publishers table are accounted for. */
if update(pub_id)
begin
if (select count(*) from inserted, publishers
where inserted.pub_id = publishers.pub_id
and inserted.pub_id is not null) != @num_updated
begin
rollback transaction
select @col1_var = pub_id from inserted
raiserror 35005, @col1_var
return
end
end
/* If the column is null, raise error 24004 and rollback the
** trigger. If the column is not null, update the roysched table
** restricting the update. */
if update(price)
begin
if exists (select count(*) from inserted
where price = null)
begin
rollback trigger with
raiserror 24004 "Update failed : Price cannot be null. "
end
else
begin
update roysched
set lorange = 0,
hirange = price * 1000
from inserted
where roysched.title_id = inserted.title_id
end
end
end
To test for the first error message, 35004, try the following query:
update titles
set title_id = "BU7777"
where title_id = "BU2075"
To test for the second error message, 35005, try this query:
update titles
set pub_id = "7777"
where pub_id = "0877"
To test for the third error, which generates message 24004, try this query:
update titles
set price = 10.00
where title_id = "PC8888"
This query fails because the price column in titles is null. If it were not null, it would have updated the price for title PC8888 and performed the necessary recalculations for the roysched table. Error 24004 is not in sysusermessages but it is valid in this case. It demonstrates the "rollback trigger with raiserror" section of the code.
A change or an update to a foreign key by itself is probably an error. A foreign key is just a copy of the primary key. Never design the two to be independent. If you want to allow updates of a foreign key, you should protect integrity by creating a trigger that checks updates against the master table and rolls them back if they do not match the primary key.
In the following example, the trigger tests for two possible sources of failure: either the title_id is not in the salesdetail table or it is not in the titles table.
This example uses nested if...else statements. The first if statement is true when the value in the where clause of the update statement does not match a value in salesdetail, that is, the inserted table will not contain any rows, and the select returns a null value. If this test is passed, the next if statement ascertains whether the new row or rows in the inserted table join with any title_id in the titles table. If any row does not join, the transaction is rolled back, and an error message is printed. If the join succeeds, a different message is printed.
create trigger forupdatetrig
on salesdetail
for update
as
declare @row int
/* Save value of rowcount. */
select @row = @@rowcount
if update (title_id)
begin
if (select distinct inserted.title_id
from inserted) is null
begin
rollback transaction
print "No, the old title_id must be in"
print "salesdetail."
end
else
if (select count(*)
from titles, inserted
where titles.title_id =
inserted.title_id) != @row
begin
rollback transaction
print "No, the new title_id is not in"
print "titles."
end
else
print "salesdetail table updated"
end
|
|