Trigger automatically locks the table (LOCK TABLE)?

0

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?
asked by anonymous 27.08.2018 / 15:29

2 answers

1

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.

in official documentation you can read more about it

    
31.10.2018 / 14:23
0

"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
    
27.08.2018 / 16:14