Call sproc from trigger question

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 2005 Forums

Transact-SQL (2005)

Call sproc from trigger question

Author

Topic

Chainwhip
Starting Member

33 Posts

Posted - 2008-06-18 : 16:23:19

Hi. Apologies if this has come up before, I wonder if someone can help.

I want to call a sproc from a trigger, passing in the value of a field from the updated/inserted/deleted row.

It would be like spUpdateUser @UserID = x

where x is the value from one of the columns of updated row.

What is the syntax for this please? TIA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-18 : 16:30:58

The trigger tables (inserted and deleted) could contain multiple rows, so you can not call your stored procedure without looping through the table. Can the logic of spUpdateUser be changed to handle multiple rows?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Chainwhip
Starting Member

33 Posts

Posted - 2008-06-18 : 16:37:49

thanks tara, I didn't realise there were trigger tables involved - it's something i haven't done before. what would they contain multiple rows of? The sproc being called will be updating a value in a different table altogether by calling a function, but it needs the UserID from the row that has just been altered.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-18 : 16:44:38

The trigger tables contain the data that was modified. If multiple rows were modified, then the trigger tables would contain multiple rows.

I blogged a little about this multiple row issue here: http://weblogs.sqlteam.com/tarad/archive/2004/09/14/2077.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Chainwhip
Starting Member

33 Posts

Posted - 2008-06-18 : 17:02:37

ah gotcha! i may be updating or deleting 3 rows in the triggered table so i need to call the sproc 3 times, one for each row affected. i think therefore i'll need to create a cursor to do this, as the sproc being called needs to be run for each row - unless there's a clever way of wrapping the call in a 'set-based solution'?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-18 : 17:06:13

The clever way is to not call a stored procedure for each row, instead rewrite the logic to handle multiple rows.

Could you post the code for the stored procedure so that we can see if the logic can be rewritten into the trigger set-based?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Chainwhip
Starting Member

33 Posts

Posted - 2008-06-18 : 17:14:04

Sure, the sproc to be called is this. The function it calls does some calculations on other tables to return an int representing progress

ALTER PROCEDURE [dbo].[sp_Cache_UserProgress]

@UserID INT
AS
BEGIN

SET NOCOUNT ON;

DECLARE @Progress INT

SELECT @Progress = Progress FROM dbo.func_GetProgress_User(NULL, NULL, @UserID, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 8, NULL, NULL)

IF NOT EXISTS(SELECT UserID FROM Cache_UserProgress WHERE UserID = @UserID)
INSERT Cache_UserProgress (UserID, Progress, datCache)
VALUES (@UserID,
@Progress,
GETDATE())
ELSE
UPDATE Cache_UserProgress
SET Progress = @Progress, datCache=GETDATE()
WHERE UserID = @UserID
END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-18 : 17:17:19

We'd need to rewrite the function as well, but here's a start on your trigger for the UPDATE portion of your stored procedure. So you'd do this instead of calling a stored procedure.

UPDATE c
SET Progress = @Progress, datCache = GETDATE()
FROM Cache_UserProgress c
INNER JOIN inserted i
ON c.UserID = i.UserID

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Chainwhip
Starting Member

33 Posts

Posted - 2008-06-18 : 17:25:16

The function is very long and complicated so I don't want to change it - it will mean days of retesting the application!

In your opinion will there be any significant 'punishment' by using a cursor in the trigger? we're getting about 10 inserts/updates every minute at peak time

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources