How to use the same table at the multi-trans? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How to use the same table at the multi-trans?

Author

Topic

alucado
Starting Member

4 Posts

Posted - 2008-09-22 : 03:28:08

I have a trigger and store procedure.

pls see below code.

Code Snippet
create trigger utr_a on tableA

after update as

begin

select * into dbo.tmp from inserted

exec usp_b

end

Go

create procedure usp_b as

select * from dbo.tmp

Go

Because table dbo.tmp had been updated at the trigger utr_a, table dbo.tmp need be used at the procedure usp_b before trigger utr_a was be commited.

system will block the one trans.

how to solve it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 03:35:49

why are you using select * into inside trigger? this will cause an error when subsequent updates happen?

alucado
Starting Member

4 Posts

Posted - 2008-09-22 : 04:20:54

Because I want to export inserted data to external file. for example, txt or xml.
And exec sp_xpcmdshell "bcp ****" can't be supported inside trigger.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 04:54:46

quote:
Originally posted by alucado

Because I want to export inserted data to external file. for example, txt or xml.
And exec sp_xpcmdshell "bcp ****" can't be supported inside trigger.

ok. even then you could simply use insert... select to insert into tmp. why create table each time?

alucado
Starting Member

4 Posts

Posted - 2008-09-22 : 09:27:43

I think the main point is not select * into or insert into...select to.

I want to run two transactions at the one trigger.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 09:30:34

quote:
Originally posted by alucado

I think the main point is not select * into or insert into...select to.

I want to run two transactions at the one trigger.

ok. even in that case what i told is worth looking at. For first update it will work fine. for subsequent updates it will fail as it again tries to create table which already exists.
I cant understand which two transactions you're refering to.i cant see any explicit transactions inside trigger code.

alucado
Starting Member

4 Posts

Posted - 2008-09-22 : 10:00:37

OK, I paste my sql code.

CREATE TRIGGER UTR_ExportPL ON sdbc1..OR033100
AFTER UPDATE AS

IF UPDATE(or03013) AND EXISTS(SELECT 1 FROM inserted WHERE or03013=2) AND EXISTS(SELECT 1 FROM deleted WHERE or03013<>2)
BEGIN

INSERT into tmp_insert
SELECT OR01001,OR03051,OR01004,SL01002,C.OR04003,OR01013,OR01018,OR01072,OR03001,OR03019,OR03046,OR03005,OR03002,OR03006,
OR03011 FROM inserted
LEFT JOIN OR013100 ON OR03001=OR01001
LEFT JOIN SL013100 ON OR01004=SL01001
LEFT JOIN (
SELECT OR04001,OR04003 FROM OR043100 RIGHT OUTER JOIN or013100 ON OR04001=OR01001 WHERE OR04003 IS NOT NULL
AND or04001=(SELECT TOP (1) or03001 FROM inserted)
UNION ALL
SELECT A.OR01001,B.SL14004 FROM (
SELECT OR01001,OR01004 FROM OR043100 RIGHT OUTER JOIN or013100 ON OR04001=OR01001 WHERE OR04003 IS NULL
AND or01001=(SELECT TOP (1) or03001 FROM inserted)
) A
LEFT JOIN (SELECT * FROM SL143100 WHERE SL14002='00') B ON A.OR01004=B.SL14001
) as C
ON OR01001=C.OR04001

--export result of query to txt file.
declare @date varchar(8),@filename varchar(30),@sql varchar(2000)
set @filename='c:\PL'+convert(char(8),getdate(),112)+'.txt'
SET @sql='bcp "select * from SDBC1.dbo.tmp_insert " queryout'+' '+@filename+' '+'-c -t \t -SCTCNL066 -Uscca -Pscca'
EXEC master..xp_cmdshell @sql
DELETE FROM SDBC1.dbo.tmp_insert
end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources