Updating without triggers: several questions

[mercwrought]

Hi all, I have some questions and a scenario that I need some help understanding.


I have several tables that are continuously updated by several interfaces. I need to lock those tables then perform several tractions without the triggers firing.
Here is the code I am about to use.

…..
BEGIN TRAN
ALTER TABLE t1 DISABLE TRIGGER All
delete from t1 where idnumber in (@idnumberto,@idnumberfrom)
insert into t1 select * from #t1
ALTER TABLE t1 ENABLE TRIGGER All
COMMIT TRAN
……..

My questions.

1. Will the begin and commit Tran lock out the table, so that no other inserts can occur while the triggers are off? I also need to be able to read from the table while it is locked out.
2. Only the outer most Begin and Commit count?
3. I’m also having a problem with the fact that the replication is getting in the way of this transaction. I think I have a solution for that but I have not got there yet. I would appreciate any suggestions.


Thx in advance for any input.

 

[NoCoolHandle]

Merc..

I think that

1. will impact any user that tries to connect after the tranaction starts. (as it is disabling the trigger that it a mod will effect - and there will be a big schema lock on the table.)

2. No Any commit matchs the begin..
Therefore each begin tran needs to have a matching commit. However any ROLLBACK rolls back all Transactions

3. Replication will get in your way because the schema on the remote system will be effected by any modifications that should be made on those systems.

You will probably need to reinitialize all subscrivers, but no real experience there ....