Today's question comes from Fedirico:
I am quite new in SQL, and I wanted to know how to perform a cascading delete. I know that I have to use triggers, but I donīt have any idea of how to write them. Also, any book recommendations?
Hi Fedirico,
As you say, triggers are the best way to go to perform cascading deletes. Due to their proximity to the actual data, they are extremely efficient but also have a number of other advantages, namely:
- A user cannot update your tables without causing the trigger to fire
- In the event that an error occurs during trigger execution, the original transaction is rolled back, leaving your data in a state that is known to be correct.
So let's see how to perform this cascading delete. Let us assume that you have a stock table which contains a list of items a shop stocks and sells, as well as a stock transaction table which contains a list of purchases and sales for each stock item. We wish to implement a routine which ensures that whenever a stock item is deleted, all the related transaction items are deleted also.
The code for the trigger would look something like this:
|
Keep in mind that we refer to the deleted row(s) using the Deleted
table.
Now, I wish to point out one area which can be a source of potential bugs unless you keep this fact in mind. A trigger only fires ONCE per statement.
What do I mean by this? The trigger above will work fine as long as only one
row is deleted from the Stock
table. It will figure out which
Stock_Code
it
must clear and delete all related records from the Stock_Transaction
table.
However, what happens if the user executes a DELETE
statement which deletes
more than one record? The trigger will only remove related records for ONE
of the deleted records, leaving the other records there and wrecking the
integrity of your data.
With this in mind, let's rewrite the trigger
|
This will handle single record deletes as well as multiple record deletes.
So, a couple of points I would like to summarize:
-
1. Triggers are the best way to implement cascading deletes and updates
2. A trigger will roll back the change you made to the data if an error occurs within it
3. A trigger is executed ONCE per statement.
4. We refer to the deleted rows using the
deleted
table (and new rows with
the inserted
table)
Keeping these points in mind, writing a trigger is very similar to writing a stored procedure, except that your code will not be called explicitly, but execute whenever a change is made to a table in the database.
With respect to you question about books, the best source of information are the Books Online (BOL) which ship with SQL. If you find them a bit dry, you might want to have a look at my booklist at http://www.only-network.com/books. I'm sure you'll find something you'll find interesting. Happy Programming!
Owen
Read Other SQL Guru Questions |