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_DeleteAdmissionRelatedInfo
> -- 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 tblProgramDischarge Table
> DELETE FROM tblProgramDischarge
> WHERE AdmissionID = @AdmissionID
>
> -- And now I'll use that value to delete the data in
> -- the tblRoomAssignment Table
> DELETE FROM tblRoomAssignment
> 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 tblUnitedWaySurvey Table
> DELETE FROM tblUnitedWaySurvey
> WHERE AdmissionID = @AdmissionID
>
> -- And now I'll use that value to delete the data in
> -- the tblWFGMSurvey Table
> DELETE FROM tblWFGMSurvey
> WHERE AdmissionID = @AdmissionID