Solved

Need to fix this trigger to run on both SQL7 and SQL 2000.

Posted on 2004-03-30
12
912 Views
Last Modified: 2010-08-05
I need to have code for this trigger that works properly on both SQL7 and SQL 2000.  If I try to run the original version (made for SQL7) on SQL2000, it gives me an error.  With a small change, I can make it so that SQL 2000 accepts it, but then SQL7 doesn't like it.  Help me come up with a version that works on both so I can have one script that I can use on both platforms.

FYI:  I can't accept any answer that uses cursors or that doesn't work properly on batch updates.

Here is the trigger code:

-------------------------------------
-- SQL Server 7.0
-------------------------------------
CREATE TRIGGER tblAccountEntries_Maintain_Balance
ON dbo.tblAccountEntries
FOR INSERT, UPDATE, DELETE
AS
      -- Do not return row count
      SET NOCOUNT ON
      -- Update the balance if the amount, AssetID, or AccountID has changed
      -- OR if the record is being deleted (indicated by COLUMNS_UPDATED() = 0)
      IF ( UPDATE(Amount) OR UPDATE(AssetID) OR UPDATE(AccountID) OR (COLUMNS_UPDATED() = 0) )
            BEGIN
                  -- Update the Balances
                  UPDATE
                        dbo.tblAccountBalances
                  SET
                        CurrentBalance = AB.CurrentBalance + Change
                  FROM
                        dbo.tblAccountBalances AB INNER JOIN
                         (
                              SELECT COALESCE(I.AccountID, D.AccountID) AS AccountID,
                                    COALESCE(I.AssetID, D.AssetID) AS AssetID,
                                    SUM(COALESCE(I.Amount, 0) - COALESCE(D.Amount,0)) AS Change
                                    FROM Inserted I FULL OUTER JOIN Deleted D ON
                                          I.AccountID = D.AccountID
                                          AND
                                          I.AssetID = D.AssetID
                                          AND
                                          I.EntryID = D.EntryID
                                    GROUP BY
                                          COALESCE(I.AccountID, D.AccountID),
                                          COALESCE(I.AssetID, D.AssetID)
                         ) AS ChangeData ON
                              AB.AssetID = AssetID
                              AND
                              AB.AccountID = AccountID

                  -- Rollback on error
                  IF ( @@ERROR <> 0 )
                        BEGIN
                              ROLLBACK TRANSACTION
                        END
            END


-----------------------------
-- SQL Server 2000
------------------------------
CREATE TRIGGER tblAccountEntries_Maintain_Balance
ON dbo.tblAccountEntries
FOR INSERT, UPDATE, DELETE
AS
      -- Do not return row count
      SET NOCOUNT ON
      -- Update the balance if the amount, AssetID, or AccountID has changed
      -- OR if the record is being deleted (indicated by COLUMNS_UPDATED() = 0)
      IF ( UPDATE(Amount) OR UPDATE(AssetID) OR UPDATE(AccountID) OR (COLUMNS_UPDATED() = 0) )
            BEGIN
                  -- Update the Balances
                  UPDATE
                        dbo.tblAccountBalances
                  SET
                        CurrentBalance = AB.CurrentBalance + Change
                  FROM
                        dbo.tblAccountBalances AB INNER JOIN
                         (
                              SELECT COALESCE(I.AccountID, D.AccountID) AS AccountID,
                                    COALESCE(I.AssetID, D.AssetID) AS AssetID,
                                    SUM(COALESCE(I.Amount, 0) - COALESCE(D.Amount,0)) AS Change
                                    FROM Inserted I FULL OUTER JOIN Deleted D ON
                                          I.AccountID = D.AccountID
                                          AND
                                          I.AssetID = D.AssetID
                                          AND
                                          I.EntryID = D.EntryID
                                    GROUP BY
                                          COALESCE(I.AccountID, D.AccountID),
                                          COALESCE(I.AssetID, D.AssetID)
                         ) AS ChangeData ON
                              AB.AssetID = ChangeData.AssetID
                              AND
                              AB.AccountID = ChangeData.AccountID

                  -- Rollback on error
                  IF ( @@ERROR <> 0 )
                        BEGIN
                              ROLLBACK TRANSACTION
                        END
            END

---
(Yes, I really do always format my SQL code that way...)

The only change is to this part, where in the SQL2000 version, I specify the sub-query's alias on the columns:
                         ) AS ChangeData ON
                              AB.AssetID = ChangeData.AssetID
                              AND
                              AB.AccountID = ChangeData.AccountID

If I run the SQL7 version on SQL2K, I get this error:
      Server: Msg 209, Level 16, State 1, Procedure tblAccountEntries_Maintain_Balance, Line 2 Ambiguous column name 'AssetID'.

If I run the SQL2K version on SQL7, I get this error:
      Error 107: the column prefix 'ChangeData' does not match with a table name or alias name used in the query

Oh, and I'm always open to any other suggestions you guys could make about optimizing the query or anything like that.  This trigger runs quite frequently, so anything that improves the performance is worth extra points.

Dex*
0
Comment
Question by:Dexstar
  • 6
  • 4
  • 2
