conversion of triggers into Stored procedure

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)

conversion of triggers into Stored procedure

Author

Topic

Rajeshkulkarni
Starting Member

5 Posts

Posted - 2004-10-05 : 10:05:52

I had Triggers and i have to convert that triggers into one Stored procedure how should i do ?
How should i access data in sp that is in inserted table when i use trigger ?

Rajesh

Rajesh

Jaap
Starting Member

19 Posts

Posted - 2004-10-05 : 10:38:52

I'm not sure but for I know you cannot use the inserted/Deleted tables out side of the trigger.

What you can do is make the trigger only for looping the inserted/deleted tables and execute a procedure with a paramater.

Excample
Your trigger now is

DECLARE @ID
DECLARE crs_Inserted FOR SELECT ID FROM INSERTED
DECLARE @crs_Inserted
SET @crs_Inserted = @crs_Inserted
OPEN @crs_Inserted
FETCH FROM @crs_Inserted
INTO @ID
WHILE @@FETCH_STATUS = 0
BEGIN
[Actions]
......
......
FETCH NEXT FROM @crs_Inserted
INTO @ID
END

Make of your Action an sp and execute this instead of the other linens

BEGIN
EXEC prc_New @ID
FETCH NEXT FROM @crs_Inserted
INTO @ID
END

Jaap

Rajeshkulkarni
Starting Member

5 Posts

Posted - 2004-10-07 : 02:14:43

quote:
Originally posted by Jaap

I'm not sure but for I know you cannot use the inserted/Deleted tables out side of the trigger.

What you can do is make the trigger only for looping the inserted/deleted tables and execute a procedure with a paramater.

Excample
Your trigger now is

DECLARE @ID
DECLARE crs_Inserted FOR SELECT ID FROM INSERTED
DECLARE @crs_Inserted
SET @crs_Inserted = @crs_Inserted
OPEN @crs_Inserted
FETCH FROM @crs_Inserted
INTO @ID
WHILE @@FETCH_STATUS = 0
BEGIN
[Actions]
......
......
FETCH NEXT FROM @crs_Inserted
INTO @ID
END

Make of your Action an sp and execute this instead of the other linens

BEGIN
EXEC prc_New @ID
FETCH NEXT FROM @crs_Inserted
INTO @ID
END

Thanks but i am not wants to use table inserted and deleted

Jaap

Rajesh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources