problem with INSERT TRIGGER

• luissantos

  SSCertifiable

  Points: 7391

  More actions

  January 26, 2018 at 4:46 pm

  #335628

  Hello comunity,
  I build  this Trigger  to  insert into table mytab  2 keys for use on another transaction.
  
ALTER TRIGGER [dbo].[trg_DeleteTabelaauxiliaresNCRED]
ON [dbo].[ft]
FOR INSERT
AS
    SET NOCOUNT ON;
    
    DECLARE @ftstamp VARCHAR(25);
    DECLARE @myID INT;
    DECLARE [myID] INT);    
    DECLARE @ofistamp VARCHAR(25);

      --CREATE TABLE mytab( ftstamp VARCHAR(25), myid INT)
    --DROP table mytab
    
    IF TRIGGER_NESTLEVEL() > 1
      RETURN;    
     
        IF EXISTS (SELECT * FROM INSERTED WHERE ndoc = 4) -- I check that i Insert Credit note
            BEGIN
                PRINT 'entrei'
                
                SELECT @ofistamp = fi.ofistamp FROM INSERTED i INNER JOIN fi ON fi.ftstamp = i.ftstamp -- @ofistamp is my ID key present on my credit note to drill to my invoice
                WHERE fi.ref <> ''
                PRINT @ofistamp
                
                SELECT @ftstamp = ftstamp FROM fi WHERE fistamp = @ofistamp AND fi.ndoc IN (1,2,3,5) AND fi.ref <> '' --now i find my Header invoice Key that is @ftstamp
                PRINT @ftstamp

                      -- Here i try to save my header key invoice that is present on my table FT and also on my table u_blDocsCab
                     INSERT INTO mytab --@DelRecs
                    SELECT [ft].[ftstamp], [blDocsCab].[ID]
                    FROM [ft]
                        INNER JOIN [u_blDocsCab] AS [blDocsCab]
                        ON [blDocsCab].[ftstamp] = [ft].[ftstamp]
                    WHERE ft.ftstamp = @ftstamp

              END    


  My problem  is when  i create my credit note the trigger don´t firing, and i don´t know why.
  Someone could give me some help.
  Best regards
  luis

• Evgeny Garaev

  SSCertifiable

  Points: 7428

  More actions

  January 29, 2018 at 1:49 pm

  #1977775

  luissantos - Friday, January 26, 2018 4:46 PM

  ... when  i create my credit...

  I wonder what that does mean... anyway I spot the mistake:

  try this:
  
create trigger trg_test
on [dbo].[ft] for insert
AS
begin
 PRINT TRIGGER_NESTLEVEL();
end;


  And execute your insert. Did you get what I mean?
  https://docs.microsoft.com/en-us/sql/t-sql/functions/trigger-nestlevel-transact-sql
  

  TRIGGER_NESTLEVEL returns 0 if it is executed outside a trigger...