Re: Seeking help w/ Triggers

From: Campbell White <Preveal2_at_vni.net>
Date: 1996/08/16
Message-ID: <32147CDD.430C_at_vni.net>#1/1


There's a wealth of information on triggers, stored procedures, etc. in "Reveal for PL/SQL". You can download a copy and try it out from the RevealNet website: http://www.revealnet.com

Best regards,

Cam White
RevealNet, Inc.

Dan Roberts wrote:
>
> 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 Fri Aug 16 1996 - 00:00:00 CEST

Original text of this message