Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: triggers
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