Trigger to call a procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Trigger to call a procedure

  1. #1
    Join Date
    Sep 2008
    Posts
    8

    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?

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  3. #3
    Join Date
    Sep 2008
    Posts
    8
    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;

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  5. #5
    Join Date
    Sep 2008
    Posts
    8
    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?

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote 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
  •  


Click Here to Expand Forum to Full Width