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_DeleteAdmissionRelatedInfo
ON tblAdmissions
FOR DELETE
AS

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

(etc)

Best, Hugo
--

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