oracle替代触发器的一种用法

原文地址: 快速增加表的默认值的方法

原文描述了一种尽量短时间锁定表的情况下给大表增加带默认值的字段的方法,主要思路如下:

  1. 先增加需要的字段,但不带默认值
  2. 修改新增字段的默认值
  3. 创建视图,将表中新增加字段的值,使用nvl函数转化为默认值
  4. 利用替代触发器,解决视图更新的问题
– 1. 创建测试的表
system@ORCL>create table t_person(id number); 

Table created.

system@ORCL>insert into t_person values (1);

1 row created.

system@ORCL>insert into t_person values (2);

1 row created.

system@ORCL>commit;

Commit complete.

– 2. 增加新字段,更改默认值
system@ORCL>alter table t_person add (name varchar(20));

Table altered.

system@ORCL>alter table t_person modify (name default ‘unknown’);

Table altered.

system@ORCL>select * from t_person;
ID NAME
———- —————————————-
1
2

– 3. 将原表重命名,创建同名的视图
system@ORCL>alter table t_person rename to t_person_old;

Table altered.

system@ORCL>create view t_person(id, name) as select id, nvl(name,
‘unknown’) name from t_person_old;

View created.

system@ORCL>select * from t_person;
ID NAME
———- —————————————-
1 unknown
2 unknown

– 4. 视图无法插入,使用插入替代触发器解决这个问题
system@ORCL>insert into t_person values(3, ‘test’);
insert into t_person values(3, ‘test’)
*
ERROR at line 1:
ORA-01733: virtual column not allowed here

system@ORCL>create or replace trigger tri_insert_t_person
2 instead of insert on t_person
3 begin
4 insert into t_person_old values (:new.id, :new.name);
5 end;
6 /

Trigger created.

system@ORCL>insert into t_person values(3, ‘test’);

1 row created.

system@ORCL>insert into t_person(id) values(4);

1 row created.

system@ORCL>commit;

Commit complete.

system@ORCL>select * from t_person;
ID NAME
———- —————————————-
1 unknown
2 unknown
3 test
4 unknown

– 5. 使用更新替代触发器
system@ORCL>update t_person set name = ‘peter’ where id = 4;
update t_person set name = ‘peter’ where id = 4
*
ERROR at line 1:
ORA-01733: virtual column not allowed here

system@ORCL>create or replace trigger tri_update_t_person
2 instead of update on t_person
3 begin
4 update t_person_old set name = :new.name where id = :new.id;
5 end;
6 /

Trigger created.

system@ORCL>update t_person set name = ‘peter’ where id = 4;

1 row updated.

system@ORCL>select * from t_person where id = 4;
ID NAME
———- —————————————-
4 peter

Comments are closed.