| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Trigger on multiple rows
On Thu, 30 Dec 1999 16:53:03 -0500, Sameer Utrankar
<sameeru_at_exchange.ml.com> wrote:
><!doctype html public "-//w3c//dtd html 4.0 transitional//en">
><html>
>Triggers work fine when validation is expected to be performed on a row
>at a time. But how about when validation is based on several rows ?
><br>For example,
><br>Say a team (group of employees) work with every customer and they each
>get some % credit for every deal made with the client. Total % for the
>team should be 100. Where do you put this check ? It can't be when one
>team member row gets saved. Check should be after all the team member rows
>for the team get saved. Now, how do you do that ?
><br>
><br>
><br> </html>
>
triggers exist on row level (add for each row to trigger definition)
and statement level (the default).
Your trigger is an example of a statement level trigger.
This should be no problem, however you probably need to maintain the
credit dynamically, as you are not allowed to select from a mutating
table.
This would imply you need to the following
create a before (statement) trigger initializing the total to 0 (where
total is a variable in a package)
create an after statement for each row, synchronizing the total
create an after statement (not for each row), checking the total.
Hth,
Sybrand Bakker, Oracle DBA Received on Mon Jan 03 2000 - 12:30:51 CST
 |
 |