August 2, 2012 at 12:04 pm
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.
August 2, 2012 at 12:24 pm
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.
August 3, 2012 at 2:49 am
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;
August 3, 2012 at 6:43 am
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
August 3, 2012 at 9:58 am
That looks better. It's what you want in a trigger, a way to handle multiple rows.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply