oracle没有mysql的AUTO_INCREMENT功能,所以自增字段主要依靠自增序列来实现,本文简单的利用自增序列加触发器实现自增字段。
create table T_COMPANY
(
TREEID NUMBER(10) not null primary key,
PID NUMBER(10) default (-1),
TITLE NVARCHAR2(32) not null unique
);
创建序列:
create sequence company_tree_id_seq
increment by 1
start with 4705
maxvalue 999999999;
创建触发器:
create or replace trigger company_tree_id_tri
before insert on T_COMPANY
for each row when ( new.TREEID is null )
begin
select COMPANY_TREE_ID_SEQ.nextval into:new.TREEID from DUAL;
end;
在更新一条数据时,常常要有更新字段和修改字段,这个用触发器也特别合适如
新增记录时
create or replace trigger export_field_tpl_dld
before insert on T_EXPORTFIELDSTEMPLATE
for each row when ( new.UPDATETIME is null )
begin
select SYSDATE into:new.UPDATETIME from DUAL;
end;
更新记录时
create or replace trigger export_field_tpl_dld
before UPDATE on T_EXPORTFIELDSTEMPLATE
for each row when ( new.UPDATETIME is null )
begin
select SYSDATE into:new.UPDATETIME from DUAL;
end;