Multiple Deletions From Different Tables in SQL Server Trigger

[bmccollum]

I have written a trigger that's supposed to go out and delete
corresponding records from multiple tables once I delete a specific
record from a table called tblAdmissions.

This does not work and I'm not sure why...

Here's the code that's supposed to run, let's say, if a user (via a VB
6.0 interface) decides to delete a record. If the record in the
tblAdmissions table has the primary key (AdmissionID) of "123", then
the code below is supposed to search other tables that have related
information in them and also have an AdmissionID of "123" and delete
that information as well.

Any ideas? Here's the code:

CREATE TRIGGER tr_DeleteAdmiss ionRelatedInfo
-- and here is the table name
ON tblAdmissions
-- the operation type goes here
FOR DELETE

AS
-- I just need one variable this time
DECLARE @AdmissionID int
-- Now I'll make use of the deleted virtual table
SELECT @AdmissionID = (SELECT @AdmissionID FROM Deleted)

-- And now I'll use that value to delete the data in
-- the tblASIFollowUp Table
DELETE FROM tblASIFollowUp
WHERE AdmissionID = @AdmissionID

-- And now I'll use that value to delete the data in
-- the tblProgramDisch arge Table
DELETE FROM tblProgramDisch arge
WHERE AdmissionID = @AdmissionID

-- And now I'll use that value to delete the data in
-- the tblRoomAssignme nt Table
DELETE FROM tblRoomAssignme nt
WHERE AdmissionID = @AdmissionID

-- And now I'll use that value to delete the data in
-- the tblTOADS Table
DELETE FROM tblTOADS
WHERE AdmissionID = @AdmissionID

-- And now I'll use that value to delete the data in
-- the tblUnitedWaySur vey Table
DELETE FROM tblUnitedWaySur vey
WHERE AdmissionID = @AdmissionID

-- And now I'll use that value to delete the data in
-- the tblWFGMSurvey Table
DELETE FROM tblWFGMSurvey
WHERE AdmissionID = @AdmissionID

[Ginters]

Does it all not work or if you break it down into sections does it
still not work. Also how does it handle null values. When I started
using triggers comparisons with Nulls were a right pain. I take it that
all these tables are all in the same database with the same
permissions.

Ginters

bmccollum wrote:

I have written a trigger that's supposed to go out and delete
corresponding records from multiple tables once I delete a specific
record from a table called tblAdmissions.

This does not work and I'm not sure why...

Here's the code that's supposed to run, let's say, if a user (via a VB 6.0 interface) decides to delete a record. If the record in the
tblAdmissions table has the primary key (AdmissionID) of "123", then
the code below is supposed to search other tables that have related
information in them and also have an AdmissionID of "123" and delete
that information as well.

Any ideas? Here's the code:

CREATE TRIGGER tr_DeleteAdmiss ionRelatedInfo
-- and here is the table name
ON tblAdmissions
-- the operation type goes here
FOR DELETE

AS
-- I just need one variable this time
DECLARE @AdmissionID int
-- Now I'll make use of the deleted virtual table
SELECT @AdmissionID = (SELECT @AdmissionID FROM Deleted)

-- And now I'll use that value to delete the data in
-- the tblASIFollowUp Table
DELETE FROM tblASIFollowUp
WHERE AdmissionID = @AdmissionID

-- And now I'll use that value to delete the data in
-- the tblProgramDisch arge Table
DELETE FROM tblProgramDisch arge
WHERE AdmissionID = @AdmissionID

-- And now I'll use that value to delete the data in
-- the tblRoomAssignme nt Table
DELETE FROM tblRoomAssignme nt
WHERE AdmissionID = @AdmissionID

-- And now I'll use that value to delete the data in
-- the tblTOADS Table
DELETE FROM tblTOADS
WHERE AdmissionID = @AdmissionID

-- And now I'll use that value to delete the data in
-- the tblUnitedWaySur vey Table
DELETE FROM tblUnitedWaySur vey
WHERE AdmissionID = @AdmissionID

-- And now I'll use that value to delete the data in
-- the tblWFGMSurvey Table
DELETE FROM tblWFGMSurvey
WHERE AdmissionID = @AdmissionID

[David Portas]

What does "does not work" mean? Could you be a bit more specific. Why
not use cascading deletes on foreign keys for this? See the ON DELETE
CASCASE option in Books Online for details.

Your trigger will fail to delete all related rows if more than one row
is deleted from the Admissions table. Don't write triggers that way. To
do it in a trigger, try this:

CREATE TRIGGER tr_DeleteAdmiss ionRelatedInfo
ON tblAdmissions
FOR DELETE

AS

