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)
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.
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 >
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
here is trigger
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