Issue with Trigger on record delete

Jhstephenson

I have a couple of triggers on one of my tables...

First trigger counts the number of child records for a parent when a new record is inserted in the child table.

This works just fine.

Second trigger counts the number of child records for a parent when a record is deleted from the child table.

This one doesn't work, from Aware. However, if I delete a record through SQL it works just like it should.

Now the strange part, in another BSV I have the same triggers on a table and they both work from Aware just fine.

Here is the delete trigger(CLIENTACCOUNT is the parent, COMPLAINT is the child):

ALTER TRIGGER [dbo].[RecountDeletedComplaints] 
   ON  [dbo].[COMPLAINT]
   AFTER DELETE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

UPDATE CLIENTACCOUNT
SET Count_Complaints=(SELECT COUNT(*) FROM COMPLAINT WHERE Complainant_ClientRecord_RID=CLIENTACCOUNT.ID)
FROM deleted
LEFT JOIN CLIENTACCOUNT ON CLIENTACCOUNT.ID=deleted.Complainant_ClientRecord_RID

UPDATE CLIENTACCOUNT
SET Count_ComplaintsReceived=(SELECT COUNT(*) FROM COMPLAINT WHERE RecipientClientRecord_RID=CLIENTACCOUNT.ID)
FROM deleted
LEFT JOIN CLIENTACCOUNT ON CLIENTACCOUNT.ID=deleted.RecipientClientRecord_RID
END

Here is the trigger that works fine from the other BSV (CASEMASTER is the parent, HEARINGS is the child)

ALTER TRIGGER "dbo"."RecountDeletedClientHearings" 
   ON  HEARINGS
   AFTER DELETE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

UPDATE CASEMASTER
SET Count_Hearings=(SELECT COUNT(*) FROM HEARINGS WHERE parent_CaseMaster_RID=CASEMASTER.ID)
FROM deleted
LEFT JOIN CASEMASTER ON CASEMASTER.ID=deleted.parent_CaseMaster_RID
END

Anyone with have any ideas why the same basic trigger would work in one bsv but not another?

Thanks,
Jim

BLOMASKY

While I have no clue, since the Triggers look fine (not sure why LEFT OUTER JOINS and not INNER JOINS), but since it works in SQL, could the problem be that you have some rules on the PARENT table that are executed AFTER the child is deleted, and since the parent record is in context, when your rules on the parent finish, the row is updated and it overwrites your Trigger calc?

Could test this by disabling rules on parent.

Bruce

Jhstephenson

Thanks Bruce,

The only rules on the parent table are ones to initialize specific attributes when new records are created. I can't see how any of them would impact the trigger, but I will play with it and see.