how to pass parameter from procedure into trigger RSS

• • Reply
  zhyanadil.it...

  Member

  75 Points

  512 Posts

  how to pass parameter from procedure into trigger

  Jun 05, 2020 08:42 AM|zhyanadil.it@gmail.com|LINK

  i have this procedure 

  ALTER proc [dbo].[INSERT_Brand]
  
  @series varchar(50)=null,
  @Brand_name varchar(50)=null,
  @Description varchar(50)=null,
  @user  varchar(50)=null
  as begin
     --*****************************************
  declare  @Series_1 varchar(50)
           ,@series2 varchar(50)
   set @Series_1=( select top 1 series from Brand  order by ID desc)
  
  
  set @user='admin'
  
  IF(@Series_1 is not null)
  begin
  select @Series2=CONCAT('Brand-',RIGHT(@Series_1, CHARINDEX('-', REVERSE(@Series_1)) - 1)+1 )
  end 
  else 
  begin 
  SELECT @Series2=CONCAT('Brand-','1') 
  end 
  
  begin try
  set nocount on;
  declare @trancount int;
  set @trancount = @@trancount;
  if @trancount = 0
  begin transaction
  else
  save transaction INSERT_Brand;
  
  insert into [dbo].Brand(
  
  series,
  Brand_name,
  Description
  
  )
  
  values(@Series2,@Brand_name,@Description)
  
  lbexit:
  if @trancount = 0		
  commit;
  end try
  begin catch
  declare @error int, @message varchar(4000), @xstate int,@ErrorProc sysname, @ErrorLine INT;
  select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE(),@ErrorProc = ERROR_PROCEDURE(),@ErrorLine = ERROR_LINE();
  if @xstate = -1
  rollback;
  if @xstate = 1 and @trancount = 0
  rollback
  if @xstate = 1 and @trancount > 0
  rollback transaction
  INSERT INTO ErrorLog (ErrorMsg,  ErrorNumber,  ErrorProc,  ErrorLine) VALUES (@message, @error, @ErrorProc, @ErrorLine)
  raiserror ('INSERT_Brand: %d: %s', 16, 1, @error, @message);
  end catch
  End
  
  

  here is trigger

  CREATE trigger [dbo].[trg_audit_TableName]
  ON [dbo].brand
  FOR INSERT
  AS
  
  declare @get_user varchar(300)
  
  set @get_user=@user(i want to set @get_user get value from this parameter @user from procedure)
  
  insert into tb_log(user)(@user)

• • Reply
  yij sun

  Contributor

  2580 Points

  1024 Posts

  Re: how to pass parameter from procedure into trigger

  Jun 08, 2020 02:45 AM|yij sun|LINK

  Hi zhyanadil.it@gmail.com,

  Accroding to your description,as far as i think,trigger can't accept parameters.You could use stored procedure or function.

  Besides,you could try context_info() to retrieve the session context and create a new stored procedure,store this unique key in CONTEXT_INFO which you could then use in the Trigger to access the desired information.

  More details,you could refer to below article:

  https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms189252(v=sql.105)?redirectedfrom=MSDN

  https://www.rahulsingla.com/blog/2010/11/sql-server-passing-parameters-to-triggers/

  Best regards,

  Yijing Sun

  MSDN Community Support
  Please remember to click "Mark as Answer" the responses that resolved your issue.
  If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.