oracle 给表创建触发器,oracle创建表、序列、触发器

脚本如下

-- 1. 创建表

-- Create table

create table base

(

id number,

descr varchar2(200),

create_date date,

lst_upd_date date

)

;

-- Create/Recreate primary, unique and foreign key constraints

alter table base

add constraint pk_base primary key (ID);

/

-- 2. 创建序列

-- Create sequence

create sequence seq_base

minvalue 1

maxvalue 999999999999999999999999999999

start with 1

increment by 1

cache 3

order;

/

-- 3. 创建触发器

create or replace trigger trigger_base

before insert or update on base

for each row

declare

c date := sysdate;

begin

if INSERTING then

:new.create_date := c;

:new.lst_upd_date := c;

elsif UPDATING then

:new.lst_upd_date := c;

end if;

end trigger_base;

/

-- 4. insert update 验证序列和触发器

insert into base(id, descr) values(seq_base.nextval, 'v1');

update base set descr = 'v2';

commit;