Nested Triggers

[Mark Flippin]

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_I D] [int] NOT NULL ,
[Invoice_No] [varchar] (50) NOT NULL ,
[Invoice_Revisio n] [int] NOT NULL ,
[inv_audit_entry _date] [datetime] NULL ,
[inv_audit_chang e_date] [datetime] NULL ,
[inv_audit_chang e_user] [varchar] (255) NULL ,
[inv_timestamp] [timestamp] NULL ,
CONSTRAINT [PK_tblInvoices] PRIMARY KEY CLUSTERED
(
[Inv_id]
) ON [PRIMARY] ,
CONSTRAINT [FK_tblInvoices_ tblContractInfo rmation] FOREIGN KEY
(
[Contract_Info_I D]
) REFERENCES [tblContractInfo rmation] (
[Contract_Info_I D]
)
) 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.ent ryDate, @wrkDate),
inv_audit_chang e_date = @wrkDate,
inv_audit_chang e_user = @wrkUser
from (select i1.inv_id as entryID, d1.inv_audit_en try_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_inf o_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_i d = t1.ContractInfo ID
and invoice_no = t1.oldInvoiceNo
*******
*******

[Erland Sommarskog]

Mark Flippin (me******@comca st.net) writes:

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.

As if the trigger would care about that. :-)

Add this to both triggers:

IF @@rowcount = 0
RETURN

Triggers fires even if no rows are affected.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx