nested trigger/multi-rows/wrong PKs returned

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

nested trigger/multi-rows/wrong PKs returned

Author

Topic

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-07 : 12:51:15

I'll try to be as clear as possible here; this is somewhat complex.

I have three tables Invoice, InvoiceDetail, and Revenues. There are several rows (generally) for each invoice in the InvoiceDetail table (PK = InvoiceID + LineItem), while there is one row for each invoice in the Invoice table (PK = InvoiceID). When an Insert or Update occurs on the InvoiceDetail table, a trigger fires an SP that updates the Invoice table with the new numbers (totals for the line items for the categories they are in, not important). I've set up an Insert trigger on the Invoice table so that every time a new record is inserted, a matching record will be inserted into the Revenues table (plus a row for each month of support being sold). The Insert trigger works fine, but the problem comes in on the Update trigger. The problem is that it becomes a nested trigger because every time the InvoiceDetail table is changed, its trigger is fired, updating Invoice, which then fires the Update trigger for Invoice->Revenues. The problem is not that the trigger is fired, but that random InvoiceID's are passed from the trigger to the SP that runs. I'm guessing the problem is that multiple rows are returned as INSERTED because of the nesting. My code is below. You can ignore the part at the bottom about inserting since that works fine.

CREATE TRIGGER trUpdInvoice ON dbo.Invoice
AFTER UPDATE
AS

DECLARE @invrow int

SELECT @invrow = invoiceid FROM DELETED

EXEC spNewInvoice @invrow

CREATE PROCEDURE spNewInvoice(@invrow int) AS

update invoice
set invoicemonth = t3.monthid
from (select * from quarters t1, invoice t2
where datepart(Mm, t1.monthname) = datepart(Mm, t2.invoicedate) and
datepart(Yy, t1.monthname) = datepart(Yy, t2.invoicedate)) t3
where invoice.invoiceid = t3.invoiceid

update invoice
set invsprtduration = 0
where invsupport = 0
update invoice
set invsprtduration = 12
where invsupport <> 0

declare @revid int
select @revid = isnull(max(revenueid), 0) from revenues
set @revid = @revid + 1

declare @invid int, @invdealid int, @invmonth smallint, @invwrnty tinyint, @invproduct money, @invpsproduct money,
@invprofsvcs money, @invintegration money, @invtraining money, @invsupport money,
@invother money, @invduration tinyint
select @invid = invoiceid,@invdealid = invdealid,@invmonth = invoicemonth, @invwrnty = invwrntylength,
@invproduct = invproduct, @invpsproduct = invpsproduct, @invprofsvcs = invprofsvcs, @invintegration = invintegration,
@invsupport = invsupport, @invtraining = invtraining, @invother = invother, @invduration = invsprtduration
from invoice where invoiceid = @invrow --(select max(invoiceid) from invoice)

declare @oldrevid int
declare @i int
set @i = 1
set @oldrevid = (select min(revenueid) from revenues where revinvoiceid = @invid)

-- IF THIS IS NOT THE FIRST TIME THIS INVOICE HAS BEEN ENTERED, THEN UPDATE
if exists(select * from revenues where revinvoiceid = @invid)
begin
update revenues
set revenueid = @revid,revdealid = @invdealid,revenuemonth = @invmonth,revinvoiceid = @invid,
revproduct = @invproduct,revpsproduct = @invpsproduct,revprofsvcs = @invprofsvcs,revintegration = @invintegration,
revtraining = @invtraining,revother = @invother
where revenueid = @oldrevid

if (@invsupport > 0)
begin
set @invmonth = @invmonth + @invwrnty
set @invsupport = round(@invsupport / @invduration, 2)
while @i <= @invduration
begin
set @revid = @revid + 1
update revenues
set revsupport = @invsupport
where revenueid = @revid and revinvoiceid = @invrow
--set @invmonth = @invmonth + 1
set @i = @i + 1
end
end
end
-- IF THIS IS THE FIRST TIME, THEN INSERT NEW ROWS
else
begin
insert into revenues(revenueid,revdealid,revenuemonth,revinvoiceid,revproduct,revpsproduct,revprofsvcs,revintegration,
revtraining,revsupport,revother)
values(@revid, @invdealid, @invmonth, @invid, @invproduct, @invpsproduct, @invprofsvcs, @invintegration, @invtraining, 0, @invother)

if (@invsupport > 0)
begin
set @invmonth = @invmonth + @invwrnty
set @invsupport = round(@invsupport / @invduration, 2)
while @i <= @invduration
begin
set @revid = @revid + 1
insert into revenues(revenueid,revdealid,revenuemonth,revinvoiceid,revsupport)
values(@revid, @invdealid, @invmonth, @invid, @invsupport)
set @invmonth = @invmonth + 1
set @i = @i + 1
end
end
end

GO

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-07 : 14:54:35

I figured it out. It may not be the best solution, but it works great in this case. I added the following lines to the beginning of the update trigger on the Invoice table:

IF (SELECT COUNT(*) FROM INSERTED) > 1
BEGIN
RETURN
END

khilton
Starting Member

1 Post

Posted - 2004-10-13 : 11:22:29

FYI, instead of using "Select Count(*) from Inserted" , you can reference the server variable @@ROWCOUNT to get the number of rows affected. It does the same thing, just a little cleaner.

Example:
IF @@ROWCOUNT > 1
exec myProc

quote:
Originally posted by influent

I figured it out. It may not be the best solution, but it works great in this case. I added the following lines to the beginning of the update trigger on the Invoice table:

IF (SELECT COUNT(*) FROM INSERTED) > 1
BEGIN
RETURN
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources