udpate trigger

Please post your code

CREATE TRIGGER SetFlag ON Table2
AFTER INSERT
AS

UPDATE A
SET [SentFlag] = 1
FROM Table1 A INNER JOIN Inserted B
  ON A.ID = B.ID
GO

here's the code, hilaire - i'm thinking it's just updating the sentflag upon insert into table1.  i don't want it changed until records have been written to table2.  

CREATE trigger trg_recon ON working FOR INSERT, UPDATE
AS

UPDATE a
SET sent = 1
FROM working a inner join inserted b on a.sequencenumber=b.sequencenumber

hi rafran - the sent flag is only in table1 - your creation is on table2.  can i change that?

The trigger has to be on Table2 and it will update the send flag on Table1.

aaah....
i should have known that
gimme a sec

I'd do it in the update trigger of table1(working) to avoid triggers calling each other recursively

CREATE trigger trg_recon ON working FOR INSERT, UPDATE
AS
-- copy records to table2
insert into table2(<columns....>) select <columns ...> from inserted
-- update the flag once done
UPDATE a
SET sent = 1
FROM working a inner join inserted b on a.sequencenumber=b.sequencenumber

oh, rafran, what about other unrelated inserts into table2?

alright, now i'm puzzled.  hilaire, the insertion stmts in your trigger - are you saying i need that in the trigger?  

>> oh, rafran, what about other unrelated inserts into table2? <<

What do you mean by "unrelated inserts".  Are you talking about inserts to table2 that did not come from table1?

yes, exactly

If they did not come from table1, I am assuming that they will have a different sequence number.  If this is the case, then nothing will be updated in table1.

Aaah I thought you were looking for an automatic way to transfer the data from table1 to table2 upon insert/update in table1.
I might have misunderstood...

If you transfer the data manually, it's a whole different issue, and rafrancisco's code (trigger only on table2) might be more relevant, with a little tweaking know whether the current operation on table2 is a "transfer" or not

if a discrepancy occurs, i use a recon proc to find the data.  when the data is found, it is written to table1 - the working table.  if after review, it is determined that we actually want to write the data elsewhere, (get rid of the discrepancy) the records are transferred from table1 into table2.  

table1 (working) is only written to if/when there is a discrepancy and i've performed reconciliation.
table2 is written to at all times by a number of other apps.  

rafran, the trigger will not otherwise impact table2?

>> the trigger will not otherwise impact table2? <<

It will not impact table 2 in the sense that it will not do anything to it.  The updates are being performed on table 1.

beauty.  let me try, i'll be back in a sec

well, let me paint a bigger pix.  i've got a 'main' db that is used to fill discrepancies out in many other varying db's.  if a discrepancy is found, the records are pulled from the 'main' db's trade table and transferred to the trade table in any/all of the other databases where the discrepancy exists.  so, working and the source table are in the 'main' db.  i want to update the sent flag in the main.dbo.working table when i transfer to any other db.  but, if the trigger is on the trade table in the 'main' db, but i'm transferring data into another database.dbo.trade table, does this trigger mean anything to me?  

if so, great, i will use it.  if not, all i want to do is update the sent flag to 1 after the transfer cuz i don't want those records included in subsequent transferrs. you know what i mean?

so rafran, w/the bigger pix, am i still ok to use the after insert trigger?

The trigger should be in the trade table on the other databases, not in the main db.

other db's on other servers --- still ok?

CREATE TRIGGER SetFlag ON Table2
AFTER INSERT
AS

UPDATE A
SET [SentFlag] = 1
FROM Table1 A INNER JOIN Inserted B
  ON A.ID = B.ID
GO

>> other db's on other servers --- still ok? <<

Yes, as long as you specify the full name.

CREATE TRIGGER SetFlag ON Table2
AFTER INSERT
AS

UPDATE A
SET [SentFlag] = 1
FROM [ServerName].[DatabaseName].[dbo].Table1 A INNER JOIN Inserted B
  ON A.ID = B.ID
GO

well, i might have the trigger in place, but now the insert is failing w/this:

Server: Msg 7395, Level 16, State 2, Procedure tr_sentflag, Line 5
Unable to start a nested transaction for OLE DB provider 'SQLOLEDB'.  A nested transaction was required because the XACT_ABORT option was set to OFF.
[OLE/DB provider returned message: Cannot start more transactions on this session.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionLocal::StartTransaction returned 0x8004d013:  ISOLEVEL=4096].

Try this:

CREATE TRIGGER SetFlag ON Table2
AFTER INSERT
AS

SET XACT_ABORT ON ---- I added this line

UPDATE A
SET [SentFlag] = 1
FROM [ServerName].[DatabaseName].[dbo].Table1 A INNER JOIN Inserted B
  ON A.ID = B.ID
GO

actually, it's the trigger that's issuing that error msg?  see :  Server: Msg 7395, Level 16, State 2, Procedure tr_sentflag, Line 5
that's the trigger i just created

