PL/SQL triggers with two existing tables and one empty table












0















I am trying to write a trigger that will insert into an empty table the employeeID, employeeName, old department, new department and effective date from two already existing tables employee and department. I have to retrieve that department name from the department table. I got a "bad bind variable" error from oracle and I don't know what I have to fix:



CREATE OR REPLACE TRIGGER emp_dept_change_trg
AFTER INSERT OR UPDATE ON EMPLOYEE
FOR EACH ROW

DECLARE
v_dept DEPARTMENT.DEPARTMENT_NAME%TYPE;

BEGIN

SELECT DEPARTMENT_NAME
INTO v_dept
FROM DEPARTMENT
WHERE DEPARTMENT_ID = :NEW.DEPARTMENT_ID;

IF INSERTING THEN
INSERT INTO emp_dept_change
VALUES(:NEW.EMPLOYEE_ID, :NEW.EMPLOYEE_NAME, '[New Hire]',:NEW.DEPARTMENT_NAME ,SYSDATE);

ELSIF UPDATING THEN

IF :OLD.DEPARTMENT_ID != :NEW.DEPARTMENT_ID THEN
INSERT INTO emp_dept_change
VALUES(:OLD.EMPLOYEE_ID, :OLD.EMPLOYEE_NAME, :OLD.DEPARTMENT_NAME, :NEW.DEPARTMENT_NAME ,SYSDATE);


END IF;
END IF;
END;


I am following logical steps as well:



Write a trigger emp_dept_change_trg that monitors the employee table as follows.



When a row (record) is inserted into the employee table, the trigger automatically inserts a row (record) into the emp_dept_change table in any situations.



The OLD_DEPARTMENT_NAME is always '[New Hire]'.



Find the new department name from the department table based on the new department_id.



If the new department_id is NULL, the NEW_DEPARTMENT_NAME will be '[-----]'.



When an employee changes his/her department (the old department_id is not equal to the new department_id), the trigger automatically inserts a row (record) into the emp_dept_change table. (If both the old department_id and new department_id are NULL (from NULL department to NULL department), the trigger does not insert a row (record) into the emp_dept_change table.)



Find the old department name from the department table based on the old department_id.



If the old department_id is NULL, the OLD_DEPARTMENT_NAME will be '[-----]'.



Find the new department name from the department table based on the new department_id.



If the new department_id is NULL, the NEW_DEPARTMENT_NAME will be '[-----]'.



The SYSDATE can be used in the EFFECTIVE_DATE column.



You can assume that the insert/update statements do not violate the integrity constraints between the department and employee tables.



No temporary table/view/procedure/function is allowed in your trigger.



You can only use the department, employee, and emp_dept_change tables in your trigger.










share|improve this question














bumped to the homepage by Community 13 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
















  • Add the table definitions to your question.

    – Balazs Papp
    Aug 14 '16 at 16:51
















0















I am trying to write a trigger that will insert into an empty table the employeeID, employeeName, old department, new department and effective date from two already existing tables employee and department. I have to retrieve that department name from the department table. I got a "bad bind variable" error from oracle and I don't know what I have to fix:



CREATE OR REPLACE TRIGGER emp_dept_change_trg
AFTER INSERT OR UPDATE ON EMPLOYEE
FOR EACH ROW

DECLARE
v_dept DEPARTMENT.DEPARTMENT_NAME%TYPE;

BEGIN

SELECT DEPARTMENT_NAME
INTO v_dept
FROM DEPARTMENT
WHERE DEPARTMENT_ID = :NEW.DEPARTMENT_ID;

IF INSERTING THEN
INSERT INTO emp_dept_change
VALUES(:NEW.EMPLOYEE_ID, :NEW.EMPLOYEE_NAME, '[New Hire]',:NEW.DEPARTMENT_NAME ,SYSDATE);

ELSIF UPDATING THEN

