how to pass parameter from procedure into trigger RSS

• • Reply
  zhyanadil.it...

  Member

  75 Points

  513 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

  4040 Points

  1568 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

  ASP.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today. Learn more >