Oracle与Mysql中的触发器（Trigger）对比

Oracle触发器 Demo(通过触发器结合创建序列(sequence)的方式实现字段递增)

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
)
;
<!--创建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 触发器（Trigger）Demo(业务等同于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 ;