Delete trigger not working when multiple records deleted

Question

Post reply

Delete trigger not working when multiple records deleted

MrBaseball34

SSCrazy

Points: 2515
More actions
September 12, 2008 at 8:02 am

#197749

In the trigger below, I am getting an error when deleting > 1 row at a time.
I understand that the deleted table will contain all rows deleted in one statement but
how do I need to handle that in this trigger? I'm not getting it.
CREATE TRIGGER [tr_ptChemoMedicareBilling_StoreCharges_del]
ON [dbo].[ptChemoMedicareBilling]
AFTER DELETE
AS
BEGIN
SET NOCOUNT ON
DECLARE @MRN varchar(25)
DECLARE @dos datetime
DECLARE @ChargeSourceID int
SET @MRN = (SELECT MRN FROM deleted)
SET @dos = (SELECT DateOfService FROM ptChemoOrders o
WHERE o.OrderID = (SELECT OrderID FROM deleted)
)
SET @ChargeSourceID = (SELECT ID FROM deleted)
DELETE FROM ptEncounterDetailsCharges
WHERE MRN = @MRN
AND DOS = @dos
AND ChargeSourceID = @ChargeSourceID
SET NOCOUNT OFF
END
GO

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Here are 2 options:

[font="Courier New"]CREATE TRIGGER [tr_ptChemoMedicareBilling_StoreCharges_del]

ON [dbo].[ptChemoMedicareBilling]

AFTER DELETE

AS

BEGIN

SET NOCOUNT ON

DELETE P

FROM

ptEncounterDetailsCharges P JOIN

deleted D ON

P.MRN = D.MRN AND

P.DOS = D.DOS AND

P.CHARgeSourceID = D.CHARgeSourceID

OR

DELETE

FROM

ptEncounterDetailsCharges

WHERE

EXISTS(SELECT * FROM deleted D WHERE MRN = D.MRN AND

DOS = D.DOS AND

CHARgeSourceID = D.CHARgeSourceID)

SET NOCOUNT OFF

END

GO[/font]

I recommend against using variables in triggers for this very problem. You could implement a RBAR (cursor or loop) solution, but that will kill your performance.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

MrBaseball34 SSCrazy Points: 2515 More actions · Answer 2

Thanks, Jack, but the DOS comes from the ptChemoOrders table not from the

ptChemoMedicalBilling table that the trigger is on. The ptChemoMedicalBilling record is based on the OrderID in ptChemoOrders. How would I do that join?

MrBaseball34 SSCrazy Points: 2515 More actions · Answer 3

Here are the relations:

Table ptChemoOrders:

-------------------------------

OrderID -- PK

DateOfService

MRN

Table ptChemoMedicareBilling:

-------------------------------

ID -- PK

OrderID -- pointer to ptChemoOrders.OrderID

Table ptEncounterDetailsCharges:

-------------------------------

DOS -- pointer to ptChemoOrders.DateOfService

MRN -- pointer to ptChemoOrders.MRN

ChargeSourceID -- pointer to ptChemoMedicareBilling.ID

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

MrBaseball34 (9/12/2008)
Thanks, Jack, but the DOS comes from the ptChemoOrders table not from the
ptChemoMedicalBilling table that the trigger is on. The ptChemoMedicalBilling record is based on the OrderID in ptChemoOrders. How would I do that join?

Oops, that's what happens when you skim. I think these changes will help:

[font="Courier New"]CREATE TRIGGER [tr_ptChemoMedicareBilling_StoreCharges_del]

ON [dbo].[ptChemoMedicareBilling]

AFTER DELETE

AS

BEGIN

SET NOCOUNT ON

DELETE P

FROM

ptEncounterDetailsCharges P JOIN

deleted D ON

P.MRN = D.MRN AND

P.CHARgeSourceID = D.CHARgeSourceID JOIN

ptChemoOrders CO ON

D.OrderId = CO.OrderId AND

P.DOS = CO.DateOfService

OR

DELETE

FROM

ptEncounterDetailsCharges

WHERE

EXISTS(SELECT * FROM deleted D JOIN ptChemoOrders CO ON

D.OrderId = CO.OrderId WHERE MRN = D.MRN AND

CO.DOS = D.DOS AND

CHARgeSourceID = D.CHARgeSourceID)

SET NOCOUNT OFF

END

GO[/font]

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

MrBaseball34 SSCrazy Points: 2515 More actions · Answer 5

I believe that may work for me. let me test it right quick.

MrBaseball34 SSCrazy Points: 2515 More actions · Answer 6

MrBaseball34

SSCrazy

Points: 2515

September 12, 2008 at 9:14 am

#871067

Yepper, that did it. Thanks a million.