12 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 10717443
whats the point of the entryid condition on the Join ?  
0
 
LVL 19

Author Comment

by:Dexstar
ID: 10717502
@Lowfatspread:

It uniquely identifies each entry record, so that if a record is updated, the inserted and deleted values match up in the join.  Does it look like it isn't necessary to you?  I'd have to try removing it and do some testing.  Either way, I'm not sure it would fix the immediate problem.

Dex*
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 10717545
try this

you shouldn't have to modify the trigger for SQL 7 or 2000

i don't see the point of doing the individual column update tests
particularly as the trigger functions across the insert,update,delete
functions and the results are consistent...

so use a temp table, and only do the updates if a "Total" change has occurred for the account...  


CREATE TRIGGER tblAccountEntries_Maintain_Balance
ON dbo.tblAccountEntries
FOR INSERT, UPDATE, DELETE
AS
     -- Do not return row count
     SET NOCOUNT ON

Select AccountID,AssetId,Change Into #Temp
  From (
SELECT COALESCE(I.AccountID, D.AccountID) AS AccountID,
       COALESCE(I.AssetID, D.AssetID) AS AssetID,
       SUM(COALESCE(I.Amount, 0) - COALESCE(D.Amount,0)) AS Change
  FROM Inserted I
  FULL OUTER JOIN Deleted D
    ON I.AccountID = D.AccountID
   AND I.AssetID = D.AssetID
 GROUP BY  COALESCE(I.AccountID, D.AccountID),
           COALESCE(I.AssetID, D.AssetID)
     ) as X
 Where Change <> 0


              -- Update the Balances
               IF @@ERROR = 0
               BEGIN
                  UPDATE  dbo.tblAccountBalances
                     SET CurrentBalance = AB.CurrentBalance + T.Change
                    FROM   dbo.tblAccountBalances AB
                   INNER JOIN #Temp as T
                      ON AB.AssetID = T.AssetID
                     AND AB.AccountID = T.AccountID
               -- Rollback on error
                  IF ( @@ERROR <> 0 )
                    BEGIN
                         ROLLBACK TRANSACTION
                    END
               END
               else
                    BEGIN
                         ROLLBACK TRANSACTION
                    END



0
 
LVL 19

Author Comment

by:Dexstar
ID: 10717708
@Lowfatspread:

I'll try it right now.  Do you think that you way is going to be more efficient?  If so, why?  Isn't using a temporary table expensive?

Dex*
0
 
LVL 13

Accepted Solution

by:
danblake earned 250 total points
ID: 10717831
Dex*,

Your error matches a previous sql-server version fault:
http://support.microsoft.com/default.aspx?scid=kb;en-us;825019&Product=sql2k
(Just in case you want to make this 6.5 compatible...)

But your exact problem is here (For sql-server 7):
http://support.microsoft.com/default.aspx?scid=kb;en-us;225492&Product=sql

(Looks like its' not completly fixed yet, the change you need to make is quite simple....)


Basically change the update statement to:
UPDATE
                    dbo.tblAccountBalances
               SET
                    CurrentBalance = AB.CurrentBalance + Change
               FROM
                     (
                         SELECT COALESCE(I.AccountID, D.AccountID) AS AccountID,
                              COALESCE(I.AssetID, D.AssetID) AS AssetID,
                              SUM(COALESCE(I.Amount, 0) - COALESCE(D.Amount,0)) AS Change
                              FROM Inserted I FULL OUTER JOIN Deleted D ON
                                   I.AccountID = D.AccountID
                                   AND
                                   I.AssetID = D.AssetID
                                   AND
                                   I.EntryID = D.EntryID
                              GROUP BY
                                   COALESCE(I.AccountID, D.AccountID),
                                   COALESCE(I.AssetID, D.AssetID)
                     ) AS ChangeData
              INNER JOIN                     dbo.tblAccountBalances AB
             ON
                         AB.AssetID = ChangeData.AssetID
                         AND
                         AB.AccountID = ChangeData.AccountID


Not a massive major change, and should keep you up and running.
Saves using the temporary table !...

(Also I like the use of your update() /Columns_Updated checks..., Formating is also good.)

Rgds,

Dan.
0
 
LVL 19

Author Comment

by:Dexstar
ID: 10718008
@Dan:  I like the fact that you got the source of the problem.  That helps me out a lot.  It helps me to know that I'm going crazy ... It's actually a known SQL bug.  :)

@Lowfatspread:  I'd still like to hear if you think using a temporary table is a good idea.  I said I'd give points to optimizing the query.  There seems to be some disagreement on whether or not my UPDATE() usage is appropriate.

Dex*
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 13

Expert Comment

by:danblake
ID: 10718134
Dex*,

I've fixed this one before...

Thanks,

Dan.

0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 250 total points
ID: 10718189
... I like your formatting as well

I've never experienced problems with using a temp table, but we're not heavy users of triggers

it may depend on your mix of singleton to multiple row updates... but you need to cater for both...

if you've got multi-row updates then its probably as well to confirm you actually need to update
the column test only tell you that some rows have had a column change...

the best approach may be to do without the temp table altogether , like your original example
but you need to consider the query plans involved and the likely update scenarios...

