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.
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.
Member
71 Points
466 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 Endhere is trigger
Participant
1460 Points
651 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
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.