About TRIGGER in any table:
- Does TRIGGER lock (LOCK) the table until the end of its execution?
- If not, when should I block it?
- How do I block this?
About TRIGGER in any table:
Yes, lock exists until the transition is finished.
Triggers work in transactions (implicit or not) and, while are open, will block resources. The lock will remain in until the transaction is confirmed (with COMMIT) or rejected (with a ROLLBACK). The more a trigger is executed, the greater the probability of another process being blocked. Therefore, the triggers should be written in a way to reduce their duration whenever possible.
"Triggers are used to perform tasks related to validations, access restrictions, security routines and data consistency, so these controls are no longer executed by the application and are executed by Triggers"
A trigger is a special type of stored procedure that runs automatically when an event occurs on the database server.
All DML (INSERT, UPDATE, OR DELETE) transactions in the database follow a flow of transactions that we do not see, but which the database managed automatically performs:
If, however, there is still doubt about how Trigger works and if you want to make sure that the table should be locked at any access during the trigger execution place it inside a BeginTransation.
Example: Below will be triggered when someone tries to include or change data in the Titles table. It sends a message to the user. (50009 is a message defined for the user in sysmessages.)
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'reminder' AND type = 'TR')
DROP TRIGGER reminder
GO
CREATE TRIGGER reminder
ON titles
FOR INSERT, UPDATE
AS RAISERROR (50009, 16, 10)
GO