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
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
Reference : Tejas Shah
http://www.SQLYoga.com
