Give you a sample:CREATE SEQUENCE station_id
INCREMENT BY 1
START WITH 0
MINVALUE 1
MAXVALUE 999999999999999999999999999
NOCYCLE
NOORDER
CACHE 20CREATE OR REPLACE TRIGGER insert_station_id
BEFORE INSERT
ON s_station_info
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
begin
select create_station_id.nextval into :new.station_id from dual;
end;
INCREMENT BY 1
START WITH 0
MINVALUE 1
MAXVALUE 999999999999999999999999999
NOCYCLE
NOORDER
CACHE 20CREATE OR REPLACE TRIGGER insert_station_id
BEFORE INSERT
ON s_station_info
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
begin
select create_station_id.nextval into :new.station_id from dual;
end;
第一步:创建SEQUENCE
create sequence s_policy_id increment by 1 start with 1 maxvalue 999999999;
第二步:创建一个基于该表的before insert 触发器,在触发器中使用该SEQUENCE
----t_pol_main为插入的表名,其中policy_id 为此表的一个要自动增加的字段
create or replace trigger tri_policy_id
before insert on t_pol_main
referencing old as old new as new
for each row
declare
num number;
begin
select N_POLICY_ID.Nextval into num from dual;
:new.policy_id := num;
end;
以下是我写的代码,请高手指教。create sequence Xs_bsc_SalesRecord_SerialNum increment by 1 start with 1 maxvalue 999999999;reate or replace trigger tri_SerialNum
before insert on Xs_bsc_SalesRecord
referencing old as old new as new
for each row
declare
num number;
begin
select N_SerialNum.Nextval into num from dual;
:new.SerialNum := num;
end;
create table msct_test
(
user_id number(10),
user_name varchar2(32)
)
/--创建序列
CREATE SEQUENCE mscs_test
INCREMENT BY 1
START WITH 0
MINVALUE 0
MAXVALUE 100000000
NOCYCLE
NOORDER
CACHE 20
/--创建触发器
CREATE OR REPLACE TRIGGER insert_mscs_test
BEFORE INSERT
ON msct_test
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
begin
select mscs_test.nextval into :new.user_id from dual;
end;
/--测试数据
insert into msct_test(user_name) values('BuShi');
insert into msct_test(user_name) values('KeLinDun');
insert into msct_test(user_name) values('BillGates');--检查结果
SQL> select * from msct_test; USER_ID USER_NAME
---------- --------------------------------
0 BuShi
1 KeLinDun
2 BillGates--注意
--(1)如果你删除了其中一条或多条记录甚至所有记录,由于使用的是序列,已经使用过的ID将不会被重新使用。
CREATE TABLE Xs_bsc_SalesRecord
(
SaleDate Date NOT NULL,
OfficeID Varchar2(20) NOT NULL,
CustomerID Varchar2(6),
ProductID Varchar2(20),
Quantity Number(9,2),
Price Number(9,2),
CurrencyType VARCHAR2(6),
Money Number(9,2),
AuditFlag Number(1),
Re Varchar2(200),
SerialNumber Number(9),
CONSTRAINT FK_Xs_bsc_SalesRecord1 FOREIGN KEY (OfficeID) REFERENCES Xs_bsc_OfficeInfo (OfficeID),
CONSTRAINT FK_Xs_bsc_SalesRecord2 FOREIGN KEY (CustomerID) REFERENCES Xs_Kh_Customer (CustomerID),
);--创建的序列
create sequence Xs_bsc_SalesRecordSN
INCREMENT BY 1
START WITH 1
MINVALUE 0
MAXVALUE 999999999
NOCYCLE
NOORDER
CACHE 20;
--创建的触发器
create or replace trigger insert_Xs_bsc_SalesRecordSN
before insert on Xs_bsc_SalesRecord
referencing old as old new as new
for each row
begin
select Xs_bsc_SalesRecordSN.nextval into :new.SerialNum from dual;
end;
可是触发器在执行是总出现警告:创建的触发器带有编译错误.
请问我的代码哪里有问题?
谢谢给我帮助的朋友们:marvinhong(边城骆驼) 、 kerisyml(魂之利刃) 、 qdjoefan(五月的风) 。
“五月的风”你写得真详细,谢谢!
楼上的这位老兄确实不错
就是我们的老师也没你那么
有耐心的讲过
请问 dual 表示什么意思呢