DML Trigger Example : Trigger « Trigger « Oracle PL/SQL Tutorial






SQL>

SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
  3    First_Name         VARCHAR2(10 BYTE),
  4    Last_Name          VARCHAR2(10 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(10 BYTE),
  9    Description        VARCHAR2(15 BYTE)
 10  )
 11  /

Table created.

SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /

no rows selected

SQL>
SQL> create or replace trigger emp_biu
  2  BEFORE INSERT OR UPDATE
  3  of salary
  4  on employee
  5  for each row
  6  declare
  7      v_error VARCHAR2(2000);
  8  begin
  9      if :new.salary > 10000
 10      then
 11          v_error:=:old.first_name||' cannot have that much!';
 12          raise_application_error(-20999,v_error);
 13      end if;
 14
 15
 16  end;
 17  /

Trigger created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values('08','James',    'Cat',     to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 11232.78,'Vancouver', 'Tester')
  3  /
insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,               Salary, City,        Description)
            *
ERROR at line 1:
ORA-20999:  cannot have that much!
ORA-06512: at "JAVA2S.EMP_BIU", line 7
ORA-04088: error during execution of trigger 'JAVA2S.EMP_BIU'


SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /

Table dropped.

SQL>
SQL>
SQL>








28.2.Trigger
28.2.1.Triggers
28.2.2.System triggers
28.2.3.Creating a Trigger
28.2.4.Trigger Blocks
28.2.5.Trigger that output old value
28.2.6.DML Trigger Example
28.2.7.Firing a Trigger
28.2.8.The use of a trigger
28.2.9.INSERTING, DELETING and UPDATING Predicates
28.2.10.Reference current user name in trigger
28.2.11.Call raise_application_error to report an error in a trigger
28.2.12.Use cursor in trigger
28.2.13.Check the status of the trigger
28.2.14.Show errors for a trigger