Simple use of triggers in PL/SQL

[SQL] simple use of triggers 1. Introduction to triggers database Triggers are a PL/SQL program associated with tables. When a specific data operation statement (Insert, update, delete) is issued on a specified table, Oracle automatically executes the sequence of statements defined in the trigger. 2. Trigger type (1) Statement-Level Trigger is executed once before or after a specified operation statement operation, regardless of the number of rows affected by this statement. (2) EACH record of the ROW-Level Trigger statement (for each row) is triggered. Use old and new pseudo-record variables in row-level triggers to identify the status of values. 3. trigger example (1) restrict non-working hours to insert data into the database create or replace trigger insertEmp before insert on emp declare begin if (to_char (sysdate, 'day ') in ('thurday', 'Friday') then raise_application_error (-20001, 'Data cannot be inserted '); end if; end; test: SQL> insert into emp (empno, deptno) values (); insert into emp (empno, deptno) values () ORA-20001: cannot insert data ORA-06512: In "TEST1.INSERTEMP", line 5 ORA-04088: trigger 'test1. INSERTEMP error during execution (2) the changed data cannot be less than the original value create or replace trigger updateEmp before update on emp for each row declare -- local variables here begin if: new. sal <: old. sal then raise_application_error (-20002, 'changed data cannot be smaller than the original value'); end if; end updateEmp; test: SQL> update emp set sal = 200 where empno = 7369; update emp set sal = 200 where empno = 7369 ORA-20002: The changed data cannot be less than the original value ORA-06512: in "TEST1.UPDATEEMP", line 5 ORA-04088: trigger 'test1. UPDATEEMP 'error during execution (3) the number of employees inserted by the specified department cannot be greater than 5 create or replace trigger limite before insert on emp for each row declare -- local variables here cursor cl is select count (*) from emp group by deptno; emp_count emp. empno % type; begin open cl; fetch cl into emp_count; if (emp_count> 5) then raise_application_error (-20002, 'No more than five employees'); end if; close cl; end limite; or create or replace trigger limite before insert on emp for each row declare num number; begin select count (*) into num from emp where deptno =: new. deptno; if (num> = 5) then raise_application_error (-20002, 'No more than five employees'); end if; end limite; the result is the same as the preceding format. It is taken from Han Xiaoshuang's column.