刚才有一个类似的问题,你可以看一下,懒得写了,考一个. 一。创建序列 CREATE SEQUENCE TEST INCREMENT BY 1 START WITH 1 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE CACHE 20 NOORDER二 USERADD 会员触发器CREATE OR REPLACE TRIGGER USERADD BEFORE INSERT ON MEMBERRG FOR EACH ROW BEGIN SELECT text.NEXTVAL INTO :NEW.NUM FROM DUAL;
END;
1、Create Sequence 2、Create Trigger CREATE OR REPLACE TRIGGER bifer_build_id BEFORE INSERT ON table_name FOR EACH ROW BEGIN INERT INTO table_name VALUES(Sequence_name.NEXTVAL,…………); END bifer_build_id;
SORRY ,触发器主体部分写错了,同意楼上
请写清楚点 比如我的表定义如下: CREATE TYPE PT AS OBJECT CREATE TYPE LFOBJ AS OBJECT ( uniqueid NUMBER, color NUMBER, typeid NUMBER connlist LONG, pointlist MDSYS.SDO_GEOMETRY ) CREATE TABLE cola ( layerid NUMBER, lineinfo LFOBJ, fillinfo LFOBJ ) nested table lineinfo store as cola_line_info_tab nested table fillinfo store as cola_fill_info_tab ----对于以上建议的表cola,我希望layerid,LFOBJ.uniqueid,LFOBJ.uniqueid 三个都是自动增,我在插入时不给这三个字段赋值
使用序列和触发器就可以阿CREATE OR REPLACE TRIGGER bifer_build_id BEFORE INSERT ON cola FOR EACH ROW BEGIN :new.cola:=Sequence_name.NEXTVAL; END bifer_build_id;为其他两个表创建类似的触发器即可
应该这样 SQL> CREATE OR REPLACE TRIGGER TR1 2 BEFORE INSERT ON tmp 3 FOR EACH ROW 4 declare 5 com_num NUMBER; 6 BEGIN 7 select s_id.nextval into :NEW.a from 8 END TR1; 9 /触发器已创建实际:631 SQL> select * from tmp; A B C --------- --------- --------- 2 6 123 999 111 222 888 222 333 999 333 222 2 2 333 2 2 333 3 3已选择7行。实际:90SQL> insert into tmp values(null,1,1);已创建 1 行。实际:240 SQL> select * from tmp; A B C --------- --------- --------- 2 6 123 999 111 222 888 222 333 999 333 222 2 2 333 2 2 333 3 3 1 1 1已选择8行。实际:100
一。创建序列
CREATE SEQUENCE TEST INCREMENT BY 1 START WITH 1
MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE
CACHE 20 NOORDER二 USERADD 会员触发器CREATE OR REPLACE TRIGGER USERADD
BEFORE INSERT ON MEMBERRG
FOR EACH ROW
BEGIN
SELECT text.NEXTVAL INTO :NEW.NUM FROM DUAL;
END;
2、Create Trigger
CREATE OR REPLACE TRIGGER bifer_build_id
BEFORE INSERT
ON table_name
FOR EACH ROW
BEGIN
INERT INTO table_name VALUES(Sequence_name.NEXTVAL,…………);
END bifer_build_id;
比如我的表定义如下:
CREATE TYPE PT AS OBJECT
CREATE TYPE LFOBJ AS OBJECT
( uniqueid NUMBER,
color NUMBER,
typeid NUMBER
connlist LONG,
pointlist MDSYS.SDO_GEOMETRY
)
CREATE TABLE cola
( layerid NUMBER,
lineinfo LFOBJ,
fillinfo LFOBJ
)
nested table lineinfo store as cola_line_info_tab
nested table fillinfo store as cola_fill_info_tab
----对于以上建议的表cola,我希望layerid,LFOBJ.uniqueid,LFOBJ.uniqueid
三个都是自动增,我在插入时不给这三个字段赋值
BEFORE INSERT
ON cola
FOR EACH ROW
BEGIN
:new.cola:=Sequence_name.NEXTVAL;
END bifer_build_id;为其他两个表创建类似的触发器即可
SQL> CREATE OR REPLACE TRIGGER TR1
2 BEFORE INSERT ON tmp
3 FOR EACH ROW
4 declare
5 com_num NUMBER;
6 BEGIN
7 select s_id.nextval into :NEW.a from
8 END TR1;
9 /触发器已创建实际:631
SQL> select * from tmp; A B C
--------- --------- ---------
2 6
123 999 111
222 888 222
333 999 333
222 2 2
333 2 2
333 3 3已选择7行。实际:90SQL> insert into tmp values(null,1,1);已创建 1 行。实际:240
SQL> select * from tmp; A B C
--------- --------- ---------
2 6
123 999 111
222 888 222
333 999 333
222 2 2
333 2 2
333 3 3
1 1 1已选择8行。实际:100