--insert触发器提取增量
--delete触发器提取增量
--update触发器提取增量
----先建立两个增量表:一个插入表,一个删除表.
create table emp_insert_incremental(
empno number(4),
ename varchar(10)
)
create table emp_delete_incremental(
empno number(4),
ename varchar(10)
)
----------------
create or replace trigger emp_insert_trigger
after insert
on emp
for each row
begin
insert into emp_insert_incremental(empno,ename) values (:new.empno,:new.ename);
end emp_insert_trigger;
---------------
insert into emp(empno,ename)values(100,'jjj');
insert into emp(empno,ename)values(200,'ppp');
insert into emp(empno,ename)values(300,'3as');
---------------
create or replace trigger emp_update_trigger
after update
on emp
for each row
begin
insert into emp_insert_incremental(empno,ename) values (:new.empno,:new.ename);
insert into emp_delete_incremental(empno,ename) values (:old.empno,:old.ename);
end emp_update_trigger;
----------------
update emp set ename='aaa' where empno=100;
---------------
create or replace trigger emp_delete_trigger
after delete
on emp
for each row
begin
insert into emp_delete_incremental(empno,ename) values (:old.empno,:old.ename);
end emp_delete_trigger;
----------------------
delete from emp_delete_incremental where empno=200;
--=================================三个触发器合为一个触发器提取增量
create or replace trigger emp_incremental_trigger
after insert or update of empno,ename or delete
on emp
for each row
begin
case
when inserting then
insert into emp_insert_incremental(empno,ename) values (:new.empno,:new.ename);
when updating then
insert into emp_insert_incremental(empno,ename) values (:new.empno,:new.ename);
insert into emp_delete_incremental(empno,ename) values (:old.empno,:old.ename);
when deleting then
insert into emp_delete_incremental(empno,ename) values (:old.empno,:old.ename);
end case;
end emp_incremental_trigger;