Re: Trigger' s Problem

From: Andrew McAllister <mcallister_at_neptune.grad.missouri.edu>
Date: Mon, 5 Dec 1994 12:16:16
Message-ID: <mcallister.14.000C45C4_at_neptune.grad.missouri.edu>

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. Received on Mon Dec 05 1994 - 12:16:16 CET