How to determinate in a trigger direct inserts and inserts via stored procedure

TableX can be modified in two ways:

• client does "direct" inserts

• client uses stored procedure to inserts records

How to determinate the way of client's call (direct or stored proc) in a trigger of tableX

Thank you.

You can't determine it. All you have available to you is the INSERTED and DELETED tables. You could probably cheat but it's a gawdawful, bloody hack. Define a view with an "extra" column, what it is doesn't matter. All your proc insert/update assign one value to that column, direct updates supply a different (or non-existent value). Create an instead of trigger on that view and then do your update logic based on the source flag

CREATE VIEW dbo.vwTableX
AS
SELECT
    X.*
,   'T' AS fake_source_column
FROM
    dbo.tableX X

GO
CREATE TRIGGER insertHack ON dbo.vwTableX
INSTEAD OF INSERT
AS
BEGIN

    SET NOCOUNT ON

    IF EXISTS (SELECT * FROM INSERTED I WHERE I.fake_source_column = 'P')
    BEGIN
        -- perform logic for PROC sourced data
        INSERT INTO
            dbo.tableX
        SELECT
            I.col1
        ,   I.col2
        -- everything but our fake column
        FROM
            INSERTED I
        WHERE
            I.fake_source_column = 'P'
    END

    IF EXISTS (SELECT * FROM INSERTED I WHERE I.fake_source_column = 'D' OR I.fake_source_columns IS NULL)
    BEGIN
        -- perform logic for direct access data
        INSERT INTO
            dbo.tableX
        SELECT
            I.col1 * 2 -- or whatever special logic you wish to apply
        ,   I.col2
        -- everything but our fake column 
        FROM
            INSERTED I
        WHERE
            I.fake_source_column = 'D' OR I.fake_source_columns IS NULL
    END
END
GO

-- lather, rinse, repeat for update and/or delete trigger

So that's a solution, but really, what is the problem you are trying to solve? Why do you care whether the DML comes from proc or non-proc?

StackExchange Database Administrators Q#3816, answer score: 5