Several DELETEs in Delete Trigger

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Several DELETEs in Delete Trigger

Author

Topic

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-10-07 : 15:29:29

Hiya,
I have a table Patients, which has several child tables: PatientNotes, PatientAppointments, PatientInsurance, PatientRecalls, RelatedPatInfo. Some are one-to-one, some are one-to-many. There are no foreign keys defined on the children. I want to use a trigger to delete all children when a patient is deleted. This is my current trigger, which is acting funky.
CREATE TRIGGER DeletePatient ON dbo.Patients
FOR DELETE
AS
BEGIN
DELETE FROM PatientIns WHERE EXISTS(SELECT ID FROM Deleted WHERE ID = PatientIns.PatID)
print 'deleted patientins'
DELETE FROM RelatedPatInfo WHERE EXISTS(SELECT ID FROM Deleted WHERE ID = RelatedPatInfo.ID)
print 'deleted relatedpatinfo'
DELETE FROM PatientsRecall WHERE EXISTS(SELECT ID FROM Deleted WHERE ID = PatientsRecall.PatID)
print 'deleted patientsrecall'
DELETE FROM PatientAppts WHERE EXISTS(SELECT ID FROM Deleted WHERE ID = PatientAppts.PatientID)
print 'deleted patientappts'
DELETE FROM PatientsNotes WHERE EXISTS(SELECT ID FROM Deleted WHERE ID = PatientsNotes.PatID)
print 'deleted patientsnotes'
END
I'm trying to troubleshoot this: This is an output I got using print statements:
deleted patientins

(1 row(s) affected)

deleted relatedpatinfo

(0 row(s) affected)

deleted patientsrecall

(0 row(s) affected)

deleted patientappts

(1 row(s) affected)

(1 row(s) affected)

deleted patientnotes

What the patient had was: 0 rows in PatientIns, 1 row in RelatedPatInfo,0 Rows in PatientsRecall, 0 Rows in PatientAppts, 1 row in PatientsNotes.

I can make no sense of this. And ADO is bugging me in VB when there are no rows in the PatientIns table that "Row cannot be located for updating or refreshing", and when there is more than one row in PatientIns "Key Column is Insufficient...". One row doesn't seem to cause an error.

Thanks. I'm out of my depths.
Sarah

Sarah Berger MCSD

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-07 : 15:45:48

See if setting NOCOUNT ON helps, and try using the JOIN syntax:

CREATE TRIGGER DeletePatient ON dbo.Patients
FOR DELETE
AS
SET NOCOUNT ON
BEGIN
DELETE FROM P
PatientIns P INNER JOIN Deleted D ON P.PatID=D.ID
print 'deleted patientins'
DELETE FROM R
FROM RelatedPatInfo R INNER JOIN Deleted D ON R.ID=D.ID
print 'deleted relatedpatinfo'
DELETE FROM P
FROM PatientsRecall P INNER JOIN Deleted D ON P.PatID=D.ID
print 'deleted patientsrecall'
DELETE FROM P
FROM PatientAppts P INNER JOIN Deleted D ON P.PatientID=D.ID)
print 'deleted patientappts'
DELETE FROM P
FROM PatientsNotes P INNER JOIN Deleted D ON P.PatID=D.ID
print 'deleted patientsnotes'
END

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-10-07 : 16:10:23

Thanks, Rob.
ADO definitely has a case with the COUNT. I've had similar problems many times when I forgot to use NOCOUNT in stored procs, but I didn't think it would cause a problem in a trigger.

Cheers, (And back I go into my depths again!)
Sarah

Sarah Berger MCSD

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources