Re: Trigger' s Problem
Date: Tue, 6 Dec 1994 10:07:24
Message-ID: <mcallister.16.000A1FE5_at_neptune.grad.missouri.edu>
In article <3c20e8$sn5_at_opuskl.kloeckner-hb.de> am_at_iaeb.kloeckner-hb.de (Markfort) writes:
>Path: golf!zombie.ncsc.mil!news.mathworks.com!panix!bloom-beacon.mit.edu!gatech!howland.reston.ans.net!Germany.EU.net!kloeckner-hb.de!iaeb!am
>From: am_at_iaeb.kloeckner-hb.de (Markfort)
>Newsgroups: comp.databases.oracle
>Subject: Re: Trigger' s Problem
>Date: 6 Dec 1994 15:36:40 GMT
>Organization: Kloeckner Stahl GmbH
>Lines: 21
>Sender: am_at_iaeb (Markfort)
>Distribution: world
>Message-ID: <3c20e8$sn5_at_opuskl.kloeckner-hb.de>
>References: <3bvbg5$d6c_at_master.di.fc.ul.pt>
>NNTP-Posting-Host: iaeb.kloeckner-hb.de
>In article <3bvbg5$d6c_at_master.di.fc.ul.pt>, barreto_at_master.di.fc.ul.pt (Joao Barreto Fernandes) writes:
>|> 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.
>|>
>There is no way!
>--------------------------------------------------------------------------------
>Andreas Markfort private: am_at_megatel.de
>Be what you want to be office: am_at_kloeckner.de
>--------------------------------------------------------------------------------
There is a way!
Set your trigger to be a BEFORE INSERT trigger. Do whatever you want to
the data and then when the trigger is finished, the row is inserted. See
sample below:
CREATE TRIGGER contact_insert_row
BEFORE
INSERT
ON contact
FOR EACH ROW
DECLARE
seq_temp NUMBER;
BEGIN
select contact_seq.nextval into seq_temp from dual;
:new.contactkey := seq_temp;
:new.creator := USER;
:new.created := SYSDATE;
END;
This trigger will set the key value in the row to a unique sequence number,
then sets the creator column to the id of the user inserting the row, then the
created column to the date and time the row was inserted.
You could also manipulate other columns if you wanted.
Look at your PL/SQL guide for lots more examples.
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 Tue Dec 06 1994 - 10:07:24 CET