create or replace trigger insert_for_autoinc
before insert on ZHONGHENG.t_cn_state
for each row
declare
begin
select count(*) into i from ZHONGHENG.t_cn_state where substr(sysid,1,8)=to_char(sysdate,'yyyymmdd');
if(i>0)then
drop sequence state_id;
create sequence state_id
minvalue 1
maxvalue 999999
start with 1 increment by 1 nocache;
end if;
update ZHONGHENG.t_cn_state set sysid=to_char(sysdate,'yyyymmdd')||nid; end insert_for_autoinc;
oracle触发器序列trigger
before insert on ZHONGHENG.t_cn_state
for each row
declare
begin
select count(*) into i from ZHONGHENG.t_cn_state where substr(sysid,1,8)=to_char(sysdate,'yyyymmdd');
if(i>0)then
drop sequence state_id;
create sequence state_id
minvalue 1
maxvalue 999999
start with 1 increment by 1 nocache;
end if;
update ZHONGHENG.t_cn_state set sysid=to_char(sysdate,'yyyymmdd')||nid; end insert_for_autoinc;
oracle触发器序列trigger
BEFORE INSERT ON zhongheng.t_cn_state
FOR EACH ROW
DECLARE
i NUMBER;
BEGIN
SELECT COUNT(*) INTO i FROM zhongheng.t_cn_state WHERE substr(sysid, 1, 8) = to_char(SYSDATE, 'yyyymmdd');
IF (i > 0) THEN
EXECUTE IMMEDIATE 'drop sequence state_id';
EXECUTE IMMEDIATE 'CREATE sequence state_id minvalue 1 maxvalue 999999 START WITH 1 increment BY 1 nocache';
END IF;
UPDATE zhongheng.t_cn_state SET sysid = to_char(SYSDATE, 'yyyymmdd') || nid;END insert_for_autoinc;
BEFORE INSERT ON t_cn_TEST
FOR EACH ROW
DECLARE
i NUMBER;
BEGIN
/* SELECT COUNT(*) INTO i FROM t_cn_test;
IF (i > 0) THEN
UPDATE t_cn_test SET kh = '1';
--EXECUTE IMMEDIATE 'drop sequence state_id';
--EXECUTE IMMEDIATE 'CREATE sequence state_id minvalue 1 maxvalue 999999 START WITH 1 increment BY 1 nocache';
END IF;*/
pro_reset_seq;
END insert_for_autoinc;
create or replace procedure pro_reset_seq
as
BEGIN
UPDATE t_cn_test SET kh = '1';
EXECUTE IMMEDIATE 'drop sequence state_id';
EXECUTE IMMEDIATE 'CREATE sequence state_id minvalue 1 maxvalue 999999 START WITH 1 increment BY 1 nocache';
END pro_reset_seq;
SELECT state_id.nextval from dual;create sequence state_id
minvalue 1
maxvalue 9
start with 1
increment by 1
nocache;EXEC pro_reset_seq;
已完成