1
I have a table that receives data all the time, almost every second. This table has a Trigger that after inserting a new row inserts this record in another table.
I need to include a new Rigger in the same table and I have some questions:
1 - When I try to change the existing Rigger or create a new Rigger, everything stops (the table stops receiving records and consequently Rigger does not write in the other table), and it takes a long time for Rigger’s change to be made, like 5 minutes. Why is that? Is there any way to do it more correctly, without crashing anything or taking time to run?
2 - Is there any way to get the error log from the moment it hangs everything so I know what is occurring?
NOTE: I have tried to insert a new Rigger or add my code to existing Rigger, both take time to run and both lock the table.
I am willing to raise the issue with more information if necessary.
You are trying to insert a new Rigger or change an existing one?
– Reginaldo Rigo
I have tried both ways, both take time to run and both lock the table.
– Joao Paulo
If Trigger is used a lot it can be "leased" the application "loca" the table ?
– Motta
Yes, it is very used, and should be locking the table. And just this point that I do not know. How to prevent this lock.
– Joao Paulo
@Joaopaulo: Your question is typical of "SQL Server Internals"...
– José Diz
Unfortunately, in sql server you cannot create a disabled Trigger. There is no time when the table stops receiving this information every second?
– Renato Afonso
You will need to temporarily remove the application from the air. Try to put the database in 'single user' to make this change and then return the bank status.
– Rodolpho Sa