SQL Server - Problem with recursive call of trigger.

Asked By inf

27-Jul-07 02:32 PM

Hi.
I have a problem with UPDATE trigger and SQL 2005, when trigger calls
update of the table. The sample environment, table 'test' with
trigger:

create table webdata..test(col int, help int)
go

insert into webdata..test values (1,1)
go

CREATE TRIGGER [ud_test] ON  webdata..test AFTER UPDATE  AS
BEGIN
IF NOT UPDATE(help)
exec xp_cmdshell 'SQLCMD.EXE -Q "update webdata..test set
col=1,help=1 where col=1"'

END
GO

update webdata..test  set col=1  where col=1
Go

The problem: Trigger calls update statement on the same table. This
construction works without problems in older SQL version, but update
in SQL 2005 locks the record in table and the SQLCMD.EXE timeouts.

The one solution is an Asynchronous call of the command line. But I
need solution for this case.

Thank you.

27-Jul-07 02:57 PM

That should not work in prior releases, since you shell out and this gets a new connection. SQL
Server doesn't understand the relationship between the "mother" connection and the "child"
connection.

Unless it isn't the same row you are updating, of course. If it isn't the same row, then you might
see different level of locks between the versions (for various reasons, but lets not get into this
unless this actually is the case).

Can you post a runnable repro that *does* work in an earlier release? I could fire up my 2000 VPC,
but I want to be certain that I first have something to execute that does work.

You might want to investigate sp_getbindtoken. But this is deprecated, and also you will have to
pass that token to the child process somehow.

I should also add that as the script is written the while xp_cmdshell and SQLCMD is not necessary,
but I assume you know that and your case is more convoluted.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

29-Jul-07 07:33 PM

(info@pstruh.cz) writes:

Pray tell, why are you not updating the table directly in the trigger?

As Tibor said, the above is asking for trouble. There is a fair chance
that the statement that fired the trigger will block the rows the
other process needs to access.

Also, keep in mind that any security-aware DBA may flatly refuse to
enable xp_cmdshell.

I think it's back to the drawing board for a redesign.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx