oracle sql trigger loop update and delete

Question

I have a problem with trigger on oracle.

I have something like that:

 Project
 -------
 currentProgress
 plannedLoads
 currentLoads


Step
----
currentProgress
plannedLoads
currentLoads


Task
----
currentProgress
plannedLoads
currentLoads

A project is composed by Step and Step is composed by Task.

currentProgress is always = currentLoads/plannedLoads.

I have a trigger before insert on Task, to improve Step currentLoads when inserting, updating or deleting, and one another on Step currentLoads to improve Project currentLoads.

So two triggers are called if I update Task, one to update Step, and then one on Project.

When I update Step for example, I update the currentLoad of it.

The problem is when I delete a project. I must delete also steps and tasks associated with. So triggers on Task and Step on delete are called, recalling the one on Project.

I'm not sure I'm clear. Ask me details if not.

Thanks for your help.

Ben · Accepted Answer · 2013-06-16 10:25:07Z

1

This does not sound like an ideal scenario for triggers - they're better off used for additional validation (i.e. things that cannot be accomplished with constraints, not instead of constraints) / logging / sanity checks, etc. rather than application logic.

In addition, I would only use triggers for validation sparingly and as a complement to constraints and application validation.

I would strongly suggest moving the functionality for maintaining these records into a PL/SQL procedure, and invoke that instead of issuing DML statements against the tables directly. That way, you stay in control.

edited Jun 16, 2013 at 10:25

answered Jun 16, 2013 at 8:22

Ben

1,92717 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ben Over a year ago

Why are triggers better off used for validation? That's what constraints are for. A trigger is best used for altering data when performing DML on a table or for keeping other tables updated once the first has been.

Ben Over a year ago

Triggers with application logic are basically hidden behaviour. You have to put extra effort in to ensure they fire in desirable order and you incur the additional context switching cost for per-row triggers. Constraints are a safeguard to ensure data integrity, triggers can give you extra power on top of that. If all you want to do is delete rows from one table when updates are made to another, surely a procedure is the cleanest and most maintainable way to do that.

Ben Over a year ago

I'm not disputing your suggestion for a procedure! Merely your wording of the first sentence. Triggers disguise you not doing DML correctly they don't validate data, which is what constraints are used for. If you validate in a trigger something is bound to go wrong at some point.

Ben Over a year ago

Point taken, I'll clarify.

APC Over a year ago

It's the battle of the Bens!

Collectives™ on Stack Overflow

oracle sql trigger loop update and delete

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related