Is it safe to alter a trigger on a live database?

Question

I have a Microsoft SQL Server with a table. There is a trigger that fires if a row is inserted into the table. The system is live, as in that rows are constantly inserted into the table and the trigger fires all the time.

I want to alter the trigger. The trigger is the bottleneck of the system, and there is a very high chance that it is being executed/has started a transaction when issuing the query to alter it. Is it save to alter the trigger in this situation? What is the worst thing that could happen? I assume that an in-process trigger will finish with the old logic, and future executions will use the new logic, but because the system is in production (with no good way to test it) i need to be certain.

It would be possible to stop the process that is inserting rows into the table, but id rather not interrupt it if it is not necessary.

Answer 1

It is safe to alter the trigger while the table is being used. In-flight transactions will use the current trigger logic, new transactions will use the new trigger logic after it's applied.

However, altering the trigger will require a schema lock on the table, which will mean that you could cause blocking while trying to alter the trigger. I would definitely advocate for trying to find as idle a moment as possible before trying to alter the trigger, in order to minimize the blocking impact.