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

Question

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.

Answer 1

Use SET CONTEXT_INFO in the stored procedure and read it in the trigger with CONTEXT_INFO ()

Have the trigger reset it on exit.

Answer 2

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?

Answer 3

I suggest that you start a tracing session over the statements that touch (do insert actions) TableX and see some specific information like hostname, loginame, programname.

Most likely the manual inserts will be done from Management Studio using his station and his username, while the stored procedure inserts will be done from a specific application, using an application user and that program name. If you see some constant pattern that will help you discover differences between the actions, than you'll be able to use the following sample query to save data in a logging table:

select sp.hostname, sp.program_name, sp.loginame
from sysprocesses sp
where spid = @@spid

That solution (or the previous one suggested by billinkc) only if you can't alter the stored procedures. If you can do that, than it should be easy to add the logging part.