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
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 only takes a minute to sign up.Sign up to join this community
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?
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.