IF :OLD.DEPARTMENT_ID != :NEW.DEPARTMENT_ID THEN
INSERT INTO emp_dept_change
VALUES(:OLD.EMPLOYEE_ID, :OLD.EMPLOYEE_NAME, :OLD.DEPARTMENT_NAME, :NEW.DEPARTMENT_NAME ,SYSDATE);


END IF;
END IF;
END;


I am following logical steps as well:



Write a trigger emp_dept_change_trg that monitors the employee table as follows.



When a row (record) is inserted into the employee table, the trigger automatically inserts a row (record) into the emp_dept_change table in any situations.



The OLD_DEPARTMENT_NAME is always '[New Hire]'.



Find the new department name from the department table based on the new department_id.



If the new department_id is NULL, the NEW_DEPARTMENT_NAME will be '[-----]'.



When an employee changes his/her department (the old department_id is not equal to the new department_id), the trigger automatically inserts a row (record) into the emp_dept_change table. (If both the old department_id and new department_id are NULL (from NULL department to NULL department), the trigger does not insert a row (record) into the emp_dept_change table.)



Find the old department name from the department table based on the old department_id.



If the old department_id is NULL, the OLD_DEPARTMENT_NAME will be '[-----]'.



Find the new department name from the department table based on the new department_id.



If the new department_id is NULL, the NEW_DEPARTMENT_NAME will be '[-----]'.



The SYSDATE can be used in the EFFECTIVE_DATE column.



You can assume that the insert/update statements do not violate the integrity constraints between the department and employee tables.



No temporary table/view/procedure/function is allowed in your trigger.



You can only use the department, employee, and emp_dept_change tables in your trigger.










share|improve this question














bumped to the homepage by Community 13 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
















  • Add the table definitions to your question.

    – Balazs Papp
    Aug 14 '16 at 16:51














0












0








0








I am trying to write a trigger that will insert into an empty table the employeeID, employeeName, old department, new department and effective date from two already existing tables employee and department. I have to retrieve that department name from the department table. I got a "bad bind variable" error from oracle and I don't know what I have to fix:



CREATE OR REPLACE TRIGGER emp_dept_change_trg
AFTER INSERT OR UPDATE ON EMPLOYEE
FOR EACH ROW

DECLARE
v_dept DEPARTMENT.DEPARTMENT_NAME%TYPE;

BEGIN

SELECT DEPARTMENT_NAME
INTO v_dept
FROM DEPARTMENT
WHERE DEPARTMENT_ID = :NEW.DEPARTMENT_ID;

IF INSERTING THEN
INSERT INTO emp_dept_change
VALUES(:NEW.EMPLOYEE_ID, :NEW.EMPLOYEE_NAME, '[New Hire]',:NEW.DEPARTMENT_NAME ,SYSDATE);

ELSIF UPDATING THEN

IF :OLD.DEPARTMENT_ID != :NEW.DEPARTMENT_ID THEN
INSERT INTO emp_dept_change
VALUES(:OLD.EMPLOYEE_ID, :OLD.EMPLOYEE_NAME, :OLD.DEPARTMENT_NAME, :NEW.DEPARTMENT_NAME ,SYSDATE);


END IF;
END IF;
END;


I am following logical steps as well:



Write a trigger emp_dept_change_trg that monitors the employee table as follows.



When a row (record) is inserted into the employee table, the trigger automatically inserts a row (record) into the emp_dept_change table in any situations.



The OLD_DEPARTMENT_NAME is always '[New Hire]'.



Find the new department name from the department table based on the new department_id.



If the new department_id is NULL, the NEW_DEPARTMENT_NAME will be '[-----]'.



When an employee changes his/her department (the old department_id is not equal to the new department_id), the trigger automatically inserts a row (record) into the emp_dept_change table. (If both the old department_id and new department_id are NULL (from NULL department to NULL department), the trigger does not insert a row (record) into the emp_dept_change table.)



Find the old department name from the department table based on the old department_id.



If the old department_id is NULL, the OLD_DEPARTMENT_NAME will be '[-----]'.



Find the new department name from the department table based on the new department_id.



