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 |
|
barny.spencer@openoffice.
Starting Member
3 Posts |
 Posted - 2008-01-14 : 22:40:11
|
| Hi.Can someone tell me if it is possible to execute a trigger after a transaction has been committed on a table, rather than after UPDATE or INSERT?I have a transaction that inserts a Person record, then inserts a record that associates the Person record to a PersonType record.The problem is, I need to access both the Person data and the PersonType data inside the trigger, but if the trigger is an AFTER INSERT trigger on the Person table it fires before the PersonType association record has been inserted and so it is not available.Cheers. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-01-14 : 23:25:41
|
| Cant you try writing an AFTER INSERT trigger on PersonType table and use the linking id to retrieve the recently inserted PERSON record. |
 |
|
|
barny.spencer@openoffice.
Starting Member
3 Posts |
Posted - 2008-01-14 : 23:50:14
|
| Nah, there are a few different interfaces throughout the system that are used to create Person records and PersonType is not always the last record in the transaction.I thought I might be able to add an AFTER INSERT trigger to the PersonType table or the last Table that is used in the transaction but this is an old system and although PersonType record is always created when a Person record is inserted I can't guarantee that I will have access to all the data from the transaction. There are about 15 tables in this transaction that relate to the Person record and I need to access all the data within the trigger.I might be able to find another common table far enough down in the transaction to put the trigger on but it means I will probably need to make application changes rather than just db.Cheers. |
|
|
|
vemkav
Starting Member
4 Posts |
Posted - 2010-02-11 : 02:50:40
|
| I have similar requirement? did you get any solution or workaround for this? can you please share it? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-11 : 06:41:04
|
| If you don't need real-time then use the trigger to flag the record and then have a batch process do the next part (and clear the flag).Triggers on other, "later", tables in the update could react to the "flag" (rather than needing a scheduled task) and thus still be part of the same transaction block. |
|
|
|
vemkav
Starting Member
4 Posts |
Posted - 2010-02-11 : 08:38:04
|
| I can't introduce in columns in db, for flags. :( Brief description of my problem is: In first db I have Table Entity(id, name) & Table USER(id, entityid, isadmin, phone, email, userid, pwd)In second db I have table Agency(id, name, adminphone, adminemail)Requirement: I need to synchronize second.agency data, as soon as data gets inserted in first dbinsertion of data in first db is done through our own persistence framework. with sequence:1. start transaction2. insert entity3. insert user4. commitI am writing AFTER INSERT trigger on USER Table, Calling stored procedure to insert Agency in second db, using user,entity of first db.I Expect that Entity is already persisted. so, I am trying to fetch entity name using user.entityid. but, it is returning me null as transaction is not commited. (my assumption)so .. I want call stored procedure to be triggered after commit on insert. any possibility? |
|
|
|
vemkav
Starting Member
4 Posts |
Posted - 2010-02-11 : 08:39:01
|
| I can't introduce new columns in db, for flags. :( Brief description of my problem is: In first db I have Table Entity(id, name) & Table USER(id, entityid, isadmin, phone, email, userid, pwd)In second db I have table Agency(id, name, adminphone, adminemail)Requirement: I need to synchronize second.agency data, as soon as data gets inserted in first dbinsertion of data in first db is done through our own persistence framework. with sequence:1. start transaction2. insert entity3. insert user4. commitI am writing AFTER INSERT trigger on USER Table, Calling stored procedure to insert Agency in second db, using user,entity of first db.I Expect that Entity is already persisted. so, I am trying to fetch entity name using user.entityid. but, it is returning me null as transaction is not commited. (my assumption)so .. I want call stored procedure to be triggered after commit on insert. any possibility? |
|
|
|
vemkav
Starting Member
4 Posts |
Posted - 2010-02-11 : 08:41:59
|
quote:
Originally posted by Kristen
If you don't need real-time then use the trigger to flag the record and then have a batch process do the next part (and clear the flag).Triggers on other, "later", tables in the update could react to the "flag" (rather than needing a scheduled task) and thus still be part of the same transaction block.
Kristen, I liked your idea ... but this is my real-time customer requirement. I didn't understand exactly what do you mean by your second point. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-11 : 08:55:15
|
"I can't introduce in columns in db, for flags. :("Common problem, that, of course - e.g. Vendor does not permit alterations (although it seems you are writing a Trigger, so you have some ability to change things  ). Sometimes that is solved by adding a "parallel-table" just for the PK + Flag (in a separate database if necessary ...)" I Expect that Entity is already persisted. so, I am trying to fetch entity name using user.entityid. but, it is returning me null as transaction is not commited. (my assumption)"Not quite correct, what you are seeing (I think) is:1. start transaction2a. insert entity2b. Fire any After-Trigger on entity3a. insert user3b. Fire any After-Trigger on user4. commitMy personal opinion is the logic is flawed. There should be referential integrity that an Entity cannot be created in a User does not exist. In SQL Server (dunno about other brands of SQL) the test for user-exists is performed at the time of the Entity create/edit, rather than at the time of the transaction commit.My second idea was that a trigger on User could check if there were any Entities (with Flags) and sort out the related user records for them. It is not straightforward as the "flag" on Entity has to be per-batch, and can't just be Yes/No, otherwise it would not be possible to have concurrent operations.An alernative might be to have a Queue instead:1. start transaction2a. insert entity2b. Trigger copies PKs to Queue3a. insert user3b. Check queue and update related User records4. commitDunno anything about it, but you might be able to use Service Broker for this (although that may only be in SQL2008) |
|
|
|
|
|
|
|
|
Execute trigger after commit of transation?