Nested trigger not always firing

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

General SQL Server Forums

New to SQL Server Programming

Nested trigger not always firing

Author

Topic

midavis
Starting Member

23 Posts

Posted - 2011-07-26 : 09:19:09

I have a scenario with 3 tables

Table 1 - Holds all claims
Table 2 - Holds multiple coverages for a claim (i.e. comp and collision)
Table 3 - Holds all payments for coverages

When a payment is made a trigger fires to update the total amount for that coverage in Table 2. When table 2 is update a different trigger fires to update the total amount for the claim. This does seem to be working, however the second trigger to update table 1 does not always fire or it seems to be getting old data. It is like it is using data before the first trigger caused an update. The triggers are below.

ALTER TRIGGER [dbo].[Table3_UpdateTotals] ON [dbo].[Table3] AFTER INSERT , UPDATE, DELETE AS

DECLARE @Table2ID int
DECLARE @ids TABLE (Table2ID int)

-- add all of our deletions then additions
INSERT INTO @ids SELECT Table2ID From deleted
INSERT INTO @ids SELECT Table2ID From inserted

DECLARE liTotalUpdate_cursor CURSOR FOR
SELECT DISTINCT Table2ID
FROM @ids

OPEN liTotalUpdate_cursor
FETCH NEXT FROM liTotalUpdate_cursor INTO @Table2ID

WHILE @@FETCH_STATUS = 0
BEGIN

exec usp_Table2_UpdateTotals @Table2ID

FETCH NEXT FROM liTotalUpdate_cursor INTO @Table2ID

END

CLOSE liTotalUpdate_cursor
DEALLOCATE liTotalUpdate_cursor

ALTER TRIGGER [dbo].[trgTable2_UpdateTotals] ON [dbo].[Table2] AFTER INSERT , UPDATE, DELETE AS

DECLARE @Table1Id int
DECLARE @ids TABLE (Table1ID int)

--if EXISTS(Select * from inserted)
-- INSERT INTO @ids SELECT Table1ID From inserted
--else
-- INSERT INTO @ids SELECT Table1ID From deleted

-- add all of our deletions then additions
INSERT INTO @ids SELECT Table1ID From deleted
INSERT INTO @ids SELECT Table1ID From inserted

DECLARE Table1TotalUpdate_cursor CURSOR FOR
SELECT DISTINCT Table1ID
FROM @ids

OPEN Table1TotalUpdate_cursor
FETCH NEXT FROM Table1TotalUpdate_cursor INTO @Table1Id

WHILE @@FETCH_STATUS = 0
BEGIN

exec usp_Table1_UpdateTotals @Table1Id

FETCH NEXT FROM Table1TotalUpdate_cursor INTO @Table1Id

END

CLOSE Table1TotalUpdate_cursor
DEALLOCATE Table1TotalUpdate_cursor

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-26 : 11:37:40

why are you using cursors in trigger? Cant you go for set based approach? If you can let us know the rules then somebody will be able to help you out with setbased solution.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

midavis
Starting Member

23 Posts

Posted - 2011-07-26 : 13:52:19

The reason is because I have to fire a stored procedure for each unique id I get. I wish I wasn't using a cursor

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-26 : 14:38:07

There are two reasons I can think of why the triggers do not behave the way you expect them to.

First, which is unlikely, is that the NESTED TRIGGER option is turned off. I say that is unlikely because by default it is ON, and unless someone turned it off, that is probably not the cause. In any case, it may be worth looking into - details are here: http://msdn.microsoft.com/en-us/library/ms190739.aspx

The second and more likely cause is some type of logical error. You effectively have nested cursors - which means for each invocation of the first trigger the second trigger may be invoked multiple times. Whether the state of the data when that happens is what you expect it to be can be determined only after examining the logic in the stored procedures.

This is where Vishakh's suggestion about avoiding the cursors altogether comes in. More often than not, you may be able to accomplish everything that is done in the stored procedures and the two cursors via set-based queries/updates. Of course, you know your business logic and code better than I do, so may be this is the best you can do; I don't want to pretend otherwise.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-07-26 : 15:58:23

>> I have a scenario with 3 tables <<

Every SQL forum or newsgroup expects that you will post DDL, sample data and clear specifications which might might include output. This is explained in the FAQ section. We can not read minds; what would we need to know to do your job for you?

Tables must have keys and should have DRI, constraints, and all the basic features of a schema. You should know use ISO-8601 Standards for temporal data, avoid needless dialect, basic data modeling and use ISO-11179 Standards for data element names. Please tell us if you can change the DDL.

>> Table 1 - Holds all claims
Table 2 - Holds multiple coverages for a claim (i.e. comp and collision)
Table 3 - Holds all payments for coverages <<

Why did you not call the table of claims “Claims” and so forth?

>> When a payment is made a trigger fires to update the total amount for that coverage in Table 2. <<

You have more triggers in the one procedure than I have written in 3 decades of writing SQL. What you are doing is fundamentally wrong; you are mimicking a 1950's mag tape file. Your silly local table variable is a scratch tape!

You also did this in the worst. I am not sure what a “coverage_id” would be in a relational model, but why use two INSERTs when one will do? Let me answer my won question: to your mindset, every table is a magnetic tape that has to be mounted, then read. In sequence, one at a time.

INSERT INTO @Scratch_Tape
SELECT coverage_id FROM DELETED
UNION
SELECT coverage_id FROM INSERTED;

Oh, since you used a UNION, the next statement in this pseudo-tape program does not need a SELECT DISTINCT. And sorting a tape usually makes a tape merge faster.

DECLARE Sorted_Tape_Cursor CURSOR FOR
SELECT coverage_id
FROM @Scratch_Tape
ORDER BY coverage_id;

What is happening with a magical stored procedure? First of all, the ISO-11179 rules would not allow the “usp_” prefix. The correct format would be “<verb>_<object>”. This is a great way to assure that this mess cannot ever be optimized or maintained.

EXEC Update_Coverage_Totals @local_coverage_id;

>> When Coverages is update a different trigger fires to update the total amount for the claim. <<

Triggers on triggers! The locks required to assure the ROLLBACK will shut down the whole system, not just your applications. But you are finding out why tape systems worked; a tape is not shared like a table.

An SQL programmer would get the total payments agaisnt each claim and put all this in a VIEW. The view will not need to be updated and it will always be correct. It will exist as a declaration, an abstraction. Not as a mag tape.

In the insurance RDBMS systems I know, Claims should reference a policy. A claim references an action (payment, denial, etc.) There would be no procedural code. Let me say that again NO procedural code. The whole idea of SQL is that it is a declarative language.

You are so far off that you need more help than a forum or newsgroup can give you.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

midavis
Starting Member

23 Posts

Posted - 2011-07-27 : 11:14:44

The reason these totals aren't in a view is because my boss doesn't want them in there. You don't always know the situation in which things need to be done, so please try to offer advice instead of lashing out at people. People come here to get advice and to learn how to do things better which is what I am trying to do. I know that I am not an awesome sql programmer, but I am trying to learn to be a better one. I assumed that using two triggers would make things easier in the end. I will switch this to a set based update and make sure a trigger within a trigger does not happen.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources