Posted by danceli on 01/31/07 21:39

I have a trigger 'trgTblUpdate' working on the table 'Tbl'. When the
table 'Tbl' got inserted/updated data, the tigger is fired and then we
use a batch file with bcp command to unload those new inserted/updated
data to another server. But in bcp out file, it didn't get all new
inserted/updated data, there are some missing data. I am not sure if I
explain it clearly. Could you help me out of this problem???? I am
stuggling for that these two days............Any help is appreciated.

1. code for trigger:

CREATE TABLE [TblUpdate] (
[id] [int] NOT NULL ,
[eventid] [int] NOT NULL ,
[sequenceid] [int] NOT NULL ,
[UpdatePass] [int] NULL
) ON [PRIMARY]
GO
create trigger trgTblUpdate on Tbl For Insert,Update as
insert into TblUpdate(id,eventid,sequenceid) select ins.id,
ins.eventid,ins.sequenceid from inserted ins

2. bcp code in the Batch file:
isql -U <user> -P <pw> -S <server> -Q "update AA..TblUpdate set
UpdatePass=1 where UpdatePass is null"

bcp "select a.* from AA..Tbl a, AA..TblUpdate b where
a.eventid=b.eventid and a.sequenceid=b.sequenceid and b.eventid<>-1
and b.sequenceid<>-1 and b.updatepass=1" queryout 30sec-%TFN_NOW%.wrk -
U <user> -P <pw> -S <server> -f Tbl.fmt

isql -U <user> -P <pw> -S <server> -Q "delete from AA..TblUpdate where
UpdatePass=1"

[Back to original message]