DELETE FROM tblASIFollowUp
WHERE EXISTS
(SELECT *
FROM Deleted
WHERE admissionid = tblASIFollowUp. admissionid)

.... etc

If you need more help, please post some code that will actually
reproduce the problem, including the CREATE, INSERT and DELETE
statements (simplified if possible please).
--
David Portas
SQL Server MVP
--

[Hugo Kornelis]

On 31 Jan 2005 08:13:54 -0800, bmccollum wrote:

This does not work and I'm not sure why...
Hi bmccollum,

Well, "does not work" is not exactly an accurate description of what's
happening. Is the delete rejected? Is the delete accepted, but the action
that the trigger should do is not done? Do you get error messages? Is
white smoke bellowing out of your server?
Any ideas? Here's the code: (snip)

Based on your code, I can do a wild guess. In fact, you've got two
problems. Both are here:
SELECT @AdmissionID = (SELECT @AdmissionID FROM Deleted)

First, the second @ should be left out. This will simply set the variable
@AdmissionID equal to itself.

But if you change it to

SELECT @AdmissionID = (SELECT AdmissionID FROM Deleted)
or
SET @AdmissionID = (SELECT AdmissionID FROM Deleted)
or
SELECT @AdmissionID = AdmissionID FROM Deleted

you'll still have problems. Not if you delete only one row, but you'll get
an error as soon as one DELETE operation deletes more than one row from
the admissions table. It's important to know that triggers fire once per
statement, not once per row. If three rows are deleted, the deleted
pseudo-table will hold three rows. This will cause the first two versions
of the assignment to error; the third will simply assign the value from
one of these three rows to @AdmissionID.

Even if your present application will never delete more than one row at a
time, you should always ensure that your triggers handle multi-row
inserts, updates and deletes well. Someday, your application will be
changed...

CREATE TRIGGER tr_DeleteAdmiss ionRelatedInfo
ON tblAdmissions
FOR DELETE
AS

DELETE FROM tblASIFollowUp
WHERE EXISTS
(SELECT *
FROM deleted
WHERE deleted.Admissi onID = tblASIFollowUp. AdmissionID)

(etc)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[--CELKO--]

A few things to start with. Read ISO-11179, so you will stop putting
those silly prefixes on data element names. Besides violating
standards, it makes a data dictionary almost impossible to use. The
reason I call it silly is that SQL only has one data structure, so the
prefix is redundant, improper and useless all at once.

You name a thing for that it is; you do not name a thing for how it is
modeled, where it is stored, its datatype, etc. Think logical AND NOT
physical.

Do you really have tables with only one row in them? That is what a
singular name says; tables out to be collective or plural. A table is
a set, not am object instance.

Do not depend on the use of the "little snail" to identify your
parameter to the guy maintaining or porting your code. What does it
mean in the data model?

Use "SET <var> = <exp>;" instead of "SELECT <var> = .." so that you do
not create confusion and the code will port. SQL Server has a lot of
options for standard code now, so use them.

Now the real question. Why are you still thinking of procedural code
in a declarative language, like SQL? You can use DRI (declarative
referential integrity) actions to do this. Try this skeleton:

CREATE SCHEMA Foobar ..
...
CREATE TABLE Admissions -- the source of the data element
(admission_id INTEGER NOT NULL PRIMARY KEY,
...);

CREATE TABLE ASI_Followups
( ..
admission_id INTEGER NOT NULL
REFERENCES Admissions (admission_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
...);

CREATE TABLE ProgramDischarg es
( ..
admission_id INTEGER NOT NULL
REFERENCES Admissions (admission_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
...);

CREATE TABLE RoomAssignments
( ..
admission_id INTEGER NOT NULL
REFERENCES Admissions (admission_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
...);

CREATE TABLE Toads -- weird name!
( ..
admission_id INTEGER NOT NULL
REFERENCES Admissions (admission_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
...);

CREATE TABLE UnitedWaySurvey
( ..
admission_id INTEGER NOT NULL
REFERENCES Admissions (admission_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
...);

CREATE TABLE WFGMSurvey
( ..
admission_id INTEGER NOT NULL
REFERENCES Admissions (admission_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
...);

Besides being easier to code, this gives the optimizer information
about the relationships among the tables, so ALL your queries improve.
It is also faster than a TRIGGER. For example, in Sybase SQL Anywhere
there would be a single occurrence of each admission_id value and
pointer chains to all the table referencing it. Updates and deletes
are almost immediate even on huge tables.

You are still un-learning procedural code -- your "tbl-" prefixes were
a good sign that your real problem is foundations. After cleaning up
SQL code for 15-20 years, I have a good set of diagnostics :)
Go to BOL and look teh DRI you need.