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*
