Delete trigger not working when record deleted from another delete trigger

Question

Delete trigger not working when record deleted from another delete trigger

MrBaseball34

SSCrazy

Points: 2515
More actions
September 12, 2008 at 9:18 am

#212570

I have a delete trigger on a table named ptChemoOrderDetails that deletes records
from the ptEncounterDetailsCharges table.
I have a delete trigger on ptChemoOrders that deletes records from the ptChemoOrderDetails table.
Now, the delete trigger on ptChemoOrderDetails is not deleting the ptEncounterDetailsCharges records when I delete the ptChemoOrders record(s).
Is there a trick to doing this type of cascading delete in triggers?
-- ptChemoOrders delete trigger:
CREATE TRIGGER [tr_ptChemoOrders_del]
ON [dbo].[ptChemoOrders]
AFTER DELETE
AS
BEGIN
IF @@Rowcount=0
RETURN
SET NOCOUNT ON
DELETE cod
FROM ptChemoOrderDetails cod
JOIN deleted d ON
cod.OrderID = d.OrderID AND
SET NOCOUNT OFF
END
-- ptChemoOrderDetails delete trigger:
-- This works when deleteing only ptChemoOrderDetails records
CREATE TRIGGER [tr_ptChemoOrderDetails_StoreCharges_del]
ON [dbo].[ptChemoOrderDetails]
AFTER DELETE
AS
BEGIN
BEGIN
IF @@Rowcount=0
RETURN
SET NOCOUNT ON
-- INFUSION DRUG
DELETE FROM ptEncounterDetailsCharges
FROM deleted d
JOIN ptChemoOrders co ON d.OrderID = co.OrderID --DateOfService,MRN
LEFT OUTER JOIN coRegimenMeds rm on d.MedID = rm.MedID
JOIN ptEncounterDetailsCharges edc ON d.LineID = edc.ChargeSourceID
WHERE ISNULL(rm.DilutionMedId,0)=1
AND ISNULL(d.Given,0)=0
AND d.Fluids IS NOT NULL
AND d.FluidAmt>'0'
-- FLUIDS
DELETE FROM ptEncounterDetailsCharges
FROM deleted d
JOIN ptChemoOrders co ON d.OrderID = co.OrderID --DateOfService,MRN
LEFT OUTER JOIN coRegimenMeds rm on d.MedID = rm.MedID
JOIN ptEncounterDetailsCharges edc ON d.LineID = edc.ChargeSourceID
WHERE rm.MedID IS NULL
SET NOCOUNT OFF
END

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Why use a trigger? What's wrong with a cascading delete on the foreign key?

Do you have nested triggers on? (right click the server in object explorer, select properties, go to the advanced tab, look for the option 'Allow triggers to fire others', or exec sp_configure 'Nested triggers')

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MrBaseball34 SSCrazy Points: 2515 More actions · Answer 2

No, the cascading deletes, for some unknown reason, aren't working even though we have a Foreign key on the OrderID column.

Note: I just started here a little over a month ago and this place was without a seasoned SQL vet; they come from Access and don't really know a lot about DB design. Although SQL is not one of my strengths, I have shown them a few things and, with LOTS of help from you and others here and at Experts-Exchange, we've fixed several of the problems that they were having, especially performance related.

Ok, found out that this trigger is not really even needed because we don't actually delete those records but modify the Deleted column value to 1. I now have it working as an update trigger. The same goes for the ptChemoOrderDetails, a delete trigger is not needed because we do the same thing with the Deleted column. I do my ptEncounterDetailsCharges deletions in the ptChemoOrderDetails update trigger when the Deleted column is updated.

Thanks for looking at this, I really appreciate your help. With all the time spent on SQL the past few weeks, I feel like I'm taking over the DBA job here. HAHA!! :w00t: