|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Trigger to fire only after a commit.Hi Experts,
I would like to know if there is a way to have triggers that fire only after a commit. If not, is there a workaround that will give me the same affect? Avi Avi
BEGIN TRAN INSERT INTO tbl1...... UPDATE tbl2 ..... COMMIT TRAN So, you want two triggers fire after commit transaction? I assume that both tables have trigger. Show quote "Avi" <m@m.com> wrote in message news:fi3ei7$dlq$1@news2.netvision.net.il... > Hi Experts, > > > > I would like to know if there is a way to have triggers that fire only > after a commit. If not, is there a workaround that will give me the same > affect? > > > > Avi > > > > > > On Nov 22, 10:30 am, "Avi" <m***@m.com> wrote: Triggers will fire after the DML statement that was issued will run.> Hi Experts, > > I would like to know if there is a way to have triggers that fire only after > a commit. If not, is there a workaround that will give me the same affect? > > Avi It will not wait to see if the transaction was committed or rolled back. There is no way to modify this behavior and I don't think that there is a workaround that will give you the same affect. Adi > I would like to know if there is a way to have triggers that fire only A trigger always runs in the context of the transaction that caused the > after a commit. If not, is there a workaround that will give me the same > affect? trigger to fire. Perhaps you can explain the problem you are trying to solve so someone can suggest a solution. -- Show quoteHope this helps. Dan Guzman SQL Server MVP "Avi" <m@m.com> wrote in message news:fi3ei7$dlq$1@news2.netvision.net.il... > Hi Experts, > > > > I would like to know if there is a way to have triggers that fire only > after a commit. If not, is there a workaround that will give me the same > affect? > > > > Avi > > > > > > Hi all,
Thanks for the replies. I will elaborate some more. I have a trigger on table Table1 that inside that trigger I update Table2. Once I do an insert/update (within a transaction) on Table1 the trigger is fired and updates Table2. Problem 1 Since I did not yet commit my changes to table1, I keep both Table1 and Table2 locked. If the trigger could be fire only when a commit is issued Table2 will not be locked for the duration of the transaction on Table1 Problem 2 Once I committed my changes to Table1, my changes to Table2 are also committed. However, the datetime that was inserted to Table2 is the time that the trigger was fired, but not the time the data was committed to Table1. A more correct time for me is the time the changes were committed. Thanks, Avi Show quote "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message news:1D3A5336-7C10-4920-9D8C-9042DEE76289@microsoft.com... >> I would like to know if there is a way to have triggers that fire only >> after a commit. If not, is there a workaround that will give me the same >> affect? > > A trigger always runs in the context of the transaction that caused the > trigger to fire. Perhaps you can explain the problem you are trying to > solve so someone can suggest a solution. > > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > > "Avi" <m@m.com> wrote in message > news:fi3ei7$dlq$1@news2.netvision.net.il... >> Hi Experts, >> >> >> >> I would like to know if there is a way to have triggers that fire only >> after a commit. If not, is there a workaround that will give me the same >> affect? >> >> >> >> Avi >> >> >> >> >> >> >
Show quote
"Avi" <m@m.com> wrote in message news:fic0rd$18v$1@news2.netvision.net.il... The point of a trigger is that it happens before commit. It seems like you > Hi all, > > > > Thanks for the replies. I will elaborate some more. I have a trigger on > table Table1 that inside that trigger I update Table2. Once I do an > insert/update (within a transaction) on Table1 the trigger is fired and > updates Table2. > > > > Problem 1 > > Since I did not yet commit my changes to table1, I keep both Table1 and > Table2 locked. If the trigger could be fire only when a commit is issued > Table2 will not be locked for the duration of the transaction on Table1 > > > > Problem 2 > > Once I committed my changes to Table1, my changes to Table2 are also > committed. However, the datetime that was inserted to Table2 is the time > that the trigger was fired, but not the time the data was committed to > Table1. A more correct time for me is the time the changes were > committed. > > > > > > Thanks, > > Avi > don't want a trigger at all. Do: BEGIN TRAN Update Table1 COMMIT TRAN Update Table2 -- David Portas
Show quote
> Problem 1 Only uncommitted data should be locked, not the entire table. Are you > > Since I did not yet commit my changes to table1, I keep both Table1 and > Table2 locked. If the trigger could be fire only when a commit is issued > Table2 will not be locked for the duration of the transaction on Table1 > > Problem 2 > > Once I committed my changes to Table1, my changes to Table2 are also > committed. However, the datetime that was inserted to Table2 is the time > that the trigger was fired, but not the time the data was committed to > Table1. A more correct time for me is the time the changes were > committed. > keeping the transaction open for an extended period of time? -- Show quoteHope this helps. Dan Guzman SQL Server MVP "Avi" <m@m.com> wrote in message news:fic0rd$18v$1@news2.netvision.net.il... > Hi all, > > > > Thanks for the replies. I will elaborate some more. I have a trigger on > table Table1 that inside that trigger I update Table2. Once I do an > insert/update (within a transaction) on Table1 the trigger is fired and > updates Table2. > > > > Problem 1 > > Since I did not yet commit my changes to table1, I keep both Table1 and > Table2 locked. If the trigger could be fire only when a commit is issued > Table2 will not be locked for the duration of the transaction on Table1 > > > > Problem 2 > > Once I committed my changes to Table1, my changes to Table2 are also > committed. However, the datetime that was inserted to Table2 is the time > that the trigger was fired, but not the time the data was committed to > Table1. A more correct time for me is the time the changes were > committed. > > > > > > Thanks, > > Avi > > > > "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message > news:1D3A5336-7C10-4920-9D8C-9042DEE76289@microsoft.com... >>> I would like to know if there is a way to have triggers that fire only >>> after a commit. If not, is there a workaround that will give me the >>> same affect? >> >> A trigger always runs in the context of the transaction that caused the >> trigger to fire. Perhaps you can explain the problem you are trying to >> solve so someone can suggest a solution. >> >> >> -- >> Hope this helps. >> >> Dan Guzman >> SQL Server MVP >> >> "Avi" <m@m.com> wrote in message >> news:fi3ei7$dlq$1@news2.netvision.net.il... >>> Hi Experts, >>> >>> >>> >>> I would like to know if there is a way to have triggers that fire only >>> after a commit. If not, is there a workaround that will give me the >>> same affect? >>> >>> >>> >>> Avi >>> >>> >>> >>> >>> >>> >> > >  |
|||||||||||||||||||||||
Other interesting topics