?
Solved

How do I prevent a recursive Interbase trigger

Posted on 2010-09-23
2
Medium Priority
?
1,272 Views
Last Modified: 2013-12-09
How do I update multiple rows in a table using a trigger without encounting a recursive trigger scenario? For instance, you change the price for an item in Group 1, the idea would be for the trigger to set the price for every other item in the table in Group 1 to the same price. However, this is an easy scenario to encounter a recursive trigger when running an update statement on the same table. Any ideas on how to accomplish this with a trigger or trigger/procedure?

Example:

Item     Price     Group
Item1   1.00      1
Item2   2.00      2
Item3   1.00      1

Edit price for Item 1 to 3.00 should also change Item3 to 3.00.
0
Comment
Question by:PilotAdmin
2 Comments
 
LVL 19

Accepted Solution

by:
NickUpson earned 1500 total points
ID: 33752300
in an after update trigger

if exists (select 1 from mytable where group = new.group and price <> new.price)) then
  update mytable set price = new.price where group = new.group;

this is untested pseudo code, but you should get the idea






0
 

Author Closing Comment

by:PilotAdmin
ID: 33798466
After update suggestion worked.. had to manage it a bit different due to the example I provided being a little oversimplified (we have a lot more going on in the price table that I didn't go into), but it did get me going down the right track. Thanks!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question