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.
| Author |
Topic |
|
nsteiner
Starting Member
13 Posts |
 Posted - 2003-01-28 : 17:04:26
|
| Here's the situation:I have a 3rd party replication app replicating data from AS400 to SQL Server 7. The AS400 table is the source, we'll call it table A.The SQL 7 table is the target,we'll call it table B.On SQL 7, there is another table that is involved somewhat based on what happens on replication. This table will be table C.Replication from A=>B is real-time, for all actions - INSERT, UPDATE, DELETE.Table B contains an update trigger. This trigger checks 3 columns within the table to see if they were updated. If so, it updates corresponding data in table C. Table C has an udpate trigger that sends an email when certain fields are updated via replication (determined based on USER_NAME). So, A=>B, B=>C, C sends email.I really want the update trigger to fire after the replication update transaction has committed. I came across a situation where the replication transaction failed, and a rollback was issued. However, the update trigger on table b fired, then updated table c, which fired its trigger sending an email.What I need is to be able to fire the trigger on table b after a successful replication transaction.Is there any way to have a trigger for after the replication update transaction has succeeded?My other plan was to have table B insert the record from inserted into another table, we'll say table D. That covers the issue of rollbacks; if a rollback was called, then no record would be in the table. I would set up a monitoring service that would update table C based on info in table D, then delete the record from D. Got it? ;)I am praying for the after update trigger solution, though.Thanks,Nate==================================================Forced into becoming a SQL Guru |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-01-28 : 18:34:00
|
| Fraid not.A trigger is always part of the transaction otherwise integrity would be compromised.I would advise your table for emails in any situation anyway so that an email failure doesn't stop the system working.It is usual to do this by changing all calls to xp_sendmail to something like spSendMail (maybe in it's own database) and creting spSendMail to insert into a table. Your job can then look at that table to create the emails. The table also gives you a record of what has been sent. It also means you can restrict access to xp_sendmail.It's usually pretty simple to implement depending on the requirements.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-01-28 : 18:44:13
|
| You might want to look at this article too.Bottom line, create an "email queue" instead of a trigger to send the email.http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=23374Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
nsteiner
Starting Member
13 Posts |
Posted - 2003-01-29 : 08:39:56
|
| Thanks, Michael P. I did see that article prior to my post, but I was curious if there was an answer.nr - thanks for the information. I appreciate it.I will report the results, duck the arrows, and move on. ;)==================================================Forced into becoming a SQL Guru |
|
|
|
|
|
|
|
|
UPDATE trigger