Trigger to call a procedure

[texan]

Hi all, i have two tables
1) Employee
2)Employee_Updates

Whenever i add a new record or update existing record in Employee table, that recod should go and insert into the Employee_updates table.
I want to do this by write a trigger that call a procedure.

Here are more details:

Emplyee table:CREATE TABLE EMPLOYEE
(
EMP_ID VARCHAR2(255 BYTE),
EMP_TYPE VARCHAR2(255 BYTE),
START_DATE DATE,
END_DATE DATE,
TOT_SAL VARCHAR2(255 BYTE)
)

Insert into EMPLOYEE (EMP_ID, EMP_TYPE, START_DATE, END_DATE, TOT_SAL) Values
('111', 'MANAGER', TO_DATE('01/10/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('04/10/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
'150000');

Insert into EMPLOYEE
(EMP_ID, EMP_TYPE, START_DATE, END_DATE, TOT_SAL)
Values
('222', 'MANAGER', TO_DATE('02/01/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('09/30/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
'200000');

Insert into EMPLOYEE(EMP_ID, EMP_TYPE, START_DATE, END_DATE, TOT_SAL)
Values
('333', 'CONTRACTOR', TO_DATE('12/15/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/15/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
'2500000');

EMPLOYEE_UPDATES Table

CREATE TABLE EMPLOYEE_UPDATES(
EMP_KEY NUMBER,
EMP_ID VARCHAR2(255 BYTE),
EMP_TYPE VARCHAR2(255 BYTE),
START_DATE DATE,
END_DATE DATE,
TOT_SAL VARCHAR2(255 BYTE),
CUR_IND VARCHAR2(1))


here EMP_KEY is a sequense number and CUR_IND is a indicater that tells when it is updated record or new record

Can someone help to do this?

[PAVB]

Wrong approach, a trigger is nothing but a stored procedure "triggered" by a specific event.

Write your trigger including the logic to insert in the second table.

[texan]

Thanks for the reply.

Here is actual trigger that works according to my requirement

create or replace trigger TRG_EMPLOYEE_UPDATES
before insert or update on EMPLOYEE
for each row

begin
if (updating) then
insert into EMPLOYEE_UPDATES
( EMP_KEY,
EMP_ID,
EMP_TYPE,
START_DATE,
END_DATE,
TOT_SAL,
CUR_IND
)
values
( SEQ_EMP_KEY.nextval,
:new.EMP_ID,
:new.EMP_TYPE,
:new.START_DATE,
:new.END_DATE,
:new.TOT_SAL,
'U'
);
else
insert into EMPLOYEE_UPDATES
( EMP_KEY,
EMP_ID,
EMP_TYPE,
START_DATE,
END_DATE,
TOT_SAL,
CUR_IND)
values
( SEQ_EMP_KEY.nextval,
:new.EMP_ID,
:new.EMP_TYPE,
:new.START_DATE,
:new.END_DATE,
:new.TOT_SAL,
'I');
end if;
end;



But i am trying to modify this by calling a procedure to updates and new recods.

Like this
CREATE OR REPLACE TRIGGER EMPLOYEE_UPDATES
BEFORE INSERT OR UPDATE ON EMPLOYEE
FOR EACH ROW

DECLARE
p_old_rec EMPLOYEE%ROWTYPE;
p_new_rec EMPLOYEE%ROWTYPE;

BEGIN
p_old_rec.emp_id := :OLD.emP_ID;
p_old_rec.emp_type := :OLD.emp_type;
p_old_rec.start_date := :OLD.start_DATE;
p_old_rec.end_date := :OLD.end_Date;
p_old_rec.tot_sal := :OLD.tot_sal;

p_new_rec.emp_id := :NEW.emp_ID;
p_new_rec.emp_type := :NEW.emp_type;
p_new_rec.start_date := :NEW.start_DATE;
p_new_rec.end_date := :new.end_date;
p_new_rec.tot_sal := :new.tot_sal;

spw_EMPLOYEE_UPDATES();

END IF;

END;

[PAVB]

Why in the world do you want to call another procedure? just to add overhead and make the code more difficult to be maintained?

If any of my guys comes to my desk with such an idea I would hit him/her in the head with a frying pan and send him/her back to his/her desk to think about it. Unfortunately out of courtesy towards you - not to say physical location - I cannot do the same in this particular case.

[texan]

You cannot do that. I understand your frustration when i say procedure to do the Updates or Inserts. You meant to say trigger it self good to do that?

[PAVB]

You cannot do that. I understand your frustration when i say procedure to do the Updates or Inserts. You meant to say trigger it self good to do that?

Exactly. You already have your trigger -which is nothing but a procedure then, do whatever you have to do in the trigger.

Your insert/updates are going to affect a different table therefore you are not in risk of mutating table syndrome; just do it in your trigger, much clean, less overhead... or face the frying pan