before Insert or Update trigger in Oracle 11g

before Insert or Update trigger in Oracle 11g

Triggers are special kind of stored procedures in Oracle Database.

Oracle Database has 2types of DML triggers.

1. Statement level Triggers
2. Table level Triggers

DML Statement Level  triggers are automatically fired when Insert,Update,Delete operations performed on a table. so that each row values can be checked.

Here is an example for Before Insert or Before Update Statement level trigger, which performs  age check  against new values.

SQL>Create table employee(empno number(5), ename varchar2(20),JoinDate date, DOB date)

If employee Date of Birth is below 18 yrs old, it displays error message "Under Age".

Creating  a Age check Trigger  for  Before Insert or Before Update.

create or replace trigger Employee_Age_check
before insert or update
on employee
for each row

declare
n number(2);
begin

select months_between(current_date,:new.DOB)/12 into n from dual;

if n >=18 then
       dbms_output.put_line('Age is >18');
else raise_application_error(-20345,'Under Age');

end if;

end;

Note: months_between function returns number of months between 2 dates  , if it is divided by 12 months gives years.

Checking  Before Insert Trigger

for ex:

insert into employee values(1,'shyam',sysdate,to_date('2000-JAN-01','yyyy-MON-DD'))

Trigger will fire an exception

ERROR at line 1:
ORA-20345: Under Age
ORA-06512: at "SCOTT.EMPLOYEE_AGE_CHECK", line 7
ORA-04088: error during execution of trigger 'SCOTT.EMPLOYEE_AGE_CHECK'


SQL> insert into employee values(1,'shyam',sysdate,to_date('1990-JAN-01','yyyy-MON-DD'));
1 row created.

SQL> select * from employee;

    EMPNO ENAME                JOINDATE  DOB
--------- -------------------- --------- ---------
        1 shyam                14-APR-14 01-JAN-90

Checking  Before Update Trigger

SQL> update employee set DOB=to_date('2000-JAN-03','YYYY-MON-DD') where empno=1

it will throw same exception as shown above.


SQL> update employee set DOB=to_date('1990-JAN-03','YYYY-MON-DD') where empno=1

will succeed