title: Getting error INSTEAD OF triggers do not support direct recursion?
body: <p>I have a requirement to write a delete trigger,before deleting vendor it will check for existence on other database table ,if relation not found on other database table then send email to admin and set delete flag=1 and not delete record then next time when delete command is issue for same vendor it's check flag and delete that record.</p>
  
  <pre><code>create TRIGGER [dbo].[trgInsteadOfDelete] ON [dbo].[AP_Vendor] 
  INSTEAD OF DELETE
  AS
      declare @apdivisionno varchar(2);
      declare @vendorno varchar(7);
      declare @vendorname varchar(30);
      declare @addressline1 varchar(30);
      declare @addressline2 varchar(30);
      declare @addressline3 varchar(30);
      declare @city varchar(20);
      declare @state varchar(2);
      declare @zipcode varchar(10);
      declare @countrycode varchar(3);
  
      declare @primarycontact varchar(10);
      declare @telephoneno varchar(17);
      declare @telephoneext varchar(5);
      declare @faxno varchar(17);
      declare @emailaddress varchar(250);
      declare @urladdress varchar(50);
      declare @termscode varchar(2);
      declare @reference varchar(15);
      declare @temporyvendor varchar(1);
      declare @creditcardvendor varchar(1);
  
      declare @holdpayment varchar(1);
      declare @electronicpayment varchar(1);
      declare @standardentryclass varchar(3);
      declare @exemptionnoOnFile varchar(1);
      declare @taxschedule varchar(9);
      declare @taxclass varchar(2);
      declare @accountkey varchar(9);
      declare @SeparateCheck varchar(1);
      declare @Comment varchar(30);
      declare @Sort varchar(10);
  
      declare @batchfax varchar(1);
      declare @UsePOReceiptOfInvForVendor varchar(1);
      declare @PrimaryPurchaseAddressCode varchar(4);
      declare @VendorType varchar(1);
      declare @Form1099 varchar(1);
      declare @TaxpayerIDSocialSecurityNo varchar(9);
      declare @Box1099 varchar(3);
      declare @MiscBox9 varchar(1);
      declare @LastPurchaseDate datetime;
      declare @LastPaymentDate datetime;
  
      declare @LastCheckNo varchar(10);
      declare @LastCheckAmt decimal(14, 2);
      declare @RetentionRate decimal(6, 2);
      declare @AverageDaysToPay decimal(3, 0);
      declare @AverageDaysOverDue decimal(3, 0);
      declare @balancedue decimal(15, 2);
      declare @NumberOfInvToUseInCalc decimal(7, 2);
      declare @datecreated datetime;
      declare @timecreated varchar(8);
      declare @UserCreatedKey varchar(10);
  
      declare @dateupdated datetime;
      declare @timeupdated varchar(8);
      declare @UserUpdatedKey varchar(10);
      declare @UDF_LONG_NAME varchar(60);
  
      declare @audit_Event varchar(10);
      declare @audit_log varchar(100);
      declare @dbname varchar(50);
  
      declare @Deletedbname varchar(50);
  
      declare @rowcount int;
      declare @TableName varchar(50);
      declare @TableFieldName varchar(50);
  
      declare @dbcounter int;
      declare @tablecounter int;
      declare @cnt INT ;
      DECLARE @selectVendorno varchar(75)
  
      select @apdivisionno =i.APDivisionNo from deleted i;    
      select @vendorno=i.VendorNo from deleted i; 
      select @vendorname=i.VendorName from deleted i;
      select @addressline1 =i.AddressLine1 from deleted i;    
      select @addressline2=i.AddressLine2 from deleted i; 
      select @addressline3=i.AddressLine3 from deleted i; 
      select @city =i.City from deleted i;    
      select @state=i.state from deleted i;   
      select @zipcode=i.zipcode from deleted i;   
      select @countrycode =i.countrycode from deleted i;  
      select @primarycontact=i.PrimaryContact from deleted i; 
      select @telephoneno=i.TelephoneNo from deleted i;   
      select @telephoneext =i.telephoneext from deleted i;    
      select @faxno=i.faxno from deleted i;   
      select @emailaddress=i.emailaddress from deleted i; 
      select @urladdress =i.urladdress from deleted i;    
      select @termscode=i.termscode from deleted i;   
      select @temporyvendor=i.TemporaryVendor from deleted i; 
      select @creditcardvendor =i.CreditCardVendor from deleted i;    
      select @holdpayment=i.holdpayment from deleted i;   
      select @electronicpayment=i.ElectronicPayment from deleted i;   
      select @standardentryclass=i.StandardEntryClass from deleted i; 
      select @exemptionnoOnFile=i.ExemptionNoOnFile from deleted i;   
      select @taxschedule=i.taxschedule from deleted i;   
      select @taxclass=i.taxclass from deleted i; 
      select @accountkey=i.accountkey from deleted i; 
      select @SeparateCheck=i.SeparateCheck from deleted i;   
      select @Comment=i.Comment from deleted i;   
      select @Sort=i.Sort from deleted i; 
      select @batchfax=i.batchfax from deleted i; 
      select @UsePOReceiptOfInvForVendor=i.UsePOReceiptOfInvForVendor from deleted i; 
      select @PrimaryPurchaseAddressCode=i.PrimaryPurchaseAddressCode from deleted i; 
      select @VendorType=i.VendorType from deleted i; 
      select @Form1099=i.Form1099 from deleted i;
      select @TaxpayerIDSocialSecurityNo=i.TaxpayerIDSocialSecurityNo from deleted i; 
      select @Box1099=i.Box1099 from deleted i;   
      select @MiscBox9=i.MiscBox9 from deleted i;
      select @LastPurchaseDate=i.LastPurchaseDate from deleted i; 
      select @LastPaymentDate=i.LastPaymentDate from deleted i;   
      select @LastCheckNo=i.LastCheckNo from deleted i;
      select @LastCheckAmt=i.LastCheckAmt from deleted i;
      select @RetentionRate=i.RetentionRate from deleted i;
      select @AverageDaysToPay=i.AverageDaysToPay from deleted i;
      select @AverageDaysOverDue=i.AverageDaysOverDue from deleted i;
      select @balancedue=i.balancedue from deleted i;
      select @NumberOfInvToUseInCalc=i.NumberOfInvToUseInCalc from deleted i;
      select @datecreated=i.datecreated from deleted i;
      select @timecreated=i.timecreated from deleted i;
      select @UserCreatedKey=i.UserCreatedKey from deleted i;
      select @dateupdated=i.dateupdated from deleted i;
      select @timeupdated=i.timeupdated from deleted i;
      select @UserUpdatedKey=i.UserUpdatedKey from deleted i;
      select @UDF_LONG_NAME=i.UDF_LONG_NAME from deleted i;
      select @Deletedbname= DB_NAME();
  
      set  @dbcounter =0;
      set  @tablecounter =0;
  
      DECLARE @sql NVARCHAR(MAX) 
      DECLARE @deleteSql NVARCHAR(MAX) 
  
      select @rowcount=count(*) from [Mas_Master].[dbo].[Master_Vendor] where vendorno=@vendorno and deleteflag=1;
  
  
  
      BEGIN
          if(@rowcount&lt;=0) 
          begin
  
                  DECLARE db_cursor CURSOR FOR  
                  SELECT [dbname] FROM [Mas_Master].[dbo].[lookup_dbName]
  
                  OPEN db_cursor   
                  FETCH NEXT FROM db_cursor INTO @dbname 
  
                  WHILE @@FETCH_STATUS = 0   
                  BEGIN   
  
                             DECLARE Table_cursor CURSOR FOR  
                             SELECT [TableName],[TableFieldName] FROM [Mas_Master].[dbo].[lookupTableName]
                             OPEN Table_cursor   
                             FETCH NEXT FROM Table_cursor INTO @TableName,@TableFieldName 
  
                             WHILE @@FETCH_STATUS = 0  
                             BEGIN   
  
  
                                  if(CONCAT(@dbname, '_', @TableName)&lt;&gt;CONCAT(@Deletedbname, '_', 'AP_VENDOR'))  /*Not Check For Same Database */
                                   Begin
  
                                      set @sql='Select  @cnt=count(*) from ' +@dbname+'.dbo.'+@TableName +' where '+ @TableFieldName +'= ''' + @vendorno + '''';
                                      EXECUTE sp_executesql @sql, N' @cnt int out', @cnt OUTPUT
  
                                      INSERT INTO Mas_master.dbo.testDelete
                                              VALUES
                                             (
                                             @dbname
                                             ,@TableName
                                             ,@vendorno,@sql,@cnt
                                             )
  
                                          if(@cnt&gt;0)
                                          Begin
                                              SET @tablecounter = @tablecounter + 1;
                                              break;  
                                          end
                                   End
  
  
  
                                  FETCH NEXT FROM Table_cursor INTO @TableName,@TableFieldName 
                             End
                             CLOSE Table_cursor;
                             DEALLOCATE Table_cursor;
                   SET @dbcounter = @dbcounter + 1;
                   FETCH NEXT FROM db_cursor INTO @dbname   
                  END 
               CLOSE db_cursor;
              DEALLOCATE db_cursor;
                  /* if no match vendor found on other database then mark master table vendor delete*/
                  if(@tablecounter=0)
                  begin
                         UPDATE [Mas_Master].[dbo].[Master_Vendor]
                           SET [DeleteFlag] = 1
                          where vendorno=@vendorno
                  End
  
          end
          else /* if delete Flag = 1 then delete vendor from current database table and master vendor table */
          Begin
                set @deleteSql= 'DELETE FROM '+@Deletedbname+'.dbo.AP_Vendor WHERE vendorno=@vendorno';
                EXECUTE sp_executesql @deleteSql, N'@vendorno nvarchar(75)', @vendorno = @vendorno
                DELETE FROM Mas_Master.dbo.Master_Vendor WHERE vendorno=@vendorno
          End
      End 
  </code></pre>
  
  <p>if INSTEAD OF triggers used i got error message  INSTEAD OF triggers do not support direct recursion.</p>
  
  <p>If I used After Delete Trigger the i got error message </p>
  
  <pre><code>**Msg 3609, Level 16, State 1, Line 1
  The transaction ended in the trigger. The batch has been aborted.**
  
  
  create TRIGGER [dbo].[trgInsteadOfDelete] ON [dbo].[AP_Vendor] 
  after DELETE
  AS
      declare @apdivisionno varchar(2);
      declare @vendorno varchar(7);
      declare @vendorname varchar(30);
      declare @addressline1 varchar(30);
      declare @addressline2 varchar(30);
      declare @addressline3 varchar(30);
      declare @city varchar(20);
      declare @state varchar(2);
      declare @zipcode varchar(10);
      declare @countrycode varchar(3);
  
      declare @primarycontact varchar(10);
      declare @telephoneno varchar(17);
      declare @telephoneext varchar(5);
      declare @faxno varchar(17);
      declare @emailaddress varchar(250);
      declare @urladdress varchar(50);
      declare @termscode varchar(2);
      declare @reference varchar(15);
      declare @temporyvendor varchar(1);
      declare @creditcardvendor varchar(1);
  
      declare @holdpayment varchar(1);
      declare @electronicpayment varchar(1);
      declare @standardentryclass varchar(3);
      declare @exemptionnoOnFile varchar(1);
      declare @taxschedule varchar(9);
      declare @taxclass varchar(2);
      declare @accountkey varchar(9);
      declare @SeparateCheck varchar(1);
      declare @Comment varchar(30);
      declare @Sort varchar(10);
  
      declare @batchfax varchar(1);
      declare @UsePOReceiptOfInvForVendor varchar(1);
      declare @PrimaryPurchaseAddressCode varchar(4);
      declare @VendorType varchar(1);
      declare @Form1099 varchar(1);
      declare @TaxpayerIDSocialSecurityNo varchar(9);
      declare @Box1099 varchar(3);
      declare @MiscBox9 varchar(1);
      declare @LastPurchaseDate datetime;
      declare @LastPaymentDate datetime;
  
      declare @LastCheckNo varchar(10);
      declare @LastCheckAmt decimal(14, 2);
      declare @RetentionRate decimal(6, 2);
      declare @AverageDaysToPay decimal(3, 0);
      declare @AverageDaysOverDue decimal(3, 0);
      declare @balancedue decimal(15, 2);
      declare @NumberOfInvToUseInCalc decimal(7, 2);
      declare @datecreated datetime;
      declare @timecreated varchar(8);
      declare @UserCreatedKey varchar(10);
  
      declare @dateupdated datetime;
      declare @timeupdated varchar(8);
      declare @UserUpdatedKey varchar(10);
      declare @UDF_LONG_NAME varchar(60);
  
      declare @audit_Event varchar(10);
      declare @audit_log varchar(100);
      declare @dbname varchar(50);
  
      declare @Deletedbname varchar(50);
  
      declare @rowcount int;
      declare @TableName varchar(50);
      declare @TableFieldName varchar(50);
  
      declare @dbcounter int;
      declare @tablecounter int;
      declare @cnt INT ;
      DECLARE @selectVendorno varchar(75)
  
      select @apdivisionno =i.APDivisionNo from deleted i;    
      select @vendorno=i.VendorNo from deleted i; 
      select @vendorname=i.VendorName from deleted i;
      select @addressline1 =i.AddressLine1 from deleted i;    
      select @addressline2=i.AddressLine2 from deleted i; 
      select @addressline3=i.AddressLine3 from deleted i; 
      select @city =i.City from deleted i;    
      select @state=i.state from deleted i;   
      select @zipcode=i.zipcode from deleted i;   
      select @countrycode =i.countrycode from deleted i;  
      select @primarycontact=i.PrimaryContact from deleted i; 
      select @telephoneno=i.TelephoneNo from deleted i;   
      select @telephoneext =i.telephoneext from deleted i;    
      select @faxno=i.faxno from deleted i;   
      select @emailaddress=i.emailaddress from deleted i; 
      select @urladdress =i.urladdress from deleted i;    
      select @termscode=i.termscode from deleted i;   
      select @temporyvendor=i.TemporaryVendor from deleted i; 
      select @creditcardvendor =i.CreditCardVendor from deleted i;    
      select @holdpayment=i.holdpayment from deleted i;   
      select @electronicpayment=i.ElectronicPayment from deleted i;   
      select @standardentryclass=i.StandardEntryClass from deleted i; 
      select @exemptionnoOnFile=i.ExemptionNoOnFile from deleted i;   
      select @taxschedule=i.taxschedule from deleted i;   
      select @taxclass=i.taxclass from deleted i; 
      select @accountkey=i.accountkey from deleted i; 
      select @SeparateCheck=i.SeparateCheck from deleted i;   
      select @Comment=i.Comment from deleted i;   
      select @Sort=i.Sort from deleted i; 
      select @batchfax=i.batchfax from deleted i; 
      select @UsePOReceiptOfInvForVendor=i.UsePOReceiptOfInvForVendor from deleted i; 
      select @PrimaryPurchaseAddressCode=i.PrimaryPurchaseAddressCode from deleted i; 
      select @VendorType=i.VendorType from deleted i; 
      select @Form1099=i.Form1099 from deleted i;
      select @TaxpayerIDSocialSecurityNo=i.TaxpayerIDSocialSecurityNo from deleted i; 
      select @Box1099=i.Box1099 from deleted i;   
      select @MiscBox9=i.MiscBox9 from deleted i;
      select @LastPurchaseDate=i.LastPurchaseDate from deleted i; 
      select @LastPaymentDate=i.LastPaymentDate from deleted i;   
      select @LastCheckNo=i.LastCheckNo from deleted i;
      select @LastCheckAmt=i.LastCheckAmt from deleted i;
      select @RetentionRate=i.RetentionRate from deleted i;
      select @AverageDaysToPay=i.AverageDaysToPay from deleted i;
      select @AverageDaysOverDue=i.AverageDaysOverDue from deleted i;
      select @balancedue=i.balancedue from deleted i;
      select @NumberOfInvToUseInCalc=i.NumberOfInvToUseInCalc from deleted i;
      select @datecreated=i.datecreated from deleted i;
      select @timecreated=i.timecreated from deleted i;
      select @UserCreatedKey=i.UserCreatedKey from deleted i;
      select @dateupdated=i.dateupdated from deleted i;
      select @timeupdated=i.timeupdated from deleted i;
      select @UserUpdatedKey=i.UserUpdatedKey from deleted i;
      select @UDF_LONG_NAME=i.UDF_LONG_NAME from deleted i;
      select @Deletedbname= DB_NAME();
  
      set  @dbcounter =0;
      set  @tablecounter =0;
  
      DECLARE @sql NVARCHAR(MAX) 
      DECLARE @deleteSql NVARCHAR(MAX) 
  
      select @rowcount=count(*) from [Mas_Master].[dbo].[Master_Vendor] where vendorno=@vendorno and deleteflag=1;
  
  
  
      BEGIN
          if(@rowcount&lt;=0) 
          begin
                  Rollback;
                  DECLARE db_cursor CURSOR FOR  
                  SELECT [dbname] FROM [Mas_Master].[dbo].[lookup_dbName]
  
                  OPEN db_cursor   
                  FETCH NEXT FROM db_cursor INTO @dbname 
  
                  WHILE @@FETCH_STATUS = 0   
                  BEGIN   
  
                             DECLARE Table_cursor CURSOR FOR  
                             SELECT [TableName],[TableFieldName] FROM [Mas_Master].[dbo].[lookupTableName]
                             OPEN Table_cursor   
                             FETCH NEXT FROM Table_cursor INTO @TableName,@TableFieldName 
  
                             WHILE @@FETCH_STATUS = 0  
                             BEGIN   
  
  
                                  if(CONCAT(@dbname, '_', @TableName)&lt;&gt;CONCAT(@Deletedbname, '_', 'AP_VENDOR'))  /*Not Check For Same Database */
                                   Begin
  
                                      set @sql='Select  @cnt=count(*) from ' +@dbname+'.dbo.'+@TableName +' where '+ @TableFieldName +'= ''' + @vendorno + '''';
                                      EXECUTE sp_executesql @sql, N' @cnt int out', @cnt OUTPUT
  
                                      INSERT INTO Mas_master.dbo.testDelete
                                              VALUES
                                             (
                                             @dbname
                                             ,@TableName
                                             ,@vendorno,@sql,@cnt
                                             )
  
                                          if(@cnt&gt;0)
                                          Begin
                                              SET @tablecounter = @tablecounter + 1;
                                              break;  
                                          end
                                   End
  
  
  
                                  FETCH NEXT FROM Table_cursor INTO @TableName,@TableFieldName 
                             End
                             CLOSE Table_cursor;
                             DEALLOCATE Table_cursor;
                   SET @dbcounter = @dbcounter + 1;
                   FETCH NEXT FROM db_cursor INTO @dbname   
                  END 
               CLOSE db_cursor;
              DEALLOCATE db_cursor;
                  /* if no match vendor found on other database then mark master table vendor delete*/
                  if(@tablecounter=0)
                  begin
                         UPDATE [Mas_Master].[dbo].[Master_Vendor]
                           SET [DeleteFlag] = 1
                          where vendorno=@vendorno
                  End
  
          end
          else /* if delete Flag = 1 then delete vendor from current database table and master vendor table */
          Begin
                set @deleteSql= 'DELETE FROM '+@Deletedbname+'.dbo.AP_Vendor WHERE vendorno=@vendorno';
                EXECUTE sp_executesql @deleteSql, N'@vendorno nvarchar(75)', @vendorno = @vendorno
                DELETE FROM Mas_Master.dbo.Master_Vendor WHERE vendorno=@vendorno
          End
      End 
  </code></pre>
  
  <p>Please help me?</p>