Two typical ORACLE trigger examples

[Case 1]

Question:
-- Trigger:
-- Add employee information. The serial number is used as the automatic serial number (generated by serial number ),
-- If the wage is less than 0, it is changed to 0. If the wage is greater than 10000, it is changed to 10000.

Create Table emp2 (
E_id number,
E_no number,
E_name varchar2 (20 ),
E_sal number
)

Select * From emp2;

Create sequence seq_trg_id;

Insert into emp2 (e_id, e_no, e_name, e_sal) values (seq_trg_id.nextval, 7788, 'zhangzi ',
1000000000000)
Insert into emp2 (e_id, e_no, e_name, e_sal) values (seq_trg_id.nextval, 7788, 'zhangziyi',-10)

Create or replace trigger trg_add_emp_info
Before insert
On emp2
For each row
Declare
-- Local variables here
Begin
Select seq_trg_id.nextval into: New. e_id from dual;
If: New. e_sal <0 then
: New. e_sal: = 0;
Elsif: New. e_sal & gt; 10000 then
: New. e_sal: = 10000;
End if;
End;

Case 2]

Question:

-- Expand exercise:
-- Create a trigger for EMP and put the deleted records in the emp3 table (autoid, deptno, empno, ename, del_rq-date of deletion)
-- TestCode

Create Table emp3 (
Autoid number primary key,
Deptno number,
Empno number,
Ename varchar2 (20 ),
Del_rq date
)

Create sequence seq_trg_del_autoid;

Insert into EMP
(Empno, ename, deptno)
Values
(114, 'abaclo', 10 );
Commit;
 
Select * from EMP;
 
Delete E-mapreduce where e-mapreduce = 114;
Select * From emp3;
 
Answer:

Create or replace trigger trg_del_emp_info
Before Delete
On EMP
For each row
Declare
-- Local variables here
Begin
Insert into emp3 (autoid, deptno, empno, ename, del_rq)
Values (seq_trg_del_autoid.nextval,: Old. deptno,: Old. empno,: Old. ename, sysdate );
End;

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/justdo2008/archive/2009/04/29/4137779.aspx