Trigger automatically blocks the table (LOCK TABLE)?

Asked

Viewed 415 times

1

About TRIGGER in any table:

  • To TRIGGER blocks (LOCK) the table until the end of its execution?
    • If not, when should I block it out?
    • How do I do that blockade?
sql-server trigger

  • If you’re using "Commit and Rollback," yes.. Inside Begintransation you can get your table locked up waiting to finish your entire transaction,

  • 1

    @Daniellearrudatorres So if I’m not using COMMIT e ROLLBACK, and not force the LOCK, then doesn’t lock?

  • The Trigger is characterized by being a counter where the competition problem is perfectly manageable. Based on it, it would theoretically not be possible for two competing users to achieve the same value.

  • I did not understand your answer, because it seems that it is possible, and then not. Anyway I’ll explain better why of the doubt: I have an ERP system that does all BD manipulation. So I need a Rigger who will always check one UPDATE in a table, and change the record if it is in condition. The problem is that it intermittently causes errors. Hence the idea of blocking the table while running Rigger. But this is only an assumption.

2 answers

1


Yes there is the lock until the transition is over.

Triggers work in transactions (implicit or not) and, while are open, will block resources. The lock will remain on 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, triggers must be written in such a way as to reduce their duration whenever possible.

in documentation officer you can read more about

  • Hudsonph, would you be able to post the documentation that says that? So there is a complete answer to search, and there is also no problem of the link coming out of the air. (besides that they can signal it because it is basically like a comment).

  • yeah, I’ll edit it now,

-1

"Triggers are used to perform tasks related to validations, access restrictions, security routines and data consistency ; in this way these controls cease to be 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 stream of transactions that we do not see but which the database managed runs automatically:

If even so, there is still doubt about how Trigger works and whether you want to be sure that the table should be locked to any access during the Rigger run put 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 user-defined message 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

Browser other questions tagged sql-server trigger

You are not signed in. Login or sign up in order to post.