If the new department_id is NULL, the NEW_DEPARTMENT_NAME will be '[-----]'.



The SYSDATE can be used in the EFFECTIVE_DATE column.



You can assume that the insert/update statements do not violate the integrity constraints between the department and employee tables.



No temporary table/view/procedure/function is allowed in your trigger.



You can only use the department, employee, and emp_dept_change tables in your trigger.










share|improve this question














I am trying to write a trigger that will insert into an empty table the employeeID, employeeName, old department, new department and effective date from two already existing tables employee and department. I have to retrieve that department name from the department table. I got a "bad bind variable" error from oracle and I don't know what I have to fix:



CREATE OR REPLACE TRIGGER emp_dept_change_trg
AFTER INSERT OR UPDATE ON EMPLOYEE
FOR EACH ROW

DECLARE
v_dept DEPARTMENT.DEPARTMENT_NAME%TYPE;

BEGIN

SELECT DEPARTMENT_NAME
INTO v_dept
FROM DEPARTMENT
WHERE DEPARTMENT_ID = :NEW.DEPARTMENT_ID;

IF INSERTING THEN
INSERT INTO emp_dept_change
VALUES(:NEW.EMPLOYEE_ID, :NEW.EMPLOYEE_NAME, '[New Hire]',:NEW.DEPARTMENT_NAME ,SYSDATE);

ELSIF UPDATING THEN

IF :OLD.DEPARTMENT_ID != :NEW.DEPARTMENT_ID THEN
INSERT INTO emp_dept_change
VALUES(:OLD.EMPLOYEE_ID, :OLD.EMPLOYEE_NAME, :OLD.DEPARTMENT_NAME, :NEW.DEPARTMENT_NAME ,SYSDATE);


END IF;
END IF;
END;


I am following logical steps as well:



Write a trigger emp_dept_change_trg that monitors the employee table as follows.



When a row (record) is inserted into the employee table, the trigger automatically inserts a row (record) into the emp_dept_change table in any situations.



The OLD_DEPARTMENT_NAME is always '[New Hire]'.



Find the new department name from the department table based on the new department_id.



If the new department_id is NULL, the NEW_DEPARTMENT_NAME will be '[-----]'.



When an employee changes his/her department (the old department_id is not equal to the new department_id), the trigger automatically inserts a row (record) into the emp_dept_change table. (If both the old department_id and new department_id are NULL (from NULL department to NULL department), the trigger does not insert a row (record) into the emp_dept_change table.)



Find the old department name from the department table based on the old department_id.



If the old department_id is NULL, the OLD_DEPARTMENT_NAME will be '[-----]'.



Find the new department name from the department table based on the new department_id.



If the new department_id is NULL, the NEW_DEPARTMENT_NAME will be '[-----]'.



The SYSDATE can be used in the EFFECTIVE_DATE column.



You can assume that the insert/update statements do not violate the integrity constraints between the department and employee tables.



No temporary table/view/procedure/function is allowed in your trigger.



You can only use the department, employee, and emp_dept_change tables in your trigger.







oracle trigger plsql errors






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Aug 14 '16 at 2:42









user2101463user2101463

11




11





bumped to the homepage by Community 13 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.







bumped to the homepage by Community 13 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.















  • Add the table definitions to your question.

    – Balazs Papp
    Aug 14 '16 at 16:51



















  • Add the table definitions to your question.

    – Balazs Papp
    Aug 14 '16 at 16:51

















Add the table definitions to your question.

– Balazs Papp
Aug 14 '16 at 16:51





Add the table definitions to your question.

– Balazs Papp
Aug 14 '16 at 16:51










1 Answer
1






active

oldest

votes


















0














You can't use :NEW.DEPARTMENT_NAME since DEPARTMENT_NAME is a column of DEPARTMENT not of EMPLOYEE.
You have to select the values before. Something like this:



CREATE OR REPLACE TRIGGER emp_dept_change_trg
AFTER INSERT OR UPDATE ON EMPLOYEE
FOR EACH ROW

