Stored Procedure from Trigger

[dabdo555]

I have an update trigger for a view in my database. I would like to have the trigger execute a stored procedure when it runs. The trigger basically updates two tables with the inserted data. The stored procedure calculates a formula by receiving the primary key from the inserted table and then updates some fields for that row. This is the line i use to execute the procedure...

exec pCalcFinancial @key

Well, ive tested the stored procedure by itself and it works but not when i apply it to the view. What can i do about this?

 

[SQLSister]

You can execute a stored procedure from a trigger. One concern with doing this is that stored procedures are often written to handle only one record at a time and triggers must account for multiple records being inserted, updated or deleted.

Without seeing your whole code, it is hard to say what is wrong, what error message are you getting or are you getting incorrect or no data inserted into these tables?

 

[dabdo555]

My stored procedure looks like this...

ALTER procedure pCalcFinancial(@prop_num int)
as
declare @x int, @y money
select @y= isnull([Acq Price],0) from tblPIW_DB1
where tblPIW_DB1.prop_num = @prop_num

update tblPIW_DB1
SET [Acq Price]=1235 where tblPIW_DB1.prop_num = @prop_num

select @y= [Acq Price] from tblPIW_DB1 where tblPIW_DB1.prop_num = @prop_num
print str(@y)

This procedure will eventually take multiple fields from both tables in my view and calculate a formula that stores multiple outputs to different fields in the database. (Basically resulting in real time data calculation) I definitely what this to work for multiple row updates. Thats definitely something i have to understand and learn how to do. is there a way???

 

[SQLSister]

"This procedure will eventually take multiple fields from both tables in my view and calculate a formula that stores multiple outputs to different fields in the database."

Not sure what you mean by this. Could you give me some sample data and the results you want?

If you want to return the variable you have in your stored procedure to the trigger, then the print command won't do that. It might even be causing a problem as a trigger has no where to print to. Use a select statement instead.

As to how to handle multiple records, you usually do this with a join (in the case of a trigger you often join the the table you are adjusting to the inserted or deleted pseudotable)

Lets take a for example. I am inserting data in table2 and I want this to trigger an update in table1 (which is a table containing sales summaries). I might write the code this way in a trigger.

UPDATE Table1
SET ytd_sales = TAble1.ytd_sales + inserted.qty
FROM table1, inserted
WHERE table1.customerID = inserted.customerID

Of course this particular scenario assumes that there is already a relationship between the two tables such that you cannot insert a record into table 2 unless the customer ID already exists in table 1. If there was no realtionship established, then you would need to have an if statement of somekind to do the update if the ID existed or insert it if it did not. Hope this at least gives you some ideas as to how to structure what you are doing.

 

[dabdo555]

The print statement was only for testing purposes.

Here's some sample data and what will happen to it....

-user updates a record
-view's update trigger fires and basically states...

ALTER TRIGGER InsUpTrg_vwPIWDB on dbo.[PIW DB]
INSTEAD OF Update
AS
BEGIN

UPDATE dbo.[tblPIW_DB1] SET
[Address] = i.[Address],
--updates more fields
from inserted i
WHERE dbo.[tblPIW_DB1].[prop_num] = i.[prop_num]

UPDATE dbo.[tblPIW_DB2] SET
[Payment Term] = i.[Payment Term],
--updates more fields
WHERE dbo.[tblPIW_DB1].[prop_num] = i.[prop_num]

declare @prop_num int
select @prop_num=i.[prop_num] from inserted i
exec pCalcFinancial @prop_num

END

At the end i execute my stored procedure where it takes in the key and then calculates some output for fields.
I thought that the trigger would execute for every row that is updated. Im guessing thats not true when you update more than one row at a time. *** I did move this code to one of the individual table's triggers and it works. But i would perfer for the view to handle it.