Trigger学习总结
Note: The size of the trigger cannot be more than32K.
create table emp_trigger
as
select * from emp
CREATE OR REPLACE TRIGGERPrint_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON emp_trigger
FOR EACH ROW
WHEN (new.EMPNO > 5000)
DECLARE
sal_diff number;
BEGIN
sal_diff := :NEW.SAL - :OLD.SAL;
dbms_output.put('Old salary: ' || :old.sal);
dbms_output.put(' New salary: ' || :new.sal);
dbms_output.put_line(' Difference ' || sal_diff);
END;
insert intoemp_trigger(empno,sal) values(6000,20);
Oldsalary: New salary: 20 Difference
ONLY the after for each row can look at a"stable" value in the :new record. So, if you doing data validation (eg: this column must be between 1 and30 when this condition is true), you should do that in an AFTER trigger becausethe BEFORE triggers may change the value on you (and since BEFORE triggers firein SOME RANDOM order -- you cannot be assured that your BEFORE trigger firesbefore or after some other BEFORE trigger)
So, use BEFORE FOR EACH row when you needto WRITE to the :new record use AFTER FOR EACH row triggers when you want toVALIDATE the final values in the :new record
In general, you use BEFORE or AFTER triggers to achieve the following results:
■Use BEFORE row triggers tomodify the row before the row data is written to disk.
■Use AFTER row triggers toobtain, and perform operations, using the row ID.
INSTEAD OF
INSTEAD OF triggers can bedefined only on views, not on tables.
drop table tr_emp purge;
create table tr_emp
(
empno number(4) primary key,
ename varchar2(50),
deptno number(10)
)
drop table tr_dept purge;
create table tr_dept
(
deptno number(4) primary key,
dname varchar2(50)
);
insert into tr_emp
selectempno,ename,deptno from emp;
insert into tr_dept
selectdeptno,dname from dept;
select * from tr_emp
CREATE OR REPLACE VIEW v_emp_dept AS
SELECTe.empno,e.ename, d.dname, d.deptno
FROM tr_empe, tr_dept d
WHERE e.deptno= d.deptno
Select * from tr_emp select * from tr_dept
EMPNO
ENAME
DEPTNO
5555
ggg
10
7369
SMITH
20
7499
ALLEN
30
7521
WARD
30
7566
JONES
20
7654
MA & RTIN
30
7698
BLAKE
30
7782
CLARK
10
7788
SCOTT
20
7839
KING
10
7844
TURNER
30
7900
JAMES
30
7902
FORD
20
7934
MILLER
10
7876
ADAMS
20
DEPTNO
DNAME
55
55
10
ACCOUNTING
20
RESEARCH
30
SALES
40
OPERATIONS
CREATE OR REPLACE TRIGGER v_emp_dept_insert
INSTEAD OF INSERT ON v_emp_dept
REFERENCING NEW AS n --新数据行用n别名
FOR EACH ROW
DECLARE
rowcnt number;
BEGIN
SELECT COUNT(*) INTO rowcnt FROM tr_emp WHERE empno = :n.empno;
IF rowcnt = 0 THEN --表示是新的雇员
INSERT INTO tr_emp (empno, ename,deptno) VALUES (:n.empno,:n.ename, :n.deptno);--增加新的雇员信息
ELSE --否则是老雇员，则更新雇员的姓名即可
UPDATE tr_emp
SET tr_emp.ename = :n.ename,
tr_emp.deptno = :n.deptno
WHERE tr_emp.empno = :n.empno; --更新
END IF;
SELECT COUNT(*) INTO rowcnt FROM tr_dept WHERE deptno = :n.deptno;
IF rowcnt = 0 THEN --表示是新的部门
INSERT INTO tr_dept (deptno, dname) VALUES (:n.deptno,:n.dname);
ELSE
UPDATE tr_dept
SET tr_dept.dname = :n.dname
WHERE tr_dept.deptno = :n.deptno;
END IF;
END;
FOR EACH ROW Option
CREATE TABLE tr_Emp_log (
Emp_id NUMBER,
Log_date DATE,
New_salary NUMBER(16,2),
Action VARCHAR2(20)
);
create table tr_emp2
(
empno number(4