pl/sql TP9

CREATE TRIGGER jobsnt
BEFORE INSERT OR UPDATE OR DELETE
ON jobs
BEGIN
    IF INSERTING THEN
        raise_application_error(-20004,'Insertion interdite.');
    ELSE IF UPDATING THEN
            raise_application_error(-20005, 'Mise à jour interdite.');
        ELSE
            raise_application_error(-20006, 'Suppression interdite');
        END IF;
    END IF;
END;

UPDATE jobs
    SET job_id='DMP_AH';





CREATE TABLE audit_emp_table (user_name VARCHAR2(50)NOT NULL, date_op DATE, nature_op VARCHAR2(20));

CREATE TRIGGER empreee
AFTER INSERT OR UPDATE OR DELETE
ON employees
DECLARE
    nature_op VARCHAR2(20);
BEGIN
    IF INSERTING THEN
        nature_op := 'Insertion';
    ELSIF UPDATING THEN
            nature_op := 'Mise à jour';
        ELSE
            nature_op := 'Suppression';
        END IF;
    END IF;
    INSERT INTO audit_emp_table
        VALUES(USER, SYSDATE, nature_op);
END;



INSERT INTO employees
    VALUES(208, 'Yahya', 'KHALID', 'reee@ee.cc',
        '000.366.1119', SYSDATE, 'DMP_AF', 149000, 0.4, 203, 10);

UPDATE employees
    SET salary = salary * 1.2
    WHERE employee_id=208;

DELETE FROM employees
    WHERE employee_id=208;



CREATE TRIGGER salreee
BEFORE INSERT OR UPDATE OF salary
ON employees
FOR EACH ROW
DECLARE
    min_sal NUMBER;
    max_sal NUMBER;
BEGIN
    SELECT min_salary, max_salary
    INTO min_sal, max_sal
    FROM jobs
    WHERE job_id = :NEW.job_id;

    IF :NEW.salary < min_sal OR :NEW.salary > max_sal THEN
        IF updating THEN
            raise_application_error(-20000, 'erreur mise à jour');
        ELSE
            raise_application_error(-20001, 'erreur ajout');
        END IF;
    END IF;
END;

INSERT INTO employees
    VALUES(208, 'Yahya', 'KHALID', 'reee@ee.cc',
        '000.366.1119', SYSDATE, 'AD_ASST', 149000, 0.4, 203, 10);

UPDATE employees
    SET salary = 3900
    WHERE employee_id=103;



CREATE TRIGGER salcheck
BEFORE INSERT OR UPDATE OF salary
ON employees
FOR EACH ROW
DECLARE
    sal employees.salary%TYPE;
BEGIN
    sal := :OLD.salary * 1.1;
    IF :NEW.salary > sal OR :NEW.salary < sal THEN
        raise_application_error(-20003, 'Erreur - salaire entré est invalide!');
    END IF;
END;


UPDATE employees
    SET salary = salary * 1.2
    WHERE employee_id = 103;



CREATE TABLE dept AS SELECT * FROM departments;
CREATE OR REPLACE TRIGGER maintainDept
AFTER INSERT OR UPDATE OR DELETE
ON departments
FOR EACH ROW
BEGIN
    IF inserting THEN
        INSERT INTO dept
            VALUES(:NEW.department_id, :NEW.department_name, :NEW.manager_id, :NEW.location_id);
    ELSIF updating THEN
        UPDATE dept
            SET department_name = :NEW.department_name,
                manager_id = :NEW.manager_id,
                location_id = :NEW.location_id
            WHERE dept.department_id = :NEW.department_id;
    ELSE
        DELETE FROM dept
            WHERE dept.department_id = :OLD.department_id;
    END IF;
END;

INSERT INTO departments
        VALUES(5, 'Helping', '103', 1500);

UPDATE departments
    SET department_name = 'Helping''nt'
    WHERE department_id = 5;

DELETE FROM departments
    WHERE department_id = 5;