January 26, 2009 at 5:12 am
Hi,
Can't find an answer anywhere, so apologies if I have missed a resolution somewhere.
I have a Production database that needs some data from one table copying into another database on the same server.
The business want a trigger to fire whenever a new row is inserted into TableA in DatabaseA to insert a new row into TableB in DatabaseB with some of the data.
However, if the trigger fails the original insert MUST still COMMIT and NOT ROLLBACK.
I have tried a number of methods using an AFTER trigger, but if I offline DatabaseB and the trigger fails, the original insert rolls back.
HELP!!
Many thanks in advance.
MDBA
PS My Oracle colleagues are ribbing me something fierce as Oracle has AFTER COMMIT triggers to do exactly this!
January 26, 2009 at 5:35 am
As far as I know with triggers you can not rollback part of the trigger. You have rollback the whole trigger or commit the whole of the trigger. If you have an error, then the server automatically roles back the entire trigger for you. I have to admit that it sounds like a good scenario for replication. Is there any reason that you don’t use replication?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 26, 2009 at 5:50 am
Hi Adi,
Thanks for the reply.
We have considered replication, but it seems a bit overkill for 4 fields in one table.
But ultimately, it may have to be the replication way.
M.
January 26, 2009 at 6:03 am
You can use service broker to break the second insert out of your transaction. Here is a pretty good article for using service broker for auditing. It should get you started.
http://www.sqlteam.com/article/centralized-asynchronous-auditing-with-service-broker
Service broker looks confusing, but it really is not too bad. It is worth learning - I have found a lot of great uses for it.
January 26, 2009 at 6:13 am
Thanks Michael.
Looks very interesting however our Production database is SQL Server 2000 with no signs of upgrading!
Sorry, should have mentioned it before.
Could I upgrade to 2005 and run it in 2000 compatibility mode with the broker?
M.
January 26, 2009 at 6:24 am
No, you need to be running in a 2005 compatibility mode. Your best option will probably be transactional replication. If you configure replication with it's defaults, it creates stored procedures for inserts/updates/deletes. You can easily go in and modify the procedures to have complete control over the destination schema, so it may be a good option.
By default, replication sends one row at a time, so if you do a nice big set-based update to your table, it will replicate a bunch of individual updates (or deletes and inserts - see deferred updates). Keep this in mind when designing.
And you are posting in the wrong forum - this is a 2005 forum. Not to mention it is 2009 - it's time to upgrade the SQL 2000 databases to 2008.
January 26, 2009 at 6:50 am
Thanks to all.
Transactional replication it will be.
I suppose it best I keep quiet about our 6.5 Production databases then!!!
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply