| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: TRIGGER BEFORE UPDATE
On Tue, 17 Dec 1996 14:40:20 +0100, Wolf Kammer <uzr10a_at_work6c.rhrz.uni-bonn.de> wrote:
>Hallo to who is looking at this,
>
>I must set the value of the columne "status" to 'C' ("Changed") as
>soon as an other column has been updated. My trigger
>
>CREATE OR REPLACE TRIGGER htbvz
>
> BEFORE UPDATE ON table
> FOR EACH ROW WHEN (new.bvz = 'Y')
> BEGIN
> UPDATE table SET status='C' WHERE :new.bvz='Y';
> END;
>/
>
>does not work since the table is mutating, that is being modified
>by the update. How can one do this simple thing?
>
>Thanks for any help, Wolf Kammer
>
CREATE OR REPLACE TRIGGER htbvz
BEFORE UPDATE ON table
FOR EACH ROW WHEN (new.bvz = 'Y')
BEGIN
:new.status = 'C';
END;
The thing to be cautious about in the trigger you defined above is
that it is a row trigger instead of a statement trigger (by virtue of
the FOR EACH ROW option). Thus, the trigger fires once for each row
of the table that is affected by the triggering statement.
If you execute the statement :
UPDATE table SET bvz='Y'
and this updates 100,000 rows, the UPDATE statement in the trigger would be executed 100,000 times. Not always a good thing. Joel
Joel R. Kallman See Oracle technology in action! Oracle Government http://govt.us.oracle.comBethesda, MD
 |
 |