How to create a TRIGGER for an INSERT event without causing a lock in the Sqlserver table

Asked

Viewed 279 times

0

Hello, I am doing the integration between 2 solutions, where I need to detect the change of a table, and, based on the information that is sent I must manipulate another table. I don’t have access to the first solution to make this change herself, based on that premise I had to create a TRIGGER.

My problem is, when the TRIGGER is executed, she makes a lock in the table of the 1st solution which is preventing to put the 2nd solution in a production environment... Is there any way to create a TRIGGER that does not cause a lock, or some other resource that allows me to capture the information in the insertion in the bank and use them without preventing the first solution to work?

sql-server trigger
  • 2

    If you do the query who seeks the information with WITH (NOLOCK) in the SELECT you probably won’t have this problem anymore

  • So what query is inside Trigger? Why do I use some of Trigger’s update information to feed another table

  • 1

    You better put the code on TRIGGER in order to have an opinion, otherwise the question is too general and there is no way to give you a definitive solution

1 answer

0

One option would be to use the NOLOCK command. It warns the SQL Server processor to avoid using locks (LOCK) while reading data. However, SQL Server keeps using locks during data modification operations and there is no way to change this behavior. Using NOLOCK is therefore invalid for tables that will be affected by INSERT, DELETE and UPDATE.

INSERT tbUsuarios WITH (NOLOCK) (nome) VALUES ('A')

DELETE tbUsuarios WITH (NOLOCK) WHERE id = 1

UPDATE tbUsuarios WITH (NOLOCK) SET nome = 'B' WHERE id = 1

These commands result in error:

Msg 1065, Level 15, State 1, Line 15

The NOLOCK and READUNCOMMITTED lock hints are not allowed for target Tables of INSERT, UPDATE, DELETE or MERGE statements.

However, the NOLOCK restriction applies only to the table that will receive the new records, and it is still possible to use NOLOCK for the tables that provide the records. Example:

INSERT tbHistorico (Nome)
SELECT Nome FROM tbUsuarios WITH (NOLOCK)

Source: https://blogs.msdn.microsoft.com/fcatae/2010/10/11/update-with-nolock-como-funciona/

Browser other questions tagged sql-server trigger

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