Trigger/Update insterted row with sum values
-
Hi.
I have two tables in SQL Server 7.0.
Table One (Primary index is Customer, Date, RefNo)
Customer
Date
RefNo
TotalValue
Table Two (Primary index is Customer, Date, RefNo, AutoSeqNumber)
Customer
Date
RefNo
AutoSeqNumber
Value
At the time that the table one row is inserted, the corresponding rows in table two already exist (changing the order of these events is not an option
). So what I need to do is have a trigger that fires on the insert of a row into table one, sums the Values in table two for the matching customer, date, refno index and put the result into the ‘TotalValue’ row in Table One.Any help greatly appreciated.
-
Huh, Primary Index? You mean Clustered Index!
You have mentioned "So what I need to do is have a trigger that fires on the insert of a row into table one, sums the Values in table two for the matching customer, date, refno index and put the result into the ‘TotalValue’ row in Table One." However, Index? Index is not a column in your Tables!
Could we clarify the above?
-
Thanks for replying. By Primary I mean unique.
To clarify:
1). the unique index in table one is customer, date, refno.
2). the unique index in table two is customer, date, refno, AutoSeqNumber
Lets say table 2 contains the following rows:
- Jones,02/02/03,4567,50
- Smith,02/02/03,1234,1,50
- Smith,02/02/03,1234,2,100
If I insert a row into table one with the values "smith,02/02/03,1234,0", I want to interogate table two for entries that match "smith,02/02/03,1234", sum the values and update TotalValue in table one with the sum. In this case, I want to update the TotalValue for "smith,02/02/03,1234" in table one with 150.
Thanks, Jim
-
I haven't unit tested this but give it a go!
CREATE TRIGGER SumValue ON XYZ1
FOR INSERT
AS
DECLARE @CustomerID INTEGER
DECLARE @Date DATETIME
DECLARE @TotalValue INTEGER
DECLARE @CustomerID2 INTEGER
DECLARE @Date2 DATETIME
DECLARE @Value INTEGER
SELECT @CustomerID = CustomerID, @Date = [Date], @TotalValue = TotalValue
FROM XYZ1 A INNER JOIN Inserted I ON A.CustomerID = I.CustomerID
SELECT @CustomerID2 = CustomerID, @Date2 = [Date], @Value = Value
FROM XYZ2
WHERE XYZ2.CustomerID = @CustomerID
IF @CustomerID2 = @CustomerID AND @Date2 = @Date AND @Value = @TotalValue
BEGIN
SELECT CustomerID, SUM(Value) AS 'Total Value'
FROM XYZ2
WHERE XYZ2.CustomerID = @CustomerID2
GROUP BY CustomerID
END
-
How about this
-
-
set nocount on create table table1( Customer varchar(10) not null, [Date] datetime not null, RefNo int not null, TotalValue int null, constraint pk_customer1 primary key (Customer, [Date], RefNo) )
-
create table table2( [Customer] varchar(10) not null, [Date] datetime not null, RefNo int not null, [AutoSeqNumber] int not null, Value int not null, constraint pk_customer2 primary key ([Customer], [Date], RefNo, AutoSeqNumber) ) go
-
create trigger table1_ins on table1 for insert as update t1 set TotalValue = s.TotalValue from table1 t1 inner join (select t2.Customer, t2.[Date], t2.RefNo, Sum(t2.Value) as TotalValue from table2 t2 inner join inserted i on t2.Customer = i.Customer and t2.[Date] = i.[Date] and t2.RefNo = i.RefNo group by t2.Customer, t2.[Date], t2.RefNo) s on t1.Customer = s.Customer and t1.[Date] = s.[Date] and t1.RefNo = s.RefNo go
-
insert table2 values('Jones','02-feb-2003',4567,1,50) insert table2 values('Smith','02-feb-2003',1234,1,50) insert table2 values('Smith','02-feb-2003',1234,2,100) -
insert table1 values('Smith','02-feb-2003',1234,0) -
select * from table1
-
drop table table1 drop table table2
Output:
Customer Date RefNo TotalValue ---------- ------------------------------------------------------ ----------- ----------- Smith 2003-02-02 00:00:00.000 1234 150
--
George -
-
The trigger fires once even if 0, 1 or many records are affected. Using local variables is not good - please, no cursor in a trigger - in this case because you assume one record has been affected. If this must be the case, then an error should be raised before data corruption occurs.
DECLARE @Rowcount int
SET @Rowcount = @@ROWCOUNT
IF @Rowcount = 0 RETURN -- nothing to do (e.g., update ... where 1=0)
IF @Rowcount > 1 BEGIN
RAISERROR('Sorry, the trigger is unable to handle more than one record in a single update. Kick your programmer - softly because of their great error handling.', 16, 1)
ROLLBACK
RETURN
END
etc
Randy
Helpdesk: Perhaps Im not the only one that does not know what you are doing. 😉 -
rstone,
hope you're referring to MrSql post, not mine - my version should work for 0, 1 or 10000 rows being inserted (with minor modifications it will work for updates as well). We were bitten really bad with multi-row updates and I learned my lesson
cheers,
--
George -
georgedo,
I was indeed. Your trigger, without local variables and using group by, is a clear sign you are multirow aware. And the group by is a clear sign you know what you are doing. Experience, even bad, can be good.
What the developers often don't realize, as they curse you under their breath, is that any trigger that is not multirow ready limits a DBAs ability to quickly save their butts without a full restore from backup. It's not easy to fix mistakes one record at a time.
Also, a good trigger might be used as a utility if applied to the entire table. Its logic might be adapted using the original tables (not inserted or deleted) to create a query to redo the calculations on all records in a batch - should it be required due to a logic change or corruption.
Randy
Randy
Helpdesk: Perhaps Im not the only one that does not know what you are doing. 😉 -
MrSQL,
average trigger it is not but trickiest part, IMO, is not multirow update - it's the fact that aggregates cannot be used as update values hence the subselect with grouping.
Mind you, just looked at the code I wrote - if this trigger is created for update as well as insert then you'll have, as rstone has mentioned, a powerful update utility. You can issue statement like
update table1 set customer = customer
and it will in fact recalculate all TotalValues. Just don't try it with 1000000 rows
Cheers
--
George -
I would start with georgedo's solution. You can test the guts of the query on the entire table. Replace the inserted table with the original trigger table.
select -- perhaps a top 100 here
t2.Customer,
t2.[Date],
t2.RefNo,
Sum(t2.Value) as TotalValue
from
table2 t2 inner join table1 i on
t2.Customer = i.Customer
and t2.[Date] = i.[Date]
and t2.RefNo = i.RefNo
group by
t2.Customer,
t2.[Date],
t2.RefNo
If the result look good, then the "utility" would be
update t1
set TotalValue = s.TotalValue
from
table1 t1 inner join
(select
t2.Customer,
t2.[Date],
t2.RefNo,
Sum(t2.Value) as TotalValue
from
table2 t2 inner join table1 i on
t2.Customer = i.Customer
and t2.[Date] = i.[Date]
and t2.RefNo = i.RefNo
group by
t2.Customer,
t2.[Date],
t2.RefNo) s on
t1.Customer = s.Customer
and t1.[Date] = s.[Date]
and t1.RefNo = s.RefNo
You can wrap this in a begin tran - rollback and use selects, during the tran, to check results. Don't do this in production. The same begin tran - rollback can be used to test the trigger too. The utility script is also a good place to see if performance is a problem.
If your utility works, then the trigger will also, provided you remember to used the inserted or deleted table where needed. Do not update the whole table in the trigger! If you start to see deadlocks, look for this.
Randy
Helpdesk: Perhaps Im not the only one that does not know what you are doing. 😉 -
The select portion of the query used is the trigger is often the difficult part. It can be developed and tested in QA before the trigger is even started.
If the same set of keys can be in the inserted table in multiple records, then a distinct set derived from the inserted table might be required first to prevent getting too many records in the join. This is not the case here since the keys are unique in table1. This is not the case if you need this sort of trigger on table2. In that case, select a distinct set of keys for table1 from inserted (table2) first.
Randy
Helpdesk: Perhaps Im not the only one that does not know what you are doing. 😉 -
Thanks everyone for your help. I've implemented Georgedo's solution and it works fine.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply