-
Trigger to call a procedure
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?
-
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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;
-
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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?
-
Originally Posted by 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?
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
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|