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
Have the trigger reset it on exit.
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
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?
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:
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.