?
Solved

Trigger not always firing?

Posted on 2004-10-18
5
Medium Priority
?
591 Views
Last Modified: 2008-02-01
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.SumOfRewardAmount,0)
               - ISNULL(D.SumOfRewardAmount,0)),
            TaxableTotal1 = ISNULL(TaxableTotal1,0)
              + (ISNULL(I.SumTaxableAmount1,0)
               - ISNULL(D.SumTaxableAmount1,0)),
            TaxableTotal2 = ISNULL(TaxableTotal2,0)
              + (ISNULL(I.SumTaxableAmount2,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.


0
Comment
Question by:tk3
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 26

Expert Comment

by:Hilaire
ID: 12345170
You could write it as follows to keep things in synch.
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
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 12345175
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
0
 
LVL 26

Accepted Solution

by:
Hilaire earned 2000 total points
ID: 12345191
Yet another typo ...

CREATE TRIGGER dbo.tgrServiceDetail
ON dbo.tblServiceDetail
FOR INSERT, UPDATE, DELETE
AS
     UPDATE S SET
          TotalAmount = b.SumOfAmount,
          TotalRewardBase = b.SumOfRewardAmount,
          TaxableTotal1 = b.SumTaxableAmount1,
          TaxableTotal2 = b.SumTaxableAmount2
     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
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 12346375
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.serviceid) 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


 
0
 

Author Comment

by:tk3
ID: 12346641
Hilare,
   Your answer was fantastic!
0

Featured Post

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question