calling sp from trigger - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

calling sp from trigger

Author

Topic

edpel
Starting Member

22 Posts

Posted - 2004-05-10 : 15:24:07

Can I call a sp from a trigger and within the trigger access the inserted table? I am trying to do that now and am getting errors so I was wondering if could even be done.

Thanks,
Eddie

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-10 : 15:26:12

If you post the code that might help...

What does the sproc do?

Brett

8-)

edpel
Starting Member

22 Posts

Posted - 2004-05-10 : 15:45:46

I have to write some scoring triggers. The client enters a bunch of data and the trigger does all the calculations and pushes the data into another table. These have to fire on an insert and update so I thought I would hold all the processing logic in one sp and then have each trigger call it. Many of the triggers share some of the calculation logic, that is why I wanted to centralize some of the code.

For my test purposes, I just have:

Trigger:

BEGIN
DECLARE @uniqueid int
,@form int
select @form=1
select @uniqueid= uniqueid from inserted
exec mysp @form,@uniqueid

End

SP:

Create PROCEDURE mysp(@form int,@uniqueid int)
as

BEGIN

select * from inserted where uniqueid_c=@uniqueid

END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-10 : 15:48:13

The stored procedure will not be able to see the inserted table. It exists in the trigger only. Why do you need to do the calculations in a trigger? Can't you do it from the application?

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-10 : 17:00:43

Will a temp table be in the same process?

In the TRIGGER

SELECT * INTO #Temp FROM inserted

The reference the #temp...

But I don't think I'd go that route....

You're doing derivation of data...does it have to be immediate?

I'd have the sproc do it...and if your still worried, kick off a batch process overnight...

But I'd have all data access through sprocs, so it wouldn't be an issue....

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources