CODEWITHSUNDEEP
sqloracle-database
plop
plop

Reputation: 366

oracle sql trigger loop update and delete

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.

Upvotes: 0

Views: 288

Answers (1)

Ben
Ben

Reputation: 1927

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.

Upvotes: 1

Related Questions