gistfile1.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
    BEGIN
        RETURN;
    END;
   
    SET NOCOUNT ON;

    /*Use this breadcrump to bail on the trigger*/
    IF
      (
          CONVERT
          (
              int,
              SESSION_CONTEXT(N'dbo.tp_u')
          )
      ) = 138
    BEGIN
        RETURN;
    END;

    /*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;

    /*Change session context here just in case other commands should use the trigger*/
    EXEC sys.sp_set_session_context
        N'dbo.tp_u',
        831;
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*/
    EXEC sys.sp_set_session_context
        N'dbo.tp_u',
        138;
   
    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;