Re: Seeking help w/ Triggers

        I am undertaking a very similar project myself and so this thread could be very useful. My idea is to do everything in the trigger since stored procedures do not have access to the values that I am interested in storing. Remember that new and old are not defined for all transaction types (ie. old.ssn is not defined for inserts, new.ssn is not defined for deletes).

        I am not sure how the CHECK clause can be used to capture the change_type. Instead I am creating a local variable and using the transactional variables to assign a value to it.

CREATE OR REPLACE TRIGGER EMP_AUDIT_TRIGGER AFTER INSERT OR DELETE OR UPDATE ON EMPLOYEE DECLARE

trans_type	VARCHAR2(1)
old_address	EMPLOYEE.ADDRESS%TYPE;
new_address	EMPLOYEE.ADDRESS%TYPE;

BEGIN IF INSERTING THEN
trans_type := 'I';
old_address := OLD.address;
new_address := NEW.address;
ENDIF; /* similar logic for inserting & deleting */

INSERT INTO AUDIT_TABLE
VALUES (auditseq_id,

	old_address,
	new_address,,,,);

END;
/

The question I have is does anybody know how to capture an ORACLE transaction sequence so that I know if the rows belonged to one or more transactions and if rows in other tables belonged to the same transaction?

Roberts_Daniel_G.PriLVMS3_at_MSMAIL.BMS.COM (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 Mon Aug 19 1996 - 00:00:00 CEST