Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Can you change permissions? –  gbn Jul 13 '11 at 19:25

3 Answers 3

up vote 4 down vote accepted

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

Have the trigger reset it on exit.

share|improve this answer
    
+1 That's a nifty trick and easier to implement than my solution. –  billinkc Jul 13 '11 at 19:22
    
@billinkc: yours is clever, especially if you can combine it with permissions eg DENY permissions on the fake column so it's not visible. A power user could set context for my one if they were clever enough –  gbn Jul 13 '11 at 19:23
1  
@gbn: if he could touch the stored procedure, he can also use any logging mechanism from there (simple table inserts...), why would he need any of the 3 hacks as we proposed? I treated this option (and others) in his second question. –  Marian Jul 13 '11 at 20:13
    
it works, thanks. –  garik Jul 14 '11 at 8:33

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?

share|improve this answer
    
That's a nice hack! –  Marian Jul 13 '11 at 14:45
    
+1 for so hack. –  garik Jul 14 '11 at 8:34

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.

share|improve this answer
    
yes, i can change stored procedures, +1 thank you for suggestion –  garik Jul 14 '11 at 8:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.