Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

udpate trigger

Posted on 2005-05-11
34
Medium Priority
?
275 Views
Last Modified: 2010-03-19
i'm transferring data from table1 to table2.  upon transfer, i'd like to update the 'sent flag' in table1 to '1', such that these records are excluded from subsequent transferrs.  thus far, my trigger is failing.  what's the right way to write that?
0
Comment
Question by:RLLewis
  • 17
  • 11
  • 3
31 Comments
 
LVL 26

Expert Comment

by:Hilaire
ID: 13978440
Please post your code
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13978450
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

0
 
LVL 1

Author Comment

by:RLLewis
ID: 13978494
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?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 28

Expert Comment

by:rafrancisco
ID: 13978513
The trigger has to be on Table2 and it will update the send flag on Table1.
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13978523
aaah....
i should have known that
gimme a sec
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 13978534
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
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13978535
oh, rafran, what about other unrelated inserts into table2?
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13978562
alright, now i'm puzzled.  hilaire, the insertion stmts in your trigger - are you saying i need that in the trigger?  
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13978589
>> 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?
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13978598
yes, exactly
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13978621
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.
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 13978629
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
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13978828
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?
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13978869
>> 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.
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13979212
beauty.  let me try, i'll be back in a sec
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13979456
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?
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13980070
so rafran, w/the bigger pix, am i still ok to use the after insert trigger?
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13980370
The trigger should be in the trade table on the other databases, not in the main db.
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13980393
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
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13980444
>> 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
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13980529
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].
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13980605
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
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13980608
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
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13981031
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?
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13981056
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.
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13981072
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.
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13981084
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
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13981097
Looks ok.  Then you can just update the Sent flag to 1 using the same WHERE clause.
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13981128
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
0
 
LVL 28

Accepted Solution

by:
rafrancisco earned 500 total points
ID: 13981141
SET @SQLStmt2 = 'UPDATE  w SET sent = 1  
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 + ' ' + @SQLStmt2)
GO
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13981143
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
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question