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:

CREATE TRIGGER stock_cascade_delete ON Stock FOR DELETE AS DECLARE @StockCodeToDelete int SELECT @StockCodeToDelete = Stock_Code FROM Deleted DELETE FROM Stock_Transactions WHERE Stock_Code = @StockCodeToDelete

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

CREATE TRIGGER stock_cascade_delete ON Stock FOR DELETE AS DELETE FROM Stock_Transactions WHERE Stock_Code IN ( SELECT Stock_Code FROM deleted )

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