Oracle觸發(fā)器 Demo(通過觸發(fā)器結(jié)合創(chuàng)建序列(sequence)的方式實(shí)現(xiàn)字段遞增)

create table if not EXISTS hpwf_order_col_default(
    id int not null,
    fieldName int not null,
    fieldColumn varchar(1000) not null
)
;
create sequence hpwf_order_col_default_seq increment by 1 start with 1 nomaxvalue nocycle cache 10
;
create or replace trigger hpwf_order_col_default_trigger
before insert on hpwf_order_col_default for each row
begin
<!--這里通過sequence.nextval方法來將sequence 的next value賦值給最新插入的id-->
select hpwf_order_col_default_seq.nextval into :new.id from dual;
end;
;
create table if not EXISTS hpwf_order_col_use(
    id int not null,
    eid int null,
    tabid int null,
    colid int null,
    orderNum int null,
    orderType int null
)
;
<!--創(chuàng)建sequence-->
create sequence hpwf_order_col_use_seq increment by 1 start with 1 nomaxvalue nocycle cache 10
;
create or replace trigger hpwf_order_col_use_trigger
before insert on hpwf_order_col_use for each row
begin
select max(id)+1 into :new.id from hpwf_order_col_use;
end;
;

Mysql 觸發(fā)器（Trigger）Demo(業(yè)務(wù)等同于Oracle)

create table if not EXISTS hpwf_order_col_use(
    id int not null,
    eid int null,
    tabid int null,
    colid int null,
    orderNum int null,
    orderType int null
)
;
DROP TRIGGER IF EXISTS hpwf_order_col_use_trigger;
DELIMITER //  
create trigger hpwf_order_col_use_trigger
before insert on hpwf_order_col_use for each row
begin
set @num=(select count(id) from hpwf_order_col_use);
if @num=0 THEN
set new.id=1;
ELSE 
set @newId=(select MAX(id)+1 FROM hpwf_order_col_use);
set new.id= @newId; 
end if;
end
// 
DELIMITER ;