DECLARE
v_dept DEPARTMENT.DEPARTMENT_NAME%TYPE;
v_old_dept DEPARTMENT.DEPARTMENT_NAME%TYPE;

BEGIN

SELECT DEPARTMENT_NAME
INTO v_dept
FROM DEPARTMENT
WHERE DEPARTMENT_ID = :NEW.DEPARTMENT_ID;

IF INSERTING THEN
INSERT INTO emp_dept_change
VALUES(:NEW.EMPLOYEE_ID, v_dept, '[New Hire]',:NEW.DEPARTMENT_NAME ,SYSDATE);

ELSIF UPDATING and :OLD.DEPARTMENT_ID != :NEW.DEPARTMENT_ID THEN

SELECT DEPARTMENT_NAME
INTO v_old_dept
FROM DEPARTMENT
WHERE DEPARTMENT_ID = :OLD.DEPARTMENT_ID;

INSERT INTO emp_dept_change
VALUES(:OLD.EMPLOYEE_ID, :OLD.EMPLOYEE_NAME, v_old_dept, v_dept ,SYSDATE);
END IF;
END;





share|improve this answer

























    Your Answer








    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "182"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: false,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: null,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f146729%2fpl-sql-triggers-with-two-existing-tables-and-one-empty-table%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    0














    You can't use :NEW.DEPARTMENT_NAME since DEPARTMENT_NAME is a column of DEPARTMENT not of EMPLOYEE.
    You have to select the values before. Something like this:



    CREATE OR REPLACE TRIGGER emp_dept_change_trg
    AFTER INSERT OR UPDATE ON EMPLOYEE
    FOR EACH ROW

    DECLARE
    v_dept DEPARTMENT.DEPARTMENT_NAME%TYPE;
    v_old_dept DEPARTMENT.DEPARTMENT_NAME%TYPE;

    BEGIN

    SELECT DEPARTMENT_NAME
    INTO v_dept
    FROM DEPARTMENT
    WHERE DEPARTMENT_ID = :NEW.DEPARTMENT_ID;

    IF INSERTING THEN
    INSERT INTO emp_dept_change
    VALUES(:NEW.EMPLOYEE_ID, v_dept, '[New Hire]',:NEW.DEPARTMENT_NAME ,SYSDATE);

    ELSIF UPDATING and :OLD.DEPARTMENT_ID != :NEW.DEPARTMENT_ID THEN

    SELECT DEPARTMENT_NAME
    INTO v_old_dept
    FROM DEPARTMENT
    WHERE DEPARTMENT_ID = :OLD.DEPARTMENT_ID;

    INSERT INTO emp_dept_change
    VALUES(:OLD.EMPLOYEE_ID, :OLD.EMPLOYEE_NAME, v_old_dept, v_dept ,SYSDATE);
    END IF;
    END;





    share|improve this answer






























      0














      You can't use :NEW.DEPARTMENT_NAME since DEPARTMENT_NAME is a column of DEPARTMENT not of EMPLOYEE.
      You have to select the values before. Something like this:



      CREATE OR REPLACE TRIGGER emp_dept_change_trg
      AFTER INSERT OR UPDATE ON EMPLOYEE
      FOR EACH ROW

      DECLARE
      v_dept DEPARTMENT.DEPARTMENT_NAME%TYPE;
      v_old_dept DEPARTMENT.DEPARTMENT_NAME%TYPE;

      BEGIN

      SELECT DEPARTMENT_NAME
      INTO v_dept
      FROM DEPARTMENT
      WHERE DEPARTMENT_ID = :NEW.DEPARTMENT_ID;

      IF INSERTING THEN
      INSERT INTO emp_dept_change
      VALUES(:NEW.EMPLOYEE_ID, v_dept, '[New Hire]',:NEW.DEPARTMENT_NAME ,SYSDATE);

      ELSIF UPDATING and :OLD.DEPARTMENT_ID != :NEW.DEPARTMENT_ID THEN

      SELECT DEPARTMENT_NAME
      INTO v_old_dept
      FROM DEPARTMENT
      WHERE DEPARTMENT_ID = :OLD.DEPARTMENT_ID;

      INSERT INTO emp_dept_change
      VALUES(:OLD.EMPLOYEE_ID, :OLD.EMPLOYEE_NAME, v_old_dept, v_dept ,SYSDATE);
      END IF;
      END;





      share|improve this answer




























        0












        0








        0







        You can't use :NEW.DEPARTMENT_NAME since DEPARTMENT_NAME is a column of DEPARTMENT not of EMPLOYEE.
        You have to select the values before. Something like this:



        CREATE OR REPLACE TRIGGER emp_dept_change_trg
        AFTER INSERT OR UPDATE ON EMPLOYEE
        FOR EACH ROW

        DECLARE
        v_dept DEPARTMENT.DEPARTMENT_NAME%TYPE;
        v_old_dept DEPARTMENT.DEPARTMENT_NAME%TYPE;

        BEGIN

        SELECT DEPARTMENT_NAME
        INTO v_dept
        FROM DEPARTMENT
        WHERE DEPARTMENT_ID = :NEW.DEPARTMENT_ID;

        IF INSERTING THEN
        INSERT INTO emp_dept_change
        VALUES(:NEW.EMPLOYEE_ID, v_dept, '[New Hire]',:NEW.DEPARTMENT_NAME ,SYSDATE);

        ELSIF UPDATING and :OLD.DEPARTMENT_ID != :NEW.DEPARTMENT_ID THEN

        SELECT DEPARTMENT_NAME
        INTO v_old_dept
        FROM DEPARTMENT
        WHERE DEPARTMENT_ID = :OLD.DEPARTMENT_ID;

        INSERT INTO emp_dept_change
        VALUES(:OLD.EMPLOYEE_ID, :OLD.EMPLOYEE_NAME, v_old_dept, v_dept ,SYSDATE);
        END IF;
        END;





        share|improve this answer















        You can't use :NEW.DEPARTMENT_NAME since DEPARTMENT_NAME is a column of DEPARTMENT not of EMPLOYEE.
        You have to select the values before. Something like this:



        CREATE OR REPLACE TRIGGER emp_dept_change_trg
        AFTER INSERT OR UPDATE ON EMPLOYEE
        FOR EACH ROW

        DECLARE
        v_dept DEPARTMENT.DEPARTMENT_NAME%TYPE;
        v_old_dept DEPARTMENT.DEPARTMENT_NAME%TYPE;

        BEGIN

        SELECT DEPARTMENT_NAME
        INTO v_dept
        FROM DEPARTMENT
        WHERE DEPARTMENT_ID = :NEW.DEPARTMENT_ID;

        IF INSERTING THEN
        INSERT INTO emp_dept_change
        VALUES(:NEW.EMPLOYEE_ID, v_dept, '[New Hire]',:NEW.DEPARTMENT_NAME ,SYSDATE);

        ELSIF UPDATING and :OLD.DEPARTMENT_ID != :NEW.DEPARTMENT_ID THEN

        SELECT DEPARTMENT_NAME
        INTO v_old_dept
        FROM DEPARTMENT
        WHERE DEPARTMENT_ID = :OLD.DEPARTMENT_ID;

        INSERT INTO emp_dept_change
        VALUES(:OLD.EMPLOYEE_ID, :OLD.EMPLOYEE_NAME, v_old_dept, v_dept ,SYSDATE);
        END IF;
        END;






        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited Aug 16 '16 at 11:43

























        answered Aug 16 '16 at 10:31









        vercellivercelli

        72646




        72646






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Database Administrators Stack Exchange!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f146729%2fpl-sql-triggers-with-two-existing-tables-and-one-empty-table%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Liste der Baudenkmale in Friedland (Mecklenburg)

            Single-Malt-Whisky

            Czorneboh