Two classic examples of Oracle triggers

Two classic examples of Oracle triggers

http://blog.csdn.net/justdo2008/article/details/4137779

"Case One"

Topic:
--Trigger:
--Add employee information, serial number as auto number (generated by sequence),
--and determine if the wage is less than 0, change to 0, or 10000 if it is greater than 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, ' Zhang Ziyi (',
1000000000000)
INSERT into EMP2 (e_id,e_no,e_name,e_sal) VALUES (seq_trg_id.nextval,7788, ' Zhang Ziyi ',-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 > 10000 Then
: new.e_sal: = 10000;
END IF;
END;

"Case Two"

Topic:

--Expansion Exercises:
--Create a trigger for EMP and place the deleted records in the Emp3 table (autoid,deptno,empno,ename,del_rq-delete date)
--Test code

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, ' Gillian ', 10);
COMMIT;

SELECT * from EMP;

DELETE emp WHERE empno = 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;

Two classic examples of Oracle triggers