the advantage of the initial creation of the temp table is that only 2 "small" virtual tables are being
processed... doing it on the update statement (may)involve the physical table as well...

e.g.
 
Just

              -- Update the Balances
               IF @@ERROR = 0
               BEGIN
                  UPDATE  dbo.tblAccountBalances
                     SET CurrentBalance = AB.CurrentBalance + T.Change
                    FROM   dbo.tblAccountBalances AB
                   INNER JOIN

(Select AccountID,AssetId,Change
  From (
SELECT COALESCE(I.AccountID, D.AccountID) AS AccountID,
       COALESCE(I.AssetID, D.AssetID) AS AssetID,
       SUM(COALESCE(I.Amount, 0) - COALESCE(D.Amount,0)) AS Change
  FROM Inserted I
  FULL OUTER JOIN Deleted D
    ON I.AccountID = D.AccountID
   AND I.AssetID = D.AssetID
 GROUP BY  COALESCE(I.AccountID, D.AccountID),
           COALESCE(I.AssetID, D.AssetID)
     ) as X
 Where Change <> 0
) as T
                      ON AB.AssetID = T.AssetID
                     AND AB.AccountID = T.AccountID



Ps . I still don't understand the inclusion of the Entryid test in your update statement....
0
 
LVL 19

Author Comment

by:Dexstar
ID: 10719307
I'm working a solution that combines the suggestions.  I like lowfat's idea of filtering out the records where Change = 0, to avoid unnecessary updates because I know SQL Server will physically do the update, even if the value doesn't change.  And, I will obviously need to change the order of the tables in the query to account for the SQL bug.

> Ps . I still don't understand the inclusion of the Entryid test in your update statement....

Consider a statement like this:
    UPDATE tblAccountEntries
    SET Amount = Amount + 1.50

Without the EntryID in the join, the AccountBalances get all messed up.       Here is the result of the sub-queries.  The 2nd one doesn't join on the EntryID, but the first one does.

AccountID                            AssetID     Change                
------------------------------------ ----------- ---------------------
00000000-0000-0000-0000-000000000001 100         61.5000
CFEF54C9-8D9E-40FA-A754-1FDE43640985 100         10.5000
8A6A3B19-F91F-4103-B051-2450673DD827 100         10.5000
490A7121-B1D1-4BC2-99B5-AD0020D997F3 100         4.5000
A2909DF4-FAAF-4ADE-A1DF-E8A7DD9D837E 100         3.0000
79186F24-09E8-4B49-93B4-F4D930748917 100         33.0000

AccountID                            AssetID     Change                
------------------------------------ ----------- ---------------------
00000000-0000-0000-0000-000000000001 100         2521.5000
CFEF54C9-8D9E-40FA-A754-1FDE43640985 100         73.5000
8A6A3B19-F91F-4103-B051-2450673DD827 100         73.5000
490A7121-B1D1-4BC2-99B5-AD0020D997F3 100         13.5000
A2909DF4-FAAF-4ADE-A1DF-E8A7DD9D837E 100         6.0000
79186F24-09E8-4B49-93B4-F4D930748917 100         726.0000


Dex*
0
 
LVL 19

Author Comment

by:Dexstar
ID: 10719425
@Lowfatspread:

Just a quick question about your modification...  Are those two identical, or is there a reason to use one over the other?

---
(Select AccountID,AssetId,Change
  From (
SELECT COALESCE(I.AccountID, D.AccountID) AS AccountID,
       COALESCE(I.AssetID, D.AssetID) AS AssetID,
       SUM(COALESCE(I.Amount, 0) - COALESCE(D.Amount,0)) AS Change
  FROM Inserted I
  FULL OUTER JOIN Deleted D
    ON I.AccountID = D.AccountID
   AND I.AssetID = D.AssetID
 GROUP BY  COALESCE(I.AccountID, D.AccountID),
           COALESCE(I.AssetID, D.AssetID)
     ) as X
 Where Change <> 0
) as T
---
(SELECT COALESCE(I.AccountID, D.AccountID) AS AccountID,
       COALESCE(I.AssetID, D.AssetID) AS AssetID,
       SUM(COALESCE(I.Amount, 0) - COALESCE(D.Amount,0)) AS Change
  FROM Inserted I
  FULL OUTER JOIN Deleted D
    ON I.AccountID = D.AccountID
   AND I.AssetID = D.AssetID
  GROUP BY  COALESCE(I.AccountID, D.AccountID),
           COALESCE(I.AssetID, D.AssetID)
   HAVING
        SUM(COALESCE(I.Amount, 0) - COALESCE(D.Amount,0)) <> 0
) as T
---

Dex*
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 10725585
hopefully they are the same ...

you need to check the query plan to confirm that the Server has actually performed the same
operations....

i usually like to try an restrict the numbers of functions and aggregations I ask the optimiser to perform
as to my mind that usually makes for easier code maintenance, and simplifies query optimisation requirements..

 
0
 
LVL 19

Author Comment

by:Dexstar
ID: 10744076
Thanks for your help guys!  :)  It is much appreciated!

-Dex*
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

760 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now