RETURN inside a Trigger, safe?

Question

I'm trying to find information on the effects of RETURN inside a trigger. The only documentation I can find on it is that it "releases" the trigger.

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-2017#optimizing-dml-triggers

The reason I ask is that a recently added trigger is causing some deadlocking issues, even when the trigger is empty.

These examples are pretty nonsensical (why retrieve data that was just inserted), but that's just the way it works. The code that generates these statements is pretty damn old.

Body of the "empty" trigger

BEGIN
    SET NOCOUNT ON;
END

Empty trigger enabled on Table1

BEGIN TRANSACTION
    INSERT INTO Table1....                 -- Table1 becomes locked until transaction is committed

    SELECT * FROM Table1 WHERE ID = X...   -- deadlock

    INSERT INTO Table2...
COMMIT TRANSACTION

Empty trigger disabled

BEGIN TRANSACTION
    INSERT INTO Table1....                 -- Table1 DOES NOT BECOME LOCKED

    SELECT * FROM Table1 WHERE ID = X...

    INSERT INTO Table2...
COMMIT TRANSACTION

Adding RETURN at the end of the trigger releases the lock, preventing the deadlock.

Is this safe? The only thing I can think of is if the trigger modifies the table its on, releasing the lock may cause a dirty read.

Answer 1

To answer your question, yes, it is perfectly safe to use RETURN inside a trigger. It is often used at the start of complex triggers to exit immediately if there are no rows to process. Like this;

IF (@@ROWCOUNT_BIG = 0)
RETURN;

However, there must be something else going on to cause a deadlock. See Gail Shaw's excellent blog SQL Server Deadlocks by Example and take a look at the deadlock graph to see if it gives you some more clues to where the problem might be.