0

i'm running my application using one DB but during some specific table insertion i'm calling trigger where i need to get some value from some another DB.Previously i did this by hard coding the specific DB name inside the trigger. i got it correctly but now i need to remove the dependency of that DB name.so i need to find get that particular value in some another way. Can anyone help me to find the solution please...

Previous trigger.

CREATE 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()  
 select @ClientID = ClientID from dm_asarum_Client_Master..Clients  //here      only my database is hardcoded 
 where DBName_Current = @ClientDB

Now i did some changes,

set @ClientID = (select top 1 CAST(CONTEXT_INFO as varchar(50)) from    sys.dm_exec_sessions where context_info!=0x order by last_request_end_time desc) --ClientID from dm_asarum_Client_Master..Clients

i have set the Context_info value in some SP like the following.,

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

Declare @context_info1 varbinary(128);    

SET    
@context_info1 = CAST(@ClientID as varbinary(128));    

SET    
CONTEXT_INFO @context_info1;

Now the problem is,Using the SQL Profiler i noticed, i can able the get the clintID inside this SP correctly. but it is not saving this Context_info inside sys.dm_exec_sessions correctly. when i ran this manually in sql like,

   exec [dm_ApptResRecurrReasonRelViewSel] @IDLocation=101,@IDApptResource=59,@StartDate='2016-05-27 17:00:00',@IDApptResRecurr=NULL,@ClientID='cl_obgyn_reg'

i context_info value is updated correctly i don't know where i missied.

shobia
  • 75
  • 1
  • 2
  • 9
  • can anyone help me?? – shobia May 27 '16 at 13:54
  • Which RDBMS is this for? Triggers are **highly** vendor-specific - please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. – marc_s May 27 '16 at 14:01
  • i am using sql-server only – shobia May 27 '16 at 14:24
  • 2
    Your trigger has **MAJOR** flaw in that you seem to assume it'll be called **once per row** - that is **not** the case. The trigger will fire **once per statement**, so if your `INSERT` statement that causes this trigger to fire inserts 25 rows, you'll get the trigger fired **once**, but then `Inserted` pseudo table will contain 25 rows. Which of those 25 rows will your code select here?? `select @ApptID = IDAppt from inserted` - it's non-deterministic, you'll get **one arbitrary row** and you will be **ignoring all other rows**. You need to rewrite your trigger to take this into account! – marc_s May 27 '16 at 14:42
  • @Marc_S thanks for your reply. But that is not my actual problem, i'm stucking with saving Context_info value. when the SP is executed by the code the Context_info is not saving into sys.dm_exec_sessions table. when i manually running the code using EXEC comment by passing parameters the Context_info value is saving correctly.. – shobia May 27 '16 at 14:52

0 Answers0