SQL & PL/SQL

Hey guys,

I'm trying to simulate a delete operation through using an update on a trigger my tables are

CREATE TABLE EMPLOYEE (
LNAME VARCHAR(15) NOT NULL,
SSN CHAR(9) NOT NULL,
salary FLOAT,
dno INT NOT NULL,
vst DATE,
vet DATE,
PRIMARY KEY (Ssn));

CREATE TABLE EMP_HIST (
LNAME VARCHAR(15) NOT NULL,
SSN CHAR(9) NOT NULL,
salary FLOAT,
dno INT NOT NULL,
vst DATE,
vet DATE,
PRIMARY KEY (Ssn, VST));

CREATE TABLE EMPLOYEE_C (
LNAME VARCHAR(15) NOT NULL,
SSN CHAR(9) NOT NULL,
salary FLOAT,
dno INT NOT NULL,
vst DATE,
vet DATE,
PRIMARY KEY (Ssn));

What I want to do is whenever there is an update on vet( valid end time) in employee, delete the values from the employee table and insert the old values from employee into the emp_history table along with the new value for vet. Here are my triggers,

CREATE TRIGGER trig4
AFTER UPDATE OF VET ON EMPLOYEE
BEGIN
UPDATE Employee_c SET vet = :new.vet WHERE ssn = :new.ssn;
INSERT INTO EMP_HIST VALUES(:old.LNAME, :old.SSN, :old.salary, :old.dno, :old.vst, :new.vet);
END trig4;

CREATE TRIGGER trig44
AFTER update on EMPLOYEE_c
for each row
BEGIN
delete from EMPLOYEE WHERE (ssn = :new.ssn);
END trig44;

Here's what I'm updating the tables with
UPDATE Employee
SET vet = '04-JAN-05'
WHERE ssn = '111111121';

When the above statement is done I want it to DELETE the value from employee and put it into EMP_HIST. I've created a temporary table which acts as a buffer so I can update the tables when given a new vet. I'm doing this because I read that this problem can be avoided with a temporary table, but this isn't working. The problem is I consistently run into a mutating change error, I'd like to know how if at all possible a delete can be done in a trigger without getting a mutating change. I can get part of what I need if I say

DELETE Employee
WHERE ssn = '111111121';

but then i can't set the vet, hence my problem.

Here's some data to verify

SQL> INSERT INTO EMPLOYEE VALUES ('smith', '111111121', 35000, 5, '04-JAN-01', NULL);

1 row created.


SQL> select * from employee;

LNAME SSN SALARY DNO VST VET
--------------- --------- ---------- ---------- --------- ---------
smith 111111121 35000 5 04-JAN-01
SQL> UPDATE Employee
SET vet = '04-JAN-06'
WHERE ssn = '111111121';

2 3 UPDATE Employee
*
ERROR at line 1:
ORA-04091: table HXX9782.EMPLOYEE is mutating, trigger/function may not see it
ORA-06512: at "HXX9782.TRIG44", line 2
ORA-04088: error during execution of trigger 'HXX9782.TRIG44'
ORA-06512: at "HXX9782.TRIG4", line 2
ORA-04088: error during execution of trigger 'HXX9782.TRIG4'

Any help is appreciated,
thanks