Why this trigger doesn't work in sql 2005

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)

Why this trigger doesn't work in sql 2005

Author

Topic

lyw
Starting Member

5 Posts

Posted - 2008-03-28 : 13:26:46

hi eveyrone,

I have an after insert trigger that works in sql2000 but not in sql2005. Can you please help me!!

CREATE trigger dbo.trUPS_tbl_I
on dbo.UPS_tbl
After Insert --For insert
as
declare @Tracking varchar(800),
@UPSID varchar(10),
@Cmmd varchar(800)

select @UPSID = cast(inserted.UPSID as varchar)
from inserted

commit

set @Cmmd = 'c:\Tasks\UpdTracking\UpdateTrackingCS ' + @UPSID + ' ' + 'UPS1'
EXEC master.dbo.xp_cmdshell @Cmmd

The UpdateTrackingCS program will call a stored procedure to get the inserted data and update other databases. And the reason to put the commit statement in sql2000 is to have sql commit the transaction, so the store procedure in UpdateTrackingCS can query thtat inserted records.

When I use the same code in SQL2005, no matter what I do the UpdateTrackingCS program cannot query the data by the UPSID. It always says record not found.

I have tried to change commit to begin trans ... commit trans. But nothing works. Please help!

Thanks in advance.

lyw

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-29 : 00:19:06

Tried trace it in profiler?

lyw
Starting Member

5 Posts

Posted - 2008-03-31 : 11:47:07

quote:
Originally posted by rmiao

Tried trace it in profiler?

No, how do i do that??

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2008-04-01 : 16:12:02

Tried reading Books on Line? (Index Tab, Type "Profiler" without the quotes. Choose "About SQL Server Profiler", then "Using SQL Server Profiler". Need more?)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-04-01 : 16:43:24

few reasons why you may want to re-design:

1 - your trigger code doesn't allow for and/or protect against multi-row inserts.
2 - it is a bad idea to launch an external process from a trigger
3 - sql server recommends not to COMMIT a transaction from within a trigger in versions later than 7.0 (see COMMIT in Books Online)

I did a little test in 2000 and repeated it in 2005:


create table junk (i int)
go
create trigger tr_i_junk on junk after insert
as
begin
	commit
	waitfor delay '00:00:10.000'
end
go

insert junk (i) values (10)

in both versions during the waitfor I was able to see my inserted value from another session.

However in 2005 I received this error:

quote:

Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

I don't know why your external process could not read the committed row but my guess is your application has swallowed this error. Check the eventlog and/or the errorlog.

Be One with the Optimizer
TG

lyw
Starting Member

5 Posts

Posted - 2008-04-02 : 11:54:48

Hi TG,

Thank you very much for your help. Yes, in 2005, that's exactly the error that I got - "The transaction ended in the trigger".

The reason that I am using the trigger to call external program is to have SQL insert transaction update the other database(Progress database) when a new record was created.

We use SQL server to link to our UPS shipping program to update the tracking number. By calling the external program in SQL after insert trigger, I was able to update two databases at the same time. It has been working great in SQL 2000. But in 2005 with MS's 'GREAT IMPROVEMENT', I was not able to use the same approach... Is there other recommendations to do stuff like this without having to commit the data inside the trigger?

Thanks again for everyone's help. I will look into the profiler also, I am a newbe in SQL, so thank you for bearing with me if I have asked the stupid quesiton...

Thanks,
lyw

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-04-02 : 13:29:03

can't you create linked servers and update the other database directly?
if it's on the same server you don't need lined servers at all.
if not you'll have to find different solution.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

lyw
Starting Member

5 Posts

Posted - 2008-04-02 : 13:37:31

No I cannot do direct link from my other server to UPS. It just doesn't work as good as SQL. I have found a work around by passing the data string to the other program instead of query again in the external program. But I was hoping to find a better solution than that.

Thanks
lyw

X002548
Not Just a Number

15586 Posts

Posted - 2008-04-02 : 13:38:03

"That doesn't sound good. Definitely Not Good" *

Triggers should have a very small footprint

Insert the info to a batch table, and then have a job work against that table

* Philoctetes

http://www.script-o-rama.com/movie_scripts/h/hercules-script-transcript-disney.html

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources