38 lines
1.1 KiB
MySQL
38 lines
1.1 KiB
MySQL
|
CREATE OR REPLACE TRIGGER only_sales_comm
|
||
|
|
BEFORE INSERT OR UPDATE
|
||
|
|
ON EMP_COPIA
|
||
|
|
FOR EACH ROW
|
||
|
|
BEGIN
|
||
|
|
IF UPPER(:NEW.job) != 'SALESMAN' AND :NEW.comm IS NOT NULL
|
||
|
|
THEN
|
||
|
|
raise_application_error(-20669, 'Comm only allowed if SALESMAN');
|
||
|
|
END IF;
|
||
|
|
END;
|
||
|
|
/
|
||
|
|
|
||
|
|
CREATE TABLE EMP_COPIA AS (SELECT * FROM EMP);
|
||
|
|
|
||
|
|
SELECT * FROM EMP_COPIA;
|
||
|
|
|
||
|
|
UPDATE EMP_COPIA SET COMM=72 WHERE EMPNO=7782;
|
||
|
|
|
||
|
|
CREATE OR REPLACE TRIGGER sal_range
|
||
|
|
BEFORE INSERT OR UPDATE
|
||
|
|
ON EMP_COPIA
|
||
|
|
FOR EACH ROW
|
||
|
|
BEGIN
|
||
|
|
IF UPPER(:NEW.JOB) = 'CLERK' AND :NEW.SAL NOT BETWEEN 800 AND 1100
|
||
|
|
|
||
|
|
THEN
|
||
|
|
raise_application_error(-20669, 'Clerk salary must be between 800-1100');
|
||
|
|
ELSIF UPPER(:NEW.JOB) = 'ANALYST' AND :NEW.SAL NOT BETWEEN 1200 AND 1600
|
||
|
|
THEN
|
||
|
|
raise_application_error(-20669, 'Analyst salary must be between 1200-1600');
|
||
|
|
ELSIF UPPER(:NEW.JOB) = 'MANAGER' AND :NEW.SAL NOT BETWEEN 1800 AND 2000
|
||
|
|
THEN
|
||
|
|
raise_application_error(-20669, 'Manager salary must be between 1800-2000');
|
||
|
|
END IF;
|
||
|
|
END;
|
||
|
|
/
|
||
|
|
|
||
|
|
UPDATE EMP_COPIA SET SAL=5 WHERE EMPNO=7788;
|