Posted by Mark Flippin on 09/30/17 11:33

I'm evidently not understanding nested triggers and I'm looking for
some help.

I've an Invoice table (see below) that I want to enforce two actions
via after triggers.

The first trigger maintains a set of audit columns in the table
indicating the date on which the row was inserted, the date of the
last change, and the user who made the last change (see below)

The second trigger maintains the invoice number across revisions. A
specific invoice for a contract may have multiple rows as revisions
are made during processing. So if a change is made to an invoice
number in one row, the trigger makes sure that all the associated
revision rows are updated with the new invoice number. (see below)

If I implement these as two triggers, I hit the 32 level limit and I
don't understand why.

Regardless of the order (and I haven't set the first trigger so the
order is undefined) I would expect that my second trigger would
execute only once; that after one execution, there are no rows with
the old invoice number and its execution would NOT initiate the first
trigger.

So I expect to get three or four levels of trigger execution depending
on the order.

But with two triggers, it consistently reports exceeding the 32 level
limit restriction.

I've combined the processing into one trigger to get around this
problem (with some small modifications to process the audit columns),
but I really would like to understand what's going on.

Any help would be appreciated.


*******
******* Abbreviated table def
*******
CREATE TABLE [tblInvoices] (
[Inv_id] [int] IDENTITY (1, 1) NOT NULL ,
[Contract_Info_ID] [int] NOT NULL ,
[Invoice_No] [varchar] (50) NOT NULL ,
[Invoice_Revision] [int] NOT NULL ,
[inv_audit_entry_date] [datetime] NULL ,
[inv_audit_change_date] [datetime] NULL ,
[inv_audit_change_user] [varchar] (255) NULL ,
[inv_timestamp] [timestamp] NULL ,
CONSTRAINT [PK_tblInvoices] PRIMARY KEY CLUSTERED
(
[Inv_id]
) ON [PRIMARY] ,
CONSTRAINT [FK_tblInvoices_tblContractInformation] FOREIGN KEY
(
[Contract_Info_ID]
) REFERENCES [tblContractInformation] (
[Contract_Info_ID]
)
) ON [PRIMARY]
*******
*******
********************************************************
********
******** First Trigger - Audit column trigger
********
declare @wrkDate datetime
set @wrkDate = GETDATE()

declare @wrkUser varchar(255)
set @wrkUser = SUSER_SNAME()

update tblInvoices
set inv_audit_entry_date = coalesce(t1.entryDate, @wrkDate),
inv_audit_change_date = @wrkDate,
inv_audit_change_user = @wrkUser
from (select i1.inv_id as entryID, d1.inv_audit_entry_date as
entryDate
from inserted as i1
left join deleted as d1 on d1.inv_id = i1.inv_id) as t1
where inv_id = t1.entryID
*******
*******
********************************************************
*******
******* Second Trigger - Invoice Number
*******
update tblInvoices
set invoice_no = t1.newInvoiceNo
from (select i1.contract_info_id as ContractInfoID, i1.invoice_no
as newInvoiceNo, d1.invoice_no as oldInvoiceNo
from inserted as i1
inner join deleted as d1 on d1.inv_id = i1.inv_id
where d1.invoice_no <> i1.invoice_no) as t1
where contract_info_id = t1.ContractInfoID
and invoice_no = t1.oldInvoiceNo
*******
*******

[Back to original message]