当前位置: 动力学知识库 > 问答 > 编程问答 >

database - PL/SQL creating a trigger problems

问题描述:

I have to create a trigger on an Employee table. If an INSERT or UPDATE statement is issued for the Employee table the trigger launches and makes sure that the value of 'salary' field meets the criteria in the job_min_sal table. After trying over and over I got a mutating table error and now am very frustrated and don't know what to do.

 JOB_MIN_SALARY TABLE:

JOB VARCHAR2(50) PRIMARY KEY

MIN_SAL NUMBER(7,2) NOT NULL

The JOB_MIN_SAL table is populated with a variety of job titles and salaries. I am confused working with my trigger and wondering if I could get some assistance where to go from here

 CREATE OR REPLACE TRIGGER employee_job_salary

BEFORE INSERT OR UPDATE OF SALARY on employee

FOR EACH ROW

DECLARE

v_salary NUMBER;

BEGIN

SELECT minimum_salary

INTO v_salary

FROM job_min_salary

WHERE UPPER(job) = UPPER(:NEW.job);

I know I am really far off I am just looking for help as for what this requires and what steps I need to take to get this. Thanks!

 The EMPLOYEE table:

(

EMPLOYEE_ID NUMBER(4)

EMPLOYEE_NAME VARCHAR2(20)

JOB VARCHAR2(50)

MANAGER_ID NUMBER(4)

HIRE_DATE DATE

SALARY NUMBER(9)

COMMISION NUMBER(9)

DEPARTMENT_ID NUMBER(4)

);

网友答案:

i am supposing you are doing something like comparing new salary with min salary criterion and update only if :new.SALARY >= v_salary

what are you doing if this is not met, are u trapping an exception or just ignoring the error or returning an error code to debug.

post more info

网友答案:
CREATE TABLE job_min_salary
(
    job     VARCHAR2(50) PRIMARY KEY,
    min_sal NUMBER(7,2)  NOT NULL
);

INSERT INTO job_min_salary VALUES('CEO','100');
-- 1 rows inserted.

CREATE TABLE employee
(
    employee_id     NUMBER(4),
    employee_name   VARCHAR2(20),
    job             VARCHAR2(50),
    manager_id      NUMBER(4),
    hire_date       DATE,
    salary          NUMBER(9),
    commision       NUMBER(9),
    department_id   NUMBER(4)
);

INSERT INTO employee VALUES(1, 'Name', 'CEO', 1, TO_DATE('2000-01-01', 'YYYY-MM-DD'), 80, 80, 1);
-- 1 rows inserted.

CREATE OR REPLACE TRIGGER employee_job_salary
BEFORE INSERT OR UPDATE OF salary ON employee
FOR EACH ROW
DECLARE 
    v_salary  NUMBER(1);
BEGIN
    SELECT  1
    INTO    v_salary
    FROM    job_min_salary
    WHERE   UPPER(job)  = UPPER(:NEW.job)
    AND     :NEW.salary >= min_sal;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20999, 'Salary value is too low for given job');
END;
-- TRIGGER EMPLOYEE_JOB_SALARY compiled

SELECT * FROM employee;
-- 1    Name    CEO 1   2000-01-01 00:00:00 80  80  1

UPDATE employee
SET    salary = 10
WHERE  job    = 'CEO';
-- ORA-20999: Salary value is too low for given job

UPDATE employee
SET    salary = 100
WHERE  job    = 'CEO';
-- 1 rows updated.

SELECT * FROM employee;
-- 1    Name    CEO 1   2000-01-01 00:00:00 100 80  1
分享给朋友:
您可能感兴趣的文章:
随机阅读: