oracle中触发器的创建,oracle触发器的简略创建

oracle触发器的简单创建

好久没有写触发器了，有些忘记，搜索一下，感觉这个还可以，于是就收藏咯。

用户表：创建触发器之前首先要创建序列

用户表序列：

create sequence users_seq;

用户表触发器：

create or replace trigger bifer_users_userid_pk

before insert

on users

for each row

begin

select users_seq.nextval into:new.userid from dual;

end;

/

商品表：

商品表序列：create sequence product_seq;

商品表触发器：

create or replace trigger bifer_product_itemid_pk

before insert

on product

for each row

begin

select product_seq.nextval into:new.itemid from dual;

end;

/

商品品牌表：

商品品牌表序列：create sequence brand_seq;

商品品牌表触发器：

create or replace trigger bifer_brand_brandid_pk

before insert

on brand

for each row

begin

select brand_seq.nextval into:new.brandid from dual;

end;

/

商品类别表：

商品表序列：create sequence class_seq;

商品表触发器：

create or replace trigger bifer_class_classid_pk

before insert

on class

for each row

begin

select class_seq.nextval into:new.classid from dual;

end;

/

一个触发器只能用于一个表，而一个表可以有最多三个触发器，商品品牌类别表是复合主键所以就为商品表建了两个触发器分贝作用于每个主键

商品品牌类别表：

商品品牌类别序列：create sequence brand_class_seq;

商品品牌类别触发器(brandid)：

create or replace trigger brand_class_brandid_pk

before insert

on brand_class

for each row

begin

select brand_class_seq.nextval into:new.brandid from dual;

end;

/

商品品牌类别触发器(classid)：

create or replace trigger brand_class_classid_pk

before insert

on brand_class

for each row

begin

select brand_class_seq.nextval into:new.classid from dual;

end;

/

在oracle中，为了方便常常用触发器及序列结合起来实现

先建表、再建序列、然后是触发器，最后测试

=============================================

create table foo(

id number primary key,

data varchar2(100));

create sequence foo_seq;

create or replace trigger bifer_foo_id_pk

before insert

on foo

for each row

begin

select foo_seq.nextval into :new.id from dual;

end;

/

insert into foo(data)

values('Chirstopher');

insert into foo(id,data)

values(5,'Sean');

select * from foo;