Updating TableA with TableB line totals Help

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Updating TableA with TableB line totals Help

Author

Topic

KingCarlos
Yak Posting Veteran

74 Posts

Posted - 2012-08-30 : 19:18:51

If I can get some advice on my SQL Trigger at the bottom then that would be awesome, I have the following scenario.

Table1 with the fields JobNo and TotalCost

And

Table2 with the fields JobNo, LineNumber and LineCost

Here is some example data

Table1
JobNo = 1 TotalCost = ?
JobNo = 2 TotalCost = ?
JobNo = 3 TotalCost = ?

Table2
JobNo = 1 LineNumber = 1 LineCost = 100
JobNo = 2 LineNumber = 1 LineCost = 100
JobNo = 2 LineNumber = 2 LineCost = 150
JobNo = 3 LineNumber = 1 LineCost = 100
JobNo = 3 LineNumber = 2 LineCost = 200
JobNo = 3 LineNumber = 3 LineCost = 125

How can I script this so that TableA is updated as follows

JobNo = 1 TotalCost = 100
JobNo = 2 TotalCost = 250
JobNo = 3 TotalCost = 425

So far I have got

select sum (linecost) as total from Table2
group by JobNo

This gives me the totals, but I now need to script this into a trigger to update the total line cost from TableB into the field TotalCost in TableA

Here is my attempt

CREATE trigger [dbo].[x_trigger1] on [dbo].[TableB]
for INSERT, UPDATE
as
Begin tran
update TableA
set TotalCost = (select sum (linecost) from TableB
group by JonNo where TableA exists (select TableA.JobNo = TableB.JobNo))
end

Thanks

KingCarlos
Yak Posting Veteran

74 Posts

Posted - 2012-08-30 : 19:40:09

I have managed to create a view which I then run the trigger from is there anyway to bypass the view and directly update TableA?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-30 : 19:40:44

If I understood what you are trying to do, you need the trigger on DELETE as well. It would be something like this - I have not tested the code other than to see if it compiles:

CREATE TRIGGER [dbo].[x_trigger1]
ON [dbo].[TableB]
FOR  INSERT, UPDATE, DELETE
AS
;WITH cte1 AS
(
	SELECT JobId FROM INSERTED
	UNION
	SELECT JobId FROM DELETED
),
cte2 AS 
(
	SELECT
		c.JobId,
		SUM(b.linecost) AS TotalCost
	FROM
		TableB b
		INNER JOIN cte1 c ON c.JobId = b.JobId
	GROUP BY
		c.JobId
)
MERGE TableA a
USING cte2 c ON c.JobId = a.JobId
WHEN MATCHED THEN
	UPDATE SET a.totalcost = c.totalcost
WHEN NOT MATCHED BY TARGET THEN 
	INSERT (JobId, TotalCost)
	VALUES (c.JobId, c.TotalCost)
WHEN NOT MATCHED BY SOURCE THEN
	DELETE;
GO

Instead of having a table updated constantly, you could create a view with the sums. However, it is a trade-off. If the table is small but frequently updated, a view would be better. If the table is large and frequently queried, a trigger would be better.

KingCarlos
Yak Posting Veteran

74 Posts

Posted - 2012-08-31 : 02:02:19

Thanks

arpana patil
Starting Member

24 Posts

Posted - 2012-09-04 : 01:40:48

update t set totalcost=poi.tc
from dbo.Table_1 t inner join
(
select jobno,SUM(LineCost) as tc from dbo.Table_2
group by jobno
)poi on t.jobno=poi.jobno

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources