January 25, 2008 at 10:16 am
I have two table 'A' and 'B' and I have "after update" trigger on both of these tables. Trigger on table 'A' updates table 'B' and trigger on table 'B' update table 'A'. I have stored procedure to update Table 'A' in order to avoid recursive I have disabled the trigger of table 'B' and then update Table 'A' and then enable the trigger of Table 'B' in that storeprocedure.
My Question is ,
1) Will the trigger of table 'B' be executed while I am updating the Table 'A'? Does this avoid recursive action ?
2) Alter table inside a procedure
2a) does this affect the performance of the storeprocedure?
2b) Does this affect the DTC transaction ?
Thanks in advance.
January 25, 2008 at 10:42 am
Here are my two cents.
1) Will the trigger of table 'B' be executed while I am updating the Table 'A'? Does this avoid recursive action ?
It depends which column fires the trigger.
2) Alter table inside a procedure
2a) does this affect the performance of the storeprocedure?
2b) Does this affect the DTC transaction ?
If you are talking about altering the scripts in your stored procedure, it will not affect. Trigger is always a performance killer. Do not use it unless you have to.
January 25, 2008 at 12:48 pm
Create procedure prcA
(
......
)
as
Begin
.......
.......
Alter table a
disable trigger atrigger
update a
set column1 = '200-525'
where .....
Alter table a
enable trigger atrigger
end
Question
1) will this really disable the trigger and enable it because I was able to see the trigger using profiler?
2)Is there a specific rights to be given to use alter table in a storeprocedure.
Thanks
Stephen Sunil
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply