Re: Trigger' s Problem

In article <mcallister.14.000C45C4_at_neptune.grad.missouri.edu>, mcallister_at_neptune.grad.missouri.edu (Andrew McAllister) says:
>
>In article <3bvbg5$d6c_at_master.di.fc.ul.pt> barreto_at_master.di.fc.ul.pt (Joao Barreto Fernandes) writes:
>>From: barreto_at_master.di.fc.ul.pt (Joao Barreto Fernandes)
>>Subject: Trigger' s Problem
>>Date: 5 Dec 1994 16:27:01 +0100
 

>> Does anyone know if one can use triggers on a table to detect
>>insert,etc and during its execution make others alterations on that same
>>table ?
 

>> I tried it several times but I always get the same message - something
>>like ' table is mutating - no can do !! .
 

>> Please, Help.
>> Thanks in advance, Joao Barreto Fernandes
 

>>P.S. Please answer by e-mail if possible.
 

>>
>>--
>># Joao Carlos de Oliveira Barreto Fernandes #
>># e-mail : barreto_at_master.di.fc.ul.pt #
>># <a href=http://www.di.fc.ul.pt/~barreto> </a> #
>
>You cannot change a table or select information from it with a
>trigger after an insert.
>
>BUT! You can do it BEFORE the INSERT.
>Here is a sample trigger. It assumes that you have created a sequence
>named emp_seq (this will be used as the primary key), and that the
>empkey, creator, created columns exist and are of the appropriate
>type. USER and SYSDATE are Oracle constants. See your PL/SQL User's
>guide for more information and lots of examples.
>
>CREATE TRIGGER emp_insert_row
>BEFORE
>INSERT
>ON emp
>FOR EACH ROW
>DECLARE
>seq_temp NUMBER;
>BEGIN
>select emp_seq.nextval into seq_temp from dual;
>:new.empkey := seq_temp;
>:new.creator := USER;
>:new.created := SYSDATE;
>END;
>
>
>Andy
>Andrew McAllister -- mcallister_at_neptune.grad.missouri.edu
>Office of Research, University of Missouri-Columbia
>The views above are not those of my employer.

The mutating table restriction does not apply to after set triggers. Therefor you can load the row ids into a persistent variable within a packaged proc using a before/after insert row trriger and the process these with a proc in the same package using an after set trigger.

CHEERS! Received on Thu Dec 08 1994 - 01:53:41 CET