stored procedure to calculate data

[folberlin]

hi,

i have an application that feeds data into a table of a database - table
name is XTABLE716C6DC3.

now i need a stored procedure that takes and calculates data from that dable
anytime the table is changed and store the results in a certain format into
a new table - lets call that table XTABLEOVERVIEW

the calculations i need to be done are the following:

totals for all entries sorted by date

- sum of the column FLT_REVENUE_E48E42AD sorted by date which is stored in
column DT_STARTDATE_318FA6EE

code should look somewhat like this:

SELECT
DT_STARTDATE_318FA6EE,SUM(CONVERT(DECIMAL(10,2),FLT_REVENUE_E48E42AD)) FROM
dbo.XTABLE716C6DC3
GROUP BY DT_STARTDATE_318FA6EE
ORDER BY DT_STARTDATE_318FA6EE
GO

as we have the total now i need to split the sum up into different portfolios stated in column STR_BRANCH_BB618FB1 for the dates. one of the portfolios e.g. is RETAIL

summary: i need the stored procedure to calculate data and then write
results in another table as soon as the innitial table gets changed.
calculations i need are totals of the mentioned columns listed by date for
the whole group and then the split ups for the single portfolios.

thanks for any help offered

[adam_k]

If you need the sproc to fire every time the table gets updated, why don't you use a trigger?
As for the single portfolios, add STR_BRANCH_BB618FB1 to your select and group by.

PS: Good luck remembering table and field names.

[folberlin]

can you show your idea in code? im not really into sql yet.
table and field names are no problem as the external app will do the job. oh and whenever tabel1 gets updated the sproc will update table2. so only two tables in total.
thanks a lot for the comments so far...

[folberlin]

noone?

[urtrivedi]

http://msdn.microsoft.com/en-us/library/ms189799.aspx

[adam_k]

Any questions after reading the MSDN article that urtivedi posted?

[folberlin]

i did create my trigger and it works. however, where and how can i edit the code if needed? so far i cant see where the process is saved on the mssql server. it is not under stored procedures. any comments?

[debasisdas]

i did create my trigger and it works.

lets see the code.

[folberlin]

CREATE TRIGGER [dbo].[tr_GetSum]
   ON  [dbo].[XTABLE716C6DC3]
   AFTER INSERT, UPDATE, DELETE
AS 
BEGIN
	SET NOCOUNT ON;

	TRUNCATE TABLE [XTABLEOVERVIEW]


	INSERT INTO [XTABLEOVERVIEW]
	SELECT

[debasisdas]

From where you got this code ?
Why trigger will be stored as a procedure ?

Trigger is not a process. It is stored as an object in the database and is executed / fired when the triggering event occurs in the database.

[folberlin]

ok where is it stored and where can i make adjustments if needed

[debasisdas]

It is stored as a compiled object inside database.

Since you have the code you can recompile the same with changes and the trigger will be ready.

[folberlin]

if changes are done to the code and run again it returns the error
There is already an object named 'tr_GetSUM' in the database.

[folberlin]

ok nevermind, problem solved, found the trigger in the db

[adam_k]

How many records are you hanlding with this code?
Why truncate and then insert all the data back into the XTABLEOVERVIEW ?
Do you change all records at once?

When working with triggers 2 "special" tables are available. INSERTED and DELETED. They are visible only inside the trigger and they hold the data either INSERTED or DELETED. When data are updated both INSERTED and DELETED will hold data (DELETED will show the data before the update and INSERTED after the update).
If you are not updating all records it might be wise to use these 2 tables to update XTABLEOVERVIEW appropriately instead of truncating and inserting everything back.

[folberlin]

@adam
that exactly i have just figured. the trigger code i have right now does the job but slows down the database extremely. we are talking roughly 10000 entries of which only very few will be updated and need new calculation results in XTABLEOVERVIEW.
i have read about the two temporary tables INSERTED and DELETED but do not know how to use them properly. so any support is greatly appreciated!

@debasisdas
thats why i have not closed the threat yet

[adam_k]

Like I said, inside the trigger you have these 2 tables that show the changes to your data. They will have the same fields as the table with the trigger.

You can

select * from INSERTED

and get the new records or

select * from DELETED

and get the deleted ones.

When working with updates you need to check which record from the deleted got new values (which can be found in the INSERTED). It is pretty easy if you have the necessary keys (and don't update them i.e. a datetime field.)
If you don't really care or if you don't have proper keys, then you can just delete from XTABLEOVERVIEW the records that exist in DELETED and insert all records from INSERTED.

About the delay: Triggers tend to do that. Even if you create a trigger to do next to nothing you'll get a small delay. That's why you want the trigger to fire only when needed. If you don't care for deletes or updates then don't create the trigger to fire after DELETE, UPDATE. keep it firing only when you need to.
The fact that you were truncating the table and then inserting everything back in can only be disasterous in a high volume/high traffic/large size table.

You always have to keep resources in mind when writing code.

[folberlin]

when trying to make use of these two tables i get the following error:
Invalid object name 'INSERTED'.

@adam - could i pm you?

[adam_k]

You could, but this wouldn't help future readers.
Please post your new code here.

PS: I hope you are not trying to verify your query outside of the trigger.