A database trigger is a storage subroutine associated with a data table, view, or event in a database. For example, we can enable the Oracle database to automatically activate a trigger before or after an INSERT, UPDATE, or DELETE expression affects a table. One of the functions of a trigger is to review data changes. For example, the following table-level Trigger is activated after the salaries field of the emp table is updated.
Create trigger audit_sal
After update of sal
ON emp
FOR EACH ROW
BEGIN
Insert into emp_audit
VALUES...
END;
* *********** Instance !!!!! ******************
1. Create the emp_audit table first:
Create table emp_audit (information varchar2 (50), update_today date );
2. If no emp table exists, create an emp table:
Create table emp (
Empno number (4) primary key,
ENAME VARCHAR2 (10)
JOB VARCHAR2 (9)
Mgr number (4)
HIREDATE DATE
Sal number (7,2)
Comm number (7, 2)
Deptno number (2 ));
Insert data:
Insert into emp values (7368, 'smith1 ', 'cler', 7902, sysdate, 13.00, null, 20 );
3. Create a trigger:
Create trigger audit_sal
After update of sal
ON emp
FOR EACH ROW
BEGIN
Insert into emp_audit
VALUES ('successful ', sysdate );
END;
******************************
1. Update emp: update emp set sal = 1300 where empno = 7368;
2. View the updated emp data: select * from emp;
3. View emp_audit data: select * from emp_audit;