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
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
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
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*
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
@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*
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
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
Dex*,
Your error matches a previous sql-server version fault:
http://support.microsoft.c
(Just in case you want to make this 6.5 compatible...)
But your exact problem is here (For sql-server 7):
http://support.microsoft.c
(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.
@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*
... 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....
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-00
CFEF54C9-8D9E-40FA-A754-1F
8A6A3B19-F91F-4103-B051-24
490A7121-B1D1-4BC2-99B5-AD
A2909DF4-FAAF-4ADE-A1DF-E8
79186F24-09E8-4B49-93B4-F4
AccountID AssetID Change
--------------------------
00000000-0000-0000-0000-00
CFEF54C9-8D9E-40FA-A754-1F
8A6A3B19-F91F-4103-B051-24
490A7121-B1D1-4BC2-99B5-AD
A2909DF4-FAAF-4ADE-A1DF-E8
79186F24-09E8-4B49-93B4-F4
Dex*
@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*
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..
Business Accounts
Answer for Membership
by: LowfatspreadPosted on 2004-03-30 at 12:34:16ID: 10717443
whats the point of the entryid condition on the Join ?