breadcrumb.sql

/*You can just hit F5 to execute the whole script, read the comments to follow along*/
USE tempdb;
SET NOCOUNT ON;
GO

/*Just in case*/
DROP TABLE IF EXISTS
    dbo.tp;

/*A table*/
CREATE TABLE
    dbo.tp
(
    id int
       PRIMARY KEY,
    some_date datetime
);
GO

/*Start with a single id of 1 and a NULL date*/
INSERT 
    dbo.tp
(
    id,
    some_date
)
VALUES
(
    1,
    NULL
);

GO
/*A trigger*/
CREATE OR ALTER TRIGGER
    dbo.tp_t
ON dbo.tp
AFTER
    UPDATE,
    DELETE
AS
BEGIN
    IF @@ROWCOUNT = 0 
        OR OBJECT_ID(N'#f6a38e388400495e8b277b188ce2864e') IS NOT NULL
    BEGIN
        RETURN;
    END;
   
    SET NOCOUNT ON;

    /*Just to have some proof of execution, increment the id column*/
    UPDATE tp
      SET id += 1
    FROM dbo.tp AS tp
    JOIN Deleted AS d
      ON d.id = tp.id;
END;
GO

/*This is the procedure we don't want to fire the trigger*/
CREATE OR ALTER PROCEDURE
    dbo.tp_u
AS
BEGIN
    SET NOCOUNT, XACT_ABORT ON;
         
    /*Set the breadcrumb*/
    CREATE TABLE #f6a38e388400495e8b277b188ce2864e (crumb bit NULL);
   
    UPDATE tp
    SET some_date = '19000101'
    FROM dbo.tp AS tp;

    SELECT
        t.*
    FROM dbo.tp AS t;
END;
GO

/*This procedure is fine*/
CREATE OR ALTER PROCEDURE
    dbo.tp_u2
AS
BEGIN
    SET NOCOUNT, XACT_ABORT ON;
  
    UPDATE tp
      SET some_date = '99991231'
    FROM dbo.tp AS tp;
   
    SELECT
        t.*
    FROM dbo.tp AS t;
END;
GO

/*Doesn't increment id*/
EXEC dbo.tp_u;

/*increments id*/
EXEC dbo.tp_u2;