Re: Seeking help w/ Triggers

In article <Roberts_Daniel_G.PriLVMS3-1508961621440001_at_riversend.bms.com>, Roberts_Daniel_G.PriLVMS3_at_MSMAIL.BMS.COM says...
>
>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
>

You've almost got it. If i were doing it, i would probably use three triggers, one for each type of change, inserts, updates and deletes (you probably want to use at least two triggers anyway, since old values are undefined for inserts and new values are undefined for deletes). After that it's straightforward, generate your new sequence number, and do your insert.

-- 
Tony Noble
tnoble_at_mt.gov
Opinions expressed do not necessarily reflect those of my employer.