1

I want to get some value in trigger when its start executing. However getting the value using parameter option is not available in trigger. So I decided to store that particular value in one variable and pass it to Context_info in my stored procedure.

And I am getting that value in my trigger. But still I am getting null value only. But I tried manually by passing parameter to stored procedure, it is executed correctly and Context_info value is also showing correctly in that stored procedure.

But when I am getting this value in trigger it is showing NULL. And also in sys.dm_exec_sessions table context_info value is not getting updated but last_request_start_time and last_request_end_time is getting updated.

Can anyone please guide me to find my mistake where I am going wrong?

Here are my stored procedure and trigger:

ALTER PROCEDURE [dbo].[dm_ApptResRecurrReasonRelViewSel]    
   (@IDLocation int,    
    @IDApptResource int,    
    @StartDate datetime,    
    @IDApptResRecurr int,  
    @ClientID varchar(50) )    
AS    
    SET NOCOUNT ON;    

    DECLARE @context_info varbinary(100);  
    SET @context_info = CAST(@ClientID as varbinary(100));  

    SET CONTEXT_INFO @context_info;  

     etc....

Trigger

ALTER TRIGGER [dbo].[xC2C_ADT_Upd] 
ON [dbo].[dmAppt] 
FOR INSERT, UPDATE   
AS   
BEGIN  
    If Not Exists (Select top 1* from inserted)  
       Return  

    declare @ADT_ID UniqueIdentifier  
    declare @personuid int   
    declare @ApptID int  

    select @personuid = PID, @ApptID = IDAppt 
    from inserted  

    set @ADT_ID = NewID()  

    declare @MSG_Type varchar(1)  
    declare @AddDate datetime  
    declare @LastUpdateDate datetime  
    declare @ServerID [varchar](50)  
    declare @ClientDB [varchar](max)  
    declare @ClientID [varchar](50)  

    set @ServerID = Convert(varchar(50),CONNECTIONPROPERTY('local_net_address'))   
    set @ClientDB = db_name()  

    set @ClientID = CAST(CONTEXT_INFO() as varchar(50))
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
shobia
  • 75
  • 1
  • 2
  • 9
  • You trigger is broken for other reasons also - `inserted` may contain 0, 1 or *multiple* rows. So `select @personuid = PID, @ApptID = IDAppt from inserted` is misguided because it assumes a single row. – Damien_The_Unbeliever May 05 '16 at 09:17
  • But ClientID i can't able to get like that. from the C# itself i'm passing clientid to stored procedure – shobia May 05 '16 at 09:36
  • Even after executing the SP manually by pass Parameters, in new query window i'm executing this command "select CAST(CONTEXT_INFO() as varchar(50))". It is also returning NULL – shobia May 05 '16 at 09:39
  • sys.dm_exec_sessions is sleeping for context_info also. Because of this it is giving null?? – shobia May 05 '16 at 11:34
  • Can any one help me?? – shobia May 05 '16 at 11:42
  • Setting context_info within stored procedures and reading it within triggers works - it's a fundamental part of our current infrastructure where I work. So, you need to reduce this down to the fundamentals and create a [mcve] that demonstrates *your* issue. Strip out everything that is irrelevant, but make sure that the code you show us exhibits the problem you're facing. And then [edit] your question with this example. – Damien_The_Unbeliever May 05 '16 at 17:46

0 Answers0