locked
dynamic trigger not created inside procedure RRS feed

  • Question

  • User-1634604574 posted

    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

    Sunday, July 5, 2020 3:21 PM

All replies

  • User475983607 posted

    Why don't you simply insert the record rather than creating a trigger that does the same?  Makes no logical sense.

    Sunday, July 5, 2020 3:48 PM
  • User-1634604574 posted

    i nee trigger fro another case

    Sunday, July 5, 2020 4:45 PM
  • User475983607 posted

    zhyanadil.it@gmail.com

    i nee trigger fro another case

    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.

    Sunday, July 5, 2020 5:22 PM
  • User-1634604574 posted

    shortly i want to pass @user parameter from from insert_branch procedure into that trigger TR_TB_Log_Insert_branch2

    Sunday, July 5, 2020 5:29 PM