CREATE TRIGGER tr_sentflag ON Trading.dbo.Trade
AFTER INSERT
AS

UPDATE A
SET Sent = 1
FROM sqlserver2.db2.dbo.working A INNER JOIN Trade B
  ON A.sequencenumber = B.sequencenumber
GO

well, the trigger isn't working, no errors.  it's just not working.
the sent flag remains a 0, i run the proc again, it finds more discrepancies and then it transferrs everything --- including the ones it got on previous transfer, and then i've got duplicates

do you think there's an easier way to get the sent flag so they're excluded from subsequent transferrs?

Why not do it manually right after issuing the INSERT statement:

INSERT INTO Table2....
SELECT ... FROM Table1
WHERE ...

UPDATE table1
set SentFlag = 1
WHERE ....

Just make sure that the WHERE clause are the same.

yep, that's what i was just doing.  i don't know how else to handle it, i just need to make sure once transferred, they're excluded from future xferrs.  and i can't delete them from the working table cuz we need to keep this around indefinitely to be able to research the source/cause of the discrepancies.

what do you think?

SET @SQLStmt = 'INSERT INTO '+@destinationsrv+'.'+@destinationdb+'.dbo.Trade
(traderid,accountid,prevorderno,destination,symbol,buysell,lastfillquantity,lastfillprice,timeofexecution,clearingfee,liquidityflag,client,ordernumber,
origorderno,basketid,basketnumber,quantity,executedquantity,leavesquantity,ordertype,limitprice,avgfillprice,orderstatus,stopprice,timeinforce,
cntrpartyID)
   SELECT DISTINCT traderid,accountid,prevorderno,destination,symbol,buysell,lastfillquantity,lastfillprice,timeofexecution,clearingfee,liquidityflag,
   client,ordernumber,origorderno,basketid,basketnumber,quantity,executedquantity,leavesquantity,ordertype,limitprice,avgfillprice,orderstatus,
   stopprice,timeinforce,cntrpartyID
   FROM '+@sourcesrv+'.'+@sourcedb+'.dbo.Working w
   WHERE sent = 0 and timeofexecution >= left(getdate()-0,11)
   AND destination = ''' + @destination +'''
   AND EXISTS
  (SELECT 1 FROM '+@sourcesrv+'.'+@sourcedb+'.dbo.sequence s WHERE s.sequencenumber=w.sequencenumber)'

EXEC (@SQLStmt)
GO

Looks ok.  Then you can just update the Sent flag to 1 using the same WHERE clause.

same where clause, but:

Server: Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near 'w'.

SET @SQLStmt = 'INSERT INTO '+@destinationsrv+'.'+@destinationdb+'.dbo.Trade
(traderid,accountid,prevorderno,destination,symbol,buysell,lastfillquantity,lastfillprice,timeofexecution,clearingfee,liquidityflag,client,ordernumber,
origorderno,basketid,basketnumber,quantity,executedquantity,leavesquantity,ordertype,limitprice,avgfillprice,orderstatus,stopprice,timeinforce,
cntrpartyID)
   SELECT DISTINCT traderid,accountid,prevorderno,destination,symbol,buysell,lastfillquantity,lastfillprice,timeofexecution,clearingfee,liquidityflag,
   client,ordernumber,origorderno,basketid,basketnumber,quantity,executedquantity,leavesquantity,ordertype,limitprice,avgfillprice,orderstatus,
   stopprice,timeinforce,cntrpartyID
   FROM '+@sourcesrv+'.'+@sourcedb+'.dbo.Working w
   WHERE sent = 0 and timeofexecution >= left(getdate()-0,11)
   AND destination = ''' + @destination +'''
   AND EXISTS
  (SELECT 1 FROM '+@sourcesrv+'.'+@sourcedb+'.dbo.sequence s WHERE s.sequencenumber=w.sequencenumber)'

SET @SQLStmt2 = 'UPDATE  '+@sourcesrv+'.'+@sourcedb+'.dbo.Working w
SET sent = 1  WHERE sent = 0 and timeofexecution >= left(getdate()-0,11)
AND destination = ''' + @destination +'''
AND EXISTS
(SELECT 1 FROM '+@sourcesrv+'.'+@sourcedb+'.dbo.sequence s WHERE s.sequencenumber=w.sequencenumber)'

EXEC (@SQLStmt + @SQLStmt2)
GO

this alright?

SET @SQLStmt2 = 'UPDATE  '+@sourcesrv+'.'+@sourcedb+'.dbo.Working
SET sent = 1  WHERE sent = 0 and timeofexecution >= left(getdate()-0,11)
AND destination = ''' + @destination +'''
AND EXISTS
(SELECT 1 FROM '+@sourcesrv+'.'+@sourcedb+'.dbo.sequence s WHERE s.sequencenumber=sequencenumber)'

EXEC (@SQLStmt + @SQLStmt2)
GO