tk3
asked on
Trigger not always firing?
I have the following trigger in my SQL 2000 database:
CREATE TRIGGER dbo.tgrServiceDetail
ON dbo.tblServiceDetail
FOR INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON
UPDATE S SET
TotalAmount = ISNULL(TotalAmount,0)
+ (ISNULL(I.SumOfAmount,0)
- ISNULL(D.SumOfAmount,0)),
TotalRewardBase = ISNULL(TotalRewardBase,0)
+ (ISNULL(I.SumOfRewardAmoun t,0)
- ISNULL(D.SumOfRewardAmount ,0)),
TaxableTotal1 = ISNULL(TaxableTotal1,0)
+ (ISNULL(I.SumTaxableAmount 1,0)
- ISNULL(D.SumTaxableAmount1 ,0)),
TaxableTotal2 = ISNULL(TaxableTotal2,0)
+ (ISNULL(I.SumTaxableAmount 2,0)
- ISNULL(D.SumTaxableAmount2 ,0))
FROM tblService as S
LEFT JOIN
(SELECT ServiceID,
SUM(Amount) As SumOfAmount,
SUM(RewardAmount) As SumOfRewardAmount,
SUM(TaxableAmount1) as SumTaxableAmount1,
SUM(TaxableAmount2) as SumTaxableAmount2
FROM inserted
GROUP BY ServiceID) AS I
ON S.ServiceID = I.ServiceID
LEFT JOIN
(SELECT ServiceID,
SUM(Amount) As SumOfAmount,
SUM(RewardAmount) As SumOfRewardAmount,
SUM(TaxableAmount1) as SumTaxableAmount1,
SUM(TaxableAmount2) as SumTaxableAmount2
FROM deleted
GROUP BY ServiceID) AS D
ON S.ServiceID = D.ServiceID
SET NOCOUNT OFF
I am using an Access Data Project as a front end. I set up triggers so that I wouldn't have to mess with computations everywhere something changes on any of my forms.
Now, to my horror, it appears that my trigger is often not firing the very first time I add detail records to my ServiceDetail table.
Once the trigger successfully fires once, then it always seems to work. However, it gets out of sync with the table that first time, so it is then always out of sync.
I have two questions:
1. Why does this happen? I was led to believe that triggers ALWAYS fire when a table is changed.
2. How can I set this up to recompute the values in the detail table by summing them again from scratch instead of computing the incremental change. I created this trigger by cloning some other stuff I saw, so I am not an expert at triggers by any means.
Any help would be appreciated.
CREATE TRIGGER dbo.tgrServiceDetail
ON dbo.tblServiceDetail
FOR INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON
UPDATE S SET
TotalAmount = ISNULL(TotalAmount,0)
+ (ISNULL(I.SumOfAmount,0)
- ISNULL(D.SumOfAmount,0)),
TotalRewardBase = ISNULL(TotalRewardBase,0)
+ (ISNULL(I.SumOfRewardAmoun
- ISNULL(D.SumOfRewardAmount
TaxableTotal1 = ISNULL(TaxableTotal1,0)
+ (ISNULL(I.SumTaxableAmount
- ISNULL(D.SumTaxableAmount1
TaxableTotal2 = ISNULL(TaxableTotal2,0)
+ (ISNULL(I.SumTaxableAmount
- ISNULL(D.SumTaxableAmount2
FROM tblService as S
LEFT JOIN
(SELECT ServiceID,
SUM(Amount) As SumOfAmount,
SUM(RewardAmount) As SumOfRewardAmount,
SUM(TaxableAmount1) as SumTaxableAmount1,
SUM(TaxableAmount2) as SumTaxableAmount2
FROM inserted
GROUP BY ServiceID) AS I
ON S.ServiceID = I.ServiceID
LEFT JOIN
(SELECT ServiceID,
SUM(Amount) As SumOfAmount,
SUM(RewardAmount) As SumOfRewardAmount,
SUM(TaxableAmount1) as SumTaxableAmount1,
SUM(TaxableAmount2) as SumTaxableAmount2
FROM deleted
GROUP BY ServiceID) AS D
ON S.ServiceID = D.ServiceID
SET NOCOUNT OFF
I am using an Access Data Project as a front end. I set up triggers so that I wouldn't have to mess with computations everywhere something changes on any of my forms.
Now, to my horror, it appears that my trigger is often not firing the very first time I add detail records to my ServiceDetail table.
Once the trigger successfully fires once, then it always seems to work. However, it gets out of sync with the table that first time, so it is then always out of sync.
I have two questions:
1. Why does this happen? I was led to believe that triggers ALWAYS fire when a table is changed.
2. How can I set this up to recompute the values in the detail table by summing them again from scratch instead of computing the incremental change. I created this trigger by cloning some other stuff I saw, so I am not an expert at triggers by any means.
Any help would be appreciated.
Oops wrong alias name
CREATE TRIGGER dbo.tgrServiceDetail
ON dbo.tblServiceDetail
FOR INSERT, UPDATE, DELETE
AS
UPDATE S SET
TotalAmount = b.TotalAmount,
TotalRewardBase = b.TotalRewardBase,
TaxableTotal1 = b.TaxableTotal1,
TaxableTotal2 = b.TaxableTotal2
FROM tblService as S
INNER JOIN (
SELECT ServiceID,
SUM(Amount) As SumOfAmount,
SUM(RewardAmount) As SumOfRewardAmount,
SUM(TaxableAmount1) as SumTaxableAmount1,
SUM(TaxableAmount2) as SumTaxableAmount2
FROM dbo.tblServiceDetail
WHERE ServiceID in (SELECT ServiceID FROM inserted UNION SELECT ServiceID FROM deleted)
GROUP BY ServiceID
) AS b ON S.ServiceID = b.ServiceID
CREATE TRIGGER dbo.tgrServiceDetail
ON dbo.tblServiceDetail
FOR INSERT, UPDATE, DELETE
AS
UPDATE S SET
TotalAmount = b.TotalAmount,
TotalRewardBase = b.TotalRewardBase,
TaxableTotal1 = b.TaxableTotal1,
TaxableTotal2 = b.TaxableTotal2
FROM tblService as S
INNER JOIN (
SELECT ServiceID,
SUM(Amount) As SumOfAmount,
SUM(RewardAmount) As SumOfRewardAmount,
SUM(TaxableAmount1) as SumTaxableAmount1,
SUM(TaxableAmount2) as SumTaxableAmount2
FROM dbo.tblServiceDetail
WHERE ServiceID in (SELECT ServiceID FROM inserted UNION SELECT ServiceID FROM deleted)
GROUP BY ServiceID
) AS b ON S.ServiceID = b.ServiceID
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
not sure why you have problems with the first insert...
but this is more like how i'd write it..
CREATE TRIGGER dbo.tgrServiceDetail
ON dbo.tblServiceDetail
FOR INSERT, UPDATE, DELETE
AS
UPDATE tblService SET
TotalAmount = Coalesce(TotalAmount,0)
+ Coalesce(Y.Amount,0)
, TotalRewardBase = Coalesce(TotalRewardBase,0 )
+ Coalesce(Y.RewardAmount,0)
, TaxableTotal1 = Coalesce(TaxableTotal1,0)
+ Coalesce(Y.TaxableAmount1, 0)
, TaxableTotal2 = Coalesce(TaxableTotal2,0)
+ Coalesce(Y.TaxableAmount2, 0)
FROM tblService as S
Inner Join (Select Serviceid
,Sum(amount) as amount
,sum(RewardAmount) as RewardAmount
,Sum(Taxableamount1) as Taxableamount1
,Sum(Taxableamount2) as Taxableamount2
From (
SELECT coalesce(i.ServiceID,d.ser viceid) as serviceid,
coalesce(I.Amount,0) - coalesce(D.amount,0) As Amount,
coalesce(I.RewardAmount,0) - coalesce(D.RewardAmount,0) As RewardAmount,
coalesce(I.TaxableAmount1, 0) - coalesce(D.TaxableAmount1, 0) as TaxableAmount1,
coalesce(I.TaxableAmount2, 0) - coalesce(D.TaxableAmount2, 0) as TaxableAmount2
FROM inserted
Full Outer Join Deleted
on I.serviceid=d.serviceid
) as X
GROUP BY ServiceID
Having Sum(amount) <> 0 or Sum(RewardAmount) <> 0
or Sum(Taxableamount1) <> 0 or sum(Taxableamount2) <> 0
) AS Y
ON S.ServiceID = Y.ServiceID
but this is more like how i'd write it..
CREATE TRIGGER dbo.tgrServiceDetail
ON dbo.tblServiceDetail
FOR INSERT, UPDATE, DELETE
AS
UPDATE tblService SET
TotalAmount = Coalesce(TotalAmount,0)
+ Coalesce(Y.Amount,0)
, TotalRewardBase = Coalesce(TotalRewardBase,0
+ Coalesce(Y.RewardAmount,0)
, TaxableTotal1 = Coalesce(TaxableTotal1,0)
+ Coalesce(Y.TaxableAmount1,
, TaxableTotal2 = Coalesce(TaxableTotal2,0)
+ Coalesce(Y.TaxableAmount2,
FROM tblService as S
Inner Join (Select Serviceid
,Sum(amount) as amount
,sum(RewardAmount) as RewardAmount
,Sum(Taxableamount1) as Taxableamount1
,Sum(Taxableamount2) as Taxableamount2
From (
SELECT coalesce(i.ServiceID,d.ser
coalesce(I.Amount,0) - coalesce(D.amount,0) As Amount,
coalesce(I.RewardAmount,0)
coalesce(I.TaxableAmount1,
coalesce(I.TaxableAmount2,
FROM inserted
Full Outer Join Deleted
on I.serviceid=d.serviceid
) as X
GROUP BY ServiceID
Having Sum(amount) <> 0 or Sum(RewardAmount) <> 0
or Sum(Taxableamount1) <> 0 or sum(Taxableamount2) <> 0
) AS Y
ON S.ServiceID = Y.ServiceID
ASKER
Hilare,
Your answer was fantastic!
Your answer was fantastic!
Any particular reason to set NOCOUNT ON in the trigger ? I always do it in stored procedures intended to work with ADO, but never used it in a trigger ?
Although I understand the logic behind your code above, to calculate only the delta after each and every operation on the table, I think the code below should be more robust.
Putting the where clause
WHERE ServiceID in (SELECT ServiceID FROM inserted UNION SELECT ServiceID FROM deleted)
in the inner query should make things faster then in the outer query.
CREATE TRIGGER dbo.tgrServiceDetail
ON dbo.tblServiceDetail
FOR INSERT, UPDATE, DELETE
AS
UPDATE S SET
TotalAmount = b.TotalAmount,
TotalRewardBase = b.TotalRewardBase,
TaxableTotal1 = b.TaxableTotal1,
TaxableTotal2 = b.TaxableTotal2
FROM tblService as S
INNER JOIN (
SELECT ServiceID,
SUM(Amount) As SumOfAmount,
SUM(RewardAmount) As SumOfRewardAmount,
SUM(TaxableAmount1) as SumTaxableAmount1,
SUM(TaxableAmount2) as SumTaxableAmount2
FROM dbo.tblServiceDetail
WHERE ServiceID in (SELECT ServiceID FROM inserted UNION SELECT ServiceID FROM deleted)
GROUP BY ServiceID
) AS b ON S.ServiceID = I.ServiceID