Seeking help w/ Triggers

From: Dan Roberts <Roberts_Daniel_G.PriLVMS3_at_MSMAIL.BMS.COM>
Date: 1996/08/15
Message-ID: <Roberts_Daniel_G.PriLVMS3-1508961621440001_at_riversend.bms.com>#1/1


Hi Netters

I am trying to learn about triggers. What I am trying to do is log the changes which I may do on my employee table into a empaudit table. The empaudit table would keep track of any information which was I/U/D from my employee table and the type of change would be tracked by the change_type attribute somehow using the CHECK clause. Well I am lost..I know what I need to do..but I am lost when it comes to the SQL/PL level and putting all these pieces togther.

I have created a sequence auditseqid which I think should give a unique number to each empaudit tuple when the trigger is called right??

SQL> create sequence auditseqid increment by 1 start with 0001;

Sequence created.

Now the trigger I created is

create or replace trigger logempchange
after insert or update or update on employee begin
insert into empaudit
values(auditseqid,ssn,old_address,old_salary,old_superssn,old_dno,new_address, new_salary,new_superssn,new_dno,timestamp); end;

Now I am sure that this is giving you a good laugh!! But basically this is what I would like to do..Would I have to do these changes using a call to a procedure or, can all the work I need to do be done in the body of the trigger?? Do I have to create a bunch of nested if then else stmsts which
would take into account an Insert ,update or delete??? I hope my note makes sense and you can help me out!!!..Thanks again..very much for all your help!>>Dan

I have the tables listed below

SQL> desc empaudit;

 Name                            Null?    Type

------------------------------- -------- ----
AUDITSEQID NOT NULL NUMBER(5) SSN NOT NULL CHAR(9) CHANGE_TYPE NOT NULL CHAR(1) OLD_ADDRESS VARCHAR2(30) OLD_SALARY NUMBER(10) OLD_SUPERSSN CHAR(9) OLD_DNO CHAR(3) NEW_ADDRESS VARCHAR2(30) NEW_SALARY NUMBER(10) NEW_SUPERSSN CHAR(9) NEW_DNO CHAR(3) TIMESTAMP NOT NULL DATE SQL> desc employee; Name Null? Type
------------------------------- -------- ----
FNAME NOT NULL CHAR(15) MINIT CHAR(1) LNAME NOT NULL CHAR(15) SSN NOT NULL CHAR(9) BDATE DATE
ADDRESS CHAR(30) SEX CHAR(1) SALARY NUMBER(7) SUPERSSN CHAR(9) DNO NUMBER(1)
-- 
Dan Roberts
Received on Thu Aug 15 1996 - 00:00:00 CEST

Original text of this message