Scripting a Trigger for CASCADE DELETE Queries

In my database, I have a table called Jobs that contains general information about jobs. Each row in the Jobs table might have one or more related rows in a table called JobDates, which stores information about job activity for each date. The tables are related by a foreign key, which is defined on the jobid column in the JobDates table and references the jobid column in the Jobs table. The foreign key relationship specifies cascade deletes from the Jobs table to the JobDates table. Listing 1 creates the Jobs and JobDates tables and populates them with sample data based on today's (the current) date to illustrate the problem. For example, if today's date is February 12, 2002, I have to write a trigger that rejects any user's attempt to delete a job whose start date is less than 2 weeks from today's date. Figure 1, page 14, shows the JobDates table that Listing 1's code generates for this date. To easily fetch the start date for a job within the trigger, I wrote the user-defined function (UDF) that Listing 2, page 14, shows. Then I wrote the AFTER trigger on the Jobs table. Job-start dates can be in the future, and only deletions of jobs that are supposed to start more than 2 weeks from the current date are allowed. Listing 3, page 14, shows the code for the AFTER trigger.

Using the sample data I supplied, the trigger is supposed to allow deletion of only jobid 3 because that job starts 15 days from now. The trigger isn't supposed to allow the deletion of jobids 1 and 2 because they start today and in 5 days, respectively. However, the statement that Listing 4, page 15, shows deletes all three jobids, as its output in Figure 2, page 15, confirms. Why doesn't this trigger work?

A Catch-22 situation has developed in your scenario. Here's the chain of events that starts when you issue a delete against the Jobs table:

The trigger doesn't work because by the time it tries to check the start date of a job, all job dates have already been deleted by the cascading foreign key. Another problem to consider here is that the trigger can't handle multirow deletes appropriately. Consider the following assignment that Listing 3 uses to retrieve the jobid value before you pass it to the function:

SELECT @jobid = jobid FROM deleted

If you issue a multirow delete, the deleted table holds all the deleted rows, but the above assignment retrieves only one arbitrary jobid. Suppose you solve the problem of the already-deleted job dates. Your trigger still might not accomplish its task correctly. For example, suppose you issue a DELETE from the Jobs table. The SELECT statement might return jobid 3 arbitrarily, and the start date for jobid 3 doesn't conflict with your restriction. The trigger doesn't roll back the transaction, and all jobs in that transaction are deleted. So, you have to handle both problems.

One way to write a trigger that works is to create the trigger on the JobDates table, where the deleted job dates can be accessed through the deleted table, as Listing 5 shows. Note that you no longer need the fn_jobstartdate() function to fetch the start date. The trigger checks whether the minimum job date in the deleted table is earlier than 2 weeks from now. Obviously, the minimum job date in the deleted table is the start date of the earliest job that was deleted. If the answer is true, the trigger rolls back the transaction. For example, when you use the following statement to delete all jobs:

DELETE FROM Jobs

you receive the following error message:

Server: Msg 50000, Level 11, State 1, Procedure trg_JobDates_i_NoDel2wk, Line 8Cannot delete a job when...

However, when you use the following code, which deletes a job that starts more than 2 weeks from now,

BEGIN TRANDELETE FROM JobsWHERE jobid = 3SELECT * FROM JobsROLLBACK TRAN

the delete succeeds, as Figure 3 shows.

Another problem that comes to mind is that you might not want the trigger to interfere with deletions of job dates issued directly against the JobDates table, but to fire only when jobs are deleted from the Jobs table. In that case, you can use an INSTEAD OF trigger on the Jobs table instead of an AFTER trigger on the JobDates table. The code in Listing 6 drops and recreates the trigger.

Essentially, the INSTEAD OF trigger on the Jobs table performs an existence check the same way the AFTER trigger on the JobDates table did. However, because the trigger is defined on the Jobs table, to access the job dates, it has to perform a join between the deleted table—which holds the deleted jobs—and the JobDates table. The important point here is that because the trigger is an INSTEAD OF trigger, it fires before the foreign key performs its cascade activity. That is, the related job dates remain in the JobDates table instead of being deleted, as they were in the AFTER trigger scenario. Also, because the trigger's actions occur instead of the original modification, you have to resubmit the modification in case the trigger doesn't need to roll back the original activity. The resubmit operation deletes only the original jobs that were supposed to be deleted by checking that the jobs exist in the deleted table. Note that the trigger is now created on the Jobs table; the foreign key rejects attempts to directly delete rows from the JobDates table.