Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: triggers

Re: triggers

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Thu, 24 Aug 2000 00:20:41 +0200
Message-ID: <967069141.21340.4.pluto.d4ee154e@news.demon.nl>

CREATE OR REPLACE TRIGGER MY_TABLE_t
 before INSERT or UPDATE or DELETE on AMEUBLEMENT for each row  DECLARE
 U_N VARCHAR2(30):= user;
 T_N VARCHAR2(50):= 'MY_TABLE';
action varchar2(20);
 BEGIN
if inserting then
  action := 'insert';
elsif updating then
  action := 'update';
else action := 'delete';
end if;
 insert into LOG_TABLE values (U_N, T_N, :old.<primary key column>, action, SYSDATE);  END;
 /

Sorry for rearranging your code. Your version will be using three implicit cursors (one for every insert) and conduct unnecessary tests. I made these changes for instructional purposes, not to mock you. You'll need to add a column to the logtable containing the primary key of the master table;

Hth,

Sybrand Bakker, Oracle DBA

<louis.audet_at_ssss.gouv.qc.ca> wrote in message news:8o1c1u$d78$1_at_nnrp1.deja.com...
> Hi, I have a trigger to keep a log of people in my app. the trigger
> goes like this :
>
> CREATE OR REPLACE TRIGGER MY_TABLE_t
> AFTER INSERT or UPDATE or DELETE on AMEUBLEMENT
> DECLARE
> U_N VARCHAR2(50):= user;
> T_N VARCHAR2(50):= 'MY_TABLE';
> BEGIN
> IF INSERTING THEN
> insert into LOG_TABLE values (U_N, T_N, 'insert', SYSDATE);
> END IF;
> IF UPDATING THEN
> insert into LOG_TABLE values (U_N, T_N, 'update', SYSDATE);
> END IF;
> IF DELETING THEN
> insert into LOG_TABLE values (U_N, T_N, 'delete', SYSDATE);
> END IF;
>
> END;
> /
>
>
> Here's a desc of the LOG_TABLE :
>
> Name Null? Type
> ----------------------------------------- -------- ------------
> USER NOT NULL VARCHAR2(30)
> TABLEACTION NOT NULL VARCHAR2(30)
> ACTION NOT NULL VARCHAR2(25)
> DATEACTION NOT NULL DATE
>
> For this it works well but now i'd also like to keep the PK (sometimes
> more than one...) of the occurence consulted.
>
> How can I do that ?
>
>
> Thanks, Louis
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed Aug 23 2000 - 17:20:41 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US