SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Loop Deleted trigger table


Loop Deleted trigger table

Author
Message
tkhabit
tkhabit
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 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.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: Administrators
Points: 63097 Visits: 19113
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
My Blog: www.voiceofthedba.com
tkhabit
tkhabit
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 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;


tkhabit
tkhabit
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 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

Steve Jones
Steve Jones
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: Administrators
Points: 63097 Visits: 19113
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
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search