i have this procedure inside this procedure i have a trigger i want when i call this procedure inside another trigger create dynamic
trigger(TR_TB_Log_Insert_brand) which i wrote it inside
this procedure
ALTER proc [dbo].[INSERT_Brand]
@series varchar(50)=null,
@Brand_name varchar(50)=null,
@Description varchar(50)=null,
@user varchar(200)=null
as begin
--*****************************************
declare @Series_1 varchar(50)
,@series2 varchar(50)
set @Series_1=( select top 1 series from Brand order by ID desc)
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)
-------------------------------------------------------------------
--Trigger
if exists (SELECT name FROM sys.triggers WHERE name = 'TR_TB_Log_Insert_brand')
DROP TRIGGER TR_TB_Log_Insert_brand;
DECLARE @sql1 NVARCHAR(max) ,
@date_time varchar(4000)
set @date_time=(select format(getdate(),'dd/MM/yyyy,hh:mm:ss'))
SET @sql1 ='
create TRIGGER TR_TB_Log_Insert_brand
ON brand
after UPDATE,insert,delete
as begin
insert into tb_log (series,date_time,username,message)values('''+@series2+''','''+@date_time+''','''+@user+''',concat('''+@user+''' ,'' Created'','' '','''+@date_time+'''))
end'
EXEC (@sql1)
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 my static trigger
alter TRIGGER [dbo].[TR_TB_Log_Insert_branch2]
ON [dbo].[Branch]
after insert
as begin
exec INSERT_Brand
@Brand_name= 'i',
@Description='ffff'
end
my problem is when i insert data into branch table also i want to insert data into brand table and create that trigger TR_TB_Log_Insert_brand
I don't understand your response or your design. You're converting what should be a dynamic insert into a static insert. Makes no logical sense whatsoever. Just insert the record perhaps using a stored procedure to pass the parameters rather than a trigger.
Member
75 Points
513 Posts
dynamic trigger not created inside procedure
Jul 05, 2020 03:21 PM|zhyanadil.it@gmail.com|LINK
i have this procedure inside this procedure i have a trigger i want when i call this procedure inside another trigger create dynamic trigger(TR_TB_Log_Insert_brand) which i wrote it inside
this procedure
ALTER proc [dbo].[INSERT_Brand] @series varchar(50)=null, @Brand_name varchar(50)=null, @Description varchar(50)=null, @user varchar(200)=null as begin --***************************************** declare @Series_1 varchar(50) ,@series2 varchar(50) set @Series_1=( select top 1 series from Brand order by ID desc) 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) ------------------------------------------------------------------- --Trigger if exists (SELECT name FROM sys.triggers WHERE name = 'TR_TB_Log_Insert_brand') DROP TRIGGER TR_TB_Log_Insert_brand; DECLARE @sql1 NVARCHAR(max) , @date_time varchar(4000) set @date_time=(select format(getdate(),'dd/MM/yyyy,hh:mm:ss')) SET @sql1 =' create TRIGGER TR_TB_Log_Insert_brand ON brand after UPDATE,insert,delete as begin insert into tb_log (series,date_time,username,message)values('''+@series2+''','''+@date_time+''','''+@user+''',concat('''+@user+''' ,'' Created'','' '','''+@date_time+''')) end' EXEC (@sql1) 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 my static trigger
my problem is when i insert data into branch table also i want to insert data into brand table and create that trigger TR_TB_Log_Insert_brand
All-Star
53081 Points
23655 Posts
Re: dynamic trigger not created inside procedure
Jul 05, 2020 03:48 PM|mgebhard|LINK
Why don't you simply insert the record rather than creating a trigger that does the same? Makes no logical sense.
Member
75 Points
513 Posts
Re: dynamic trigger not created inside procedure
Jul 05, 2020 04:45 PM|zhyanadil.it@gmail.com|LINK
i nee trigger fro another case
All-Star
53081 Points
23655 Posts
Re: dynamic trigger not created inside procedure
Jul 05, 2020 05:22 PM|mgebhard|LINK
I don't understand your response or your design. You're converting what should be a dynamic insert into a static insert. Makes no logical sense whatsoever. Just insert the record perhaps using a stored procedure to pass the parameters rather than a trigger.
Member
75 Points
513 Posts
Re: dynamic trigger not created inside procedure
Jul 05, 2020 05:29 PM|zhyanadil.it@gmail.com|LINK
shortly i want to pass @user parameter from from insert_branch procedure into that trigger TR_TB_Log_Insert_branch2