SQL SERVER: Use CONTEXT_INFO

Recently I have following situation: I don't need to execute trigger's code on some condition, like if it is called from particular stored procedure. If data is being updated from any other places, like application, or any other stored procedures, trigger code should be executed, but when one particular Stored Procedure is updating data to that table, it should not allow to do so. I search and I got the option CONTEXT_INFO with SQL SERVER. Let me share this with all of you. NOTE: We can use CONTEXT_INFO value in that session only. If there is new session we cannot use that value stored in CONTEXT_INFO. We can set CONTEXT_INFO up to 128 bytes of binary information with the current session or connection. Reference: http://msdn.microsoft.com/en-us/library/ms180125.aspx Let see an example, for better understanding: CREATE TABLE tblA( ID INT IDENTITY, ColVal VARCHAR(100) ) Let's create two SPs, which will insert a record into tblA, which is just created CREATE PROC TestA AS BEGIN INSERT INTO tblA(ColVal) SELECT 'Allow To insert' END CREATE PROC TestB AS BEGIN DECLARE @UID VARBINARY(128) SELECT @UID = CAST('TestB' AS VARBINARY(128)) SET CONTEXT_INFO 0x5465737442 INSERT INTO tblA(ColVal) SELECT 'Not Allow To insert' END In this example, when we execute SP: TestA, it should allow to insert record in the table, while we execute SP: TestB, it should restrict. Here I set CONTEXT_INFO with SPName. I can use the same whatever is stored in CONTEXT_INFO during the current session. To make validation as defined above, lets create one trigger to restrict/allow user to proceed. CREATE TRIGGER trg_TblA ON tblA AFTER INSERT,DELETE,UPDATE AS BEGIN SET NOCOUNT ON; DECLARE @Message varbinary(128) SELECT @Message = cast('TestB' as varbinary(128)) IF @Message = CONTEXT_INFO() BEGIN RAISERROR('Not Allowed to Insert/Update/Delete from SP: TestB',15,1) ROLLBACK TRAN END END GO In Trigger, as you can see, I again converted the SP name and compare it with CONTEXT_INFO. When Stored Procedure TestA is being executed, CONTEXT_INFO is not set, so trigger will not find comparison and allows user to insert record. While we execute TestB, as we have set CONTEXT_INFO, system will find comparison in trigger and restrict us to complete transaction. So system will give error message: So, by this way we can validate value in trigger, to make such decision