Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Loop Deleted trigger table Expand / Collapse
Author
Message
Posted Thursday, August 2, 2012 12:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 3, 2012 10:13 PM
Points: 3, Visits: 10
Hi, I use SQL Server 2008 R2

I read in this forum, that it's not best practice looping Inserted/Deleted trigger tables, but I wonder how to achieve the following task without looping:

Master table = JournalEntry
Detail table = JournalEntryLine

I've set a cascading delete between these tables, I need to update a third table called AccountBalance (via SPROC), each time a row from the detail table JournalEntryLine is deleted.

Is it possible to achieve this task without looping?
If the answer is no, how to loop through Deleted rows?

Thanks.
Post #1339414
Posted Thursday, August 2, 2012 12:24 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 12:34 PM
Points: 31,181, Visits: 15,626
You'd have to provide more details, but if you have matching rows, then you don't need to loop.

If I delete from JournalEntryLine, I have a trigger there that does a join to delete.

create trigger ...

as

delete ab
from accountbalance ab
inner join deleted d
on ab.id = d.id


If you are updating things, the same thing occurs. If you have a sproc that takes one value and uses that to update one row, then you would have to loop, or use cross apply, but that might be a loop as well.

But really you shouldn't do this. You should write the trigger code to include the same logic as your sproc, but operating in a batch fashion. Whether it's an insert/update/delete, it can work.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1339436
Posted Friday, August 3, 2012 2:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 3, 2012 10:13 PM
Points: 3, Visits: 10
Thank you for your help, here is my script:

Stored Procedure:
ALTER PROC [dbo].[usp_UpdateAccountBalance] @AccountId INT, @Amount MONEY
AS

DECLARE @Count INT;
SELECT @Count = COUNT(*) FROM dbo.AccountBalance
WHERE AccountId = @AccountId;

IF (@Count = 0)
BEGIN
INSERT INTO dbo.AccountBalance
( AccountId ,
Balance
)
VALUES ( @AccountId ,
@Amount -- Balance - money
)
END
ELSE
BEGIN
UPDATE dbo.AccountBalance
SET Balance = Balance + @Amount
WHERE AccountId = @AccountId;
END


Trigger on table JournalEntryLine table:
ALTER TRIGGER [dbo].[tr_AccountBalance]
ON [dbo].[JournalEntryLine]
AFTER INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON;
DECLARE @Amount MONEY;
DECLARE @AccountId INT;

DECLARE @action CHAR(8)
IF COLUMNS_UPDATED() <> 0 -- delete or update?
BEGIN
IF EXISTS (SELECT * FROM deleted) -- updated cols + old rows means action=update
SET @action = 'UPDATE'
ELSE
SET @action = 'INSERT' -- updated columns and nothing deleted means action=insert
END
ELSE -- delete
BEGIN
SET @action = 'DELETE'
END
------------------------------------------------------------------------
DECLARE @Debit MONEY, @Credit MONEY;

IF (@action = 'INSERT')
BEGIN
SELECT @AccountId = AccountId FROM INSERTED;
SELECT @Debit = Debit FROM INSERTED;
SELECT @Credit = Credit FROM INSERTED;

IF (@Debit > 0)
BEGIN
SET @Amount = @Debit;
END
ELSE
BEGIN
SET @Amount = -@Credit;
END
END
ELSE IF (@action = 'DELETE')
BEGIN
SELECT @AccountId = AccountId FROM DELETED;
SELECT @Debit = Debit FROM DELETED;
SELECT @Credit = Credit FROM DELETED;

IF (@Debit > 0)
BEGIN
SET @Amount = -@Debit;
END
ELSE
BEGIN
SET @Amount = -@Credit;
END
END
ELSE IF (@action = 'UPDATE')
BEGIN
SELECT @AccountId = AccountId FROM INSERTED;
SELECT @Debit = Debit FROM INSERTED;
SELECT @Credit = Credit FROM INSERTED;

DECLARE @DebitDeleted MONEY, @CreditDeleted MONEY;
SELECT @DebitDeleted = Debit FROM DELETED;
SELECT @CreditDeleted = Credit FROM DELETED;

SET @Debit = @Debit - @DebitDeleted;
SET @Credit = @Credit - @CreditDeleted;
SET @Amount = @Debit - @Credit;
END

EXEC usp_UpdateAccountBalance @AccountId, @Amount;

Post #1339690
Posted Friday, August 3, 2012 6:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 3, 2012 10:13 PM
Points: 3, Visits: 10
Hi, using your help I come up with the following solution for the trigger:

	IF (@action = 'INSERT')
BEGIN

-- update existing balances
UPDATE dbo.AccountBalance
SET Balance = Balance + i.Debit - i.Credit
FROM AccountBalance b, INSERTED i
WHERE b.AccountId = i.AccountId;

-- insert new balances
INSERT INTO dbo.AccountBalance (AccountId, Balance )
SELECT ins.AccountId, ins.Debit - ins.Credit AS Balance
FROM INSERTED ins, (
SELECT AccountId, Debit - Credit AS Balance
FROM INSERTED
WHERE AccountId NOT IN (
SELECT b.AccountId FROM dbo.AccountBalance b
INNER JOIN INSERTED i
ON b.AccountId = i.AccountId
)
) n
WHERE ins.AccountId = n.AccountId
END
ELSE IF (@action = 'DELETE')
BEGIN
UPDATE dbo.AccountBalance
SET Balance = Balance - d.Debit + d.Credit
FROM AccountBalance b, DELETED d
WHERE b.AccountId = d.AccountId;
END
ELSE IF (@action = 'UPDATE')
BEGIN
UPDATE dbo.AccountBalance
SET Balance = Balance + i.Debit - d.Debit - i.Credit + d.Credit
FROM AccountBalance b, INSERTED i, DELETED d
WHERE b.AccountId = i.AccountId AND b.AccountId = d.AccountId;
END
Post #1339782
Posted Friday, August 3, 2012 9:58 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 12:34 PM
Points: 31,181, Visits: 15,626
That looks better. It's what you want in a trigger, a way to handle multiple rows.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1339948
Posted Sunday, August 5, 2012 5:53 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 1:27 PM
Points: 1,945, Visits: 3,068
From a freshman accounting course (I was awful at it), I seem to recall that Journals never have deletions, just correcting entries. And that the account balances are computed in a VIEW, not materialized in another physical table.

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1340316
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse