Exec order of triggers calling stored procedures

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)

Exec order of triggers calling stored procedures

Author

Topic

Naigewron
Starting Member

6 Posts

Posted - 2009-04-03 : 04:30:06

Hi!

I have a question I'm having a hard time finding a definite answer to, so I turn to you:

We have a trigger-based data synchronisation service running on MS SQL Server 2000 and 2005. The trigger activates on inserts and updates, and then calls one or more stored procedures to do the actual synchronising.

Now, we're doing some redesign, and we're wondering: When a trigger calls a stored procedure, will the trigger halt execution until the stored procedure exits, or may we come across cases where the trigger will continue running alongside the procedure?

All our common programming sense, tests and profiling indicate that the trigger will wait for the procedure to complete its run, but we need to be certain, so I was wondering if any of you could provide us with a definite answer to this. This is a completely autonomous service running on our clients' servers, so we'd really like to be certain before installing a "new and improved(?)" version

And a followup question, somewhat related: If the procedure called from the trigger fails and crashes, all our testing points to the trigger still continuing its execution (ie: The procedure crash does not crash the trigger). Is this correct?

Thank you in advance!
Eskil

Naigewron
Starting Member

6 Posts

Posted - 2009-04-11 : 18:14:43

Noone knows this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-12 : 02:36:12

The remote stored procedure call is synchronous, which means trigger is not done until remote procedure call is finished.

E 12°55'05.63"
N 56°04'39.26"

Naigewron
Starting Member

6 Posts

Posted - 2009-04-12 : 04:29:39

Thank you :)

dsindo
Starting Member

45 Posts

Posted - 2009-04-14 : 20:05:08

I think you should execute the stored procedure to run asynchronously so the trigger will not have to wait for
then store procedure to finish.

ALTER PROCEDURE [dbo].[spExecuteAsync]
@proc varchar(100) = 'spExecuteRetailProDailyETL'
AS
set nocount on
declare @rc int
declare @object int
declare @src varchar(255)
declare @desc varchar(255)
declare @runproc varchar(1000)

SET @runproc = 'osql -E -d [stored procedure name] -S [Server name] -Q"' + @proc + '"'

exec @rc = sp_oacreate 'wscript.shell', @object out
if @rc <> 0
begin
exec sp_oageterrorinfo @object, @src out, @desc out
select hr=convert(varbinary(4),@rc),
source=@src,
description=@desc
return
end
exec @rc=sp_oamethod @object,
'run',
null,
@runproc

if @rc <> 0
begin
exec sp_oageterrorinfo @object, @src out, @desc out
select hr=convert(varbinary(4),@rc),
source=@src,
description=@desc
return
end
exec sp_oadestroy @object
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

dsindo
Starting Member

45 Posts

Posted - 2009-04-14 : 20:08:43

This one is more like it.

ALTER PROCEDURE [dbo].[spExecuteAsync]
@proc varchar(100)
AS
set nocount on
declare @rc int
declare @object int
declare @src varchar(255)
declare @desc varchar(255)
declare @runproc varchar(1000)

SET @runproc = 'osql -E -d [Database Name] -S [Server name] -Q"' + @proc + '"'

exec @rc = sp_oacreate 'wscript.shell', @object out
if @rc <> 0
begin
exec sp_oageterrorinfo @object, @src out, @desc out
select hr=convert(varbinary(4),@rc),
source=@src,
description=@desc
return
end
exec @rc=sp_oamethod @object,
'run',
null,
@runproc

if @rc <> 0
begin
exec sp_oageterrorinfo @object, @src out, @desc out
select hr=convert(varbinary(4),@rc),
source=@src,
description=@desc
return
end
exec sp_oadestroy @object
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources