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
|
] 
Blog
Search
Help
Members
Register
Login
Home
