The following is an Oracle BEFORE INSERT OR UPDATE Trigger example to perform validations while inserting or updating the records in EMP table.
Oracle BEFORE INSERT OR UPDATE Trigger Example
The below trigger will do the following two checks on EMP table. (1) If inserting the records, it will check for commission column to not to be higher than 500. (2) If updating, then it will check that JOB column should not be set as null.
CREATE OR REPLACE TRIGGER TRIG_EMP_BEF_UPD_INS BEFORE INSERT OR UPDATE ON EMP FOR EACH ROW BEGIN IF INSERTING THEN /* commission should not be greater than 500, for new record*/ IF :new.comm > 500 THEN raise_application_error ( -20001, 'Commission should not be greater than 500.'); END IF; ELSIF UPDATING THEN /* check for column JOB should not be set as null while update*/ IF :new.job IS NULL THEN raise_application_error (-20001, 'Column JOB should have a value.'); END IF; END IF; END;
Test Above Trigger For Insert Statement
Insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) Values (7399, 'A.SMITH', 'CLERK', 7902, TO_DATE('12/17/1980 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 800, 550, 20);
Output
Error at line 1 ORA-20001: Commission should not be greater than 500. ORA-06512: at "TRIG_EMP_BEF_UPD_INS", line 6 ORA-04088: error during execution of trigger 'TRIG_EMP_BEF_UPD_INS' Script Terminated on line 1.
Test Above Trigger For Update Statement
UPDATE EMP SET sal = 900, job = NULL WHERE empno = 7499;
Output
Error at line 2 ORA-20001: Column JOB should have a value. ORA-06512: at "TRIG_EMP_BEF_UPD_INS", line 12 ORA-04088: error during execution of trigger 'TRIG_EMP_BEF_UPD_INS' Script Terminated on line 2.
You can test this trigger in your schema, by downloading the demo tables from the following link Download Scott Schema script.