Trigger : How Do You Get Row Values 1

[pcawdron]

I need to build a trigger that passes values from the row being updated, inserted or deleted to a function(that runs beautifully) to update a denormalised running balance column. How do I pass row values in my trigger to my update SQL? Consider the following table....

Date Dealer Amount Balance
1/1/02 5 $500 $1000
2/2/02 5 $500 $1500
3/3/02 5 $500 $2000

If the user changes the amount for 2/2/02 I want to run an update that effects the balance for every date>= 2/2/02. I've got the logic right, but how do I pick up the changed row values? (Any ideas on what to do if they delete a row? It'll have to be the reverse?)

Here's my Trigger.... (inserted. is a place holder for my elusive row values)

create trigger updateSmartCashBalance
on dbo.__SmartCash
for INSERT, UPDATE, DELETE
as
/*if rows 3,4,5 are updated then... */
IF (COLUMNS_UPDATED() & 14) > 0
begin
update
dbo.__SmartCash
set balance = dbo.____Running_Balance(Inserted.Dealer, Inserted.SmartCashID, Inserted.Transaction_Date)
where
Inserted.Dealer = dbo.__SmartCash.Dealer and
Inserted.SmartCashID >= dbo.__SmartCash.SmartCashID and
Inserted.Transaction_Date >= dbo.__SmartCash.Transaction_Date
end

 

[rt63]

Assuming you change only the amount field and only one record is update/inserted/deleted at a time, this could work for you.


create trigger <trigger_name>
on <your table>
for insert,update,delete
as
begin
set nocount on
declare @dt smalldatetime , @dealer smallint , @diff int , @blnc int, @amnt int

if exists (select * from inserted) and not update(amnt)
return

if exists (select * from inserted)
select @diff = amnt from inserted
if exists (select * from deleted)
select @diff = isnull(@diff,0) - amnt from deleted

if exists (select * from inserted)
begin
select @dt = dt from inserted
select @dealer = dealer from inserted
select @amnt = amnt from inserted
end
else
begin
select @dt = dt from deleted
select @dealer = dealer from deleted
end

select @blnc = sum(amnt) from <your table> where dealer = @dealer and dt < @dt

update <your table>
set blnc = isnull(@blnc,0) + isnull(@amnt,0)
where dealer = @dealer
and dt = @dt

update <your table>
set blnc = blnc + @diff
where dealer = @dealer
and dt > @dt

end



This will not require any function, but if in a row even dealer code/date could change then you will need to change some code accordingly.



RT

 

[pcawdron]

Excellent....

Thank you very much for taking the time to code this in detail for me. With only a few minor modifications I was able to use this, but more than that, I was also able to understand it so I can use the same concepts elsewhere.

Thanks,
Peter