Home » SQL & PL/SQL » SQL & PL/SQL » Trigger to call a procedure that loads updated and insert records
Trigger to call a procedure that loads updated and insert records [message #351196] Mon, 29 September 2008 16:36 Go to next message
texan
Messages: 14
Registered: September 2008
Junior Member
Hi all, i am writing a trigger on table A that call a procedure to load the updated records from table A to table B.
Here are the details


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', '01-JAN-2007','10-APR-2009','150000');

Insert into EMPLOYEE (EMP_ID, EMP_TYPE, START_DATE, END_DATE, TOT_SAL) Values ('222', 'MANAGER', '01-JAN-2007','10-APR-2008','200000');

Insert into EMPLOYEE(EMP_ID, EMP_TYPE, START_DATE, END_DATE, TOT_SAL) Values ('333', 'CONTRACTOR','01-JAN-2007','10-APR-2008','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

UPDATE EMPOLOYEE SET EMP_TYPE = 'SENIOR MANAGER' WHERE EMP_ID = '111';

When i modify the EMPLOYEE table the trigger should fire and call a procedure that write the recod into EMPLOYEE_UPDATES TABLE


Insert into EMPLOYEE_UPDATES (EMP_ID, EMP_TYPE, START_DATE, END_DATE, TOT_SAL,CUR_IND) Values (1,'111','SENIOR MANAGER',''01-JAN-2007','10-APR-2009','150000','U');


If i insert a new record into EMPLOYEE table

Insert into EMPLOYEE(EMP_ID, EMP_TYPE, START_DATE, END_DATE, TOT_SAL) Values ('444', 'SENIOR MANAGER','01-JAN-2008','31-APR-2009','2500000');

Then it should insert new record into EMPLOYEE_UPDATES TABLE

Insert into EMPLOYEE_UPDATES (EMP_ID, EMP_TYPE, START_DATE, END_DATE, TOT_SAL,CUR_IND) Values (2,'444','SENIOR MANAGER','01-JAN-2008','31-APR-2009','2500000','N');


I wrote a trigger to do this

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;


This works good for above scenario. But i want a procedure to do this.
I wrote a procedure that does only duplicate inserts. Is there any way that i can do this with no duplicates.

Here is the trigger

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;

Here is the procedure

CREATE OR REPLACE PROCEDURE spw_EMPLOYEE_UPDATES p_old_rec EMPLOYEE%ROWTYPE,p_new_rec EMPLOYEE%ROWTYPE) IS
BEGIN


if p_emp_old.emp_id = p_emp_new.emp_id then

INSERT INTO EMPLOYEE_UPDATES(EMP_KEY,EMP_ID,EMP_TYPE,START_DATE,END_DATE,TOT_SAL,CURR_IND) VALUES
(SEQ_EMP_KEY.NEXTVAL,p_new_rec.EMP_ID,p_new_rec.EMP_TYPE,p_new_rec.START_DATE,p_new_rec.END_DATE,
p_new_rec.TOT_SAL,'U');
else
INSERT INTO EMPLOYEE_UPDATES(EMP_KEY,EMP_ID,EMP_TYPE,START_DATE,END_DATE,TOT_SAL,CURR_IND) VALUES
(SEQ_EMP_KEY.NEXTVAL,p_new_rec.EMP_ID,p_new_rec.EMP_TYPE,p_new_rec.START_DATE,p_new_rec.END_DATE,
p_new_rec.TOT_SAL,'N');
end if;
END;

Can someone help me on this
Re: Trigger to call a procedure that loads updated and insert records [message #351219 is a reply to message #351196] Tue, 30 September 2008 00:21 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi,
I hope that statement trigger after insert or update, delete will fulfill your need.
yours
dr.s.raghunathan

Re: Trigger to call a procedure that loads updated and insert records [message #351229 is a reply to message #351196] Tue, 30 September 2008 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once again FOLLOW THE GUIDELINES.

Regards
Michel
Re: Trigger to call a procedure that loads updated and insert records [message #351363 is a reply to message #351219] Tue, 30 September 2008 08:46 Go to previous messageGo to next message
texan
Messages: 14
Registered: September 2008
Junior Member
You meant to say i no need to define the procedure for doing update or insert?
Re: Trigger to call a procedure that loads updated and insert records [message #351799 is a reply to message #351363] Fri, 03 October 2008 04:09 Go to previous message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
yes
unless you call another procedure / function to do some other
activity. You are the best man to decide the course of action.
yourws
dr.s.raghunathan
Previous Topic: How to declare a cursor with dynamic query
Next Topic: Help with GROUP BY in subquery
Goto Forum:
  


Current Time: Thu Dec 08 14:31:54 CST 2016

Total time taken to generate the page: 0.12932 seconds