A primary key is the unique identifier for its row and for foreign key rows in other tables. Generally, you should not allow updates to primary keys. An attempt to update a primary key should be taken very seriously. In this case, protect referential integrity by rolling back the update unless specified conditions are met.
Sybase suggests that you prohibit any editing changes to a primary key, for example, by revoking all permissions on that column. However, to prohibit updates only under certain circumstances, use a trigger.
The 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 stopupdatetrig
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:
The user tries to change a value in the primary key title_id in titles
The dependent key pub_id is not found in publishers
The target column does not exist or is null
Before you run this example, make sure the following error messages exist in 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, enter:
update titles set title_id = "BU7777" where title_id = "BU2075"
To test for the second error message, 35005:
update titles set pub_id = "7777" where pub_id = "0877"
To test for the third error, which generates message 24004:
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.
| Copyright © 2005. Sybase Inc. All rights reserved. |
|
|
