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)
