创建表语句
CREATE TABLE product (
id NUMBER(11) NOT NULL ,
name varchar(255) NOT NULL,
price float NOT NULL,
PRIMARY KEY ( id )
);
创建触发器语句
--创建递增触发器
--drop TRIGGER trig_product_in;
CREATE OR REPLACE TRIGGER trig_product_in
BEFORE INSERT ON product
FOR EACH ROW
declare
nextid number(11);
BEGIN
if:new.id is null then
select max(id) into nextid from product;
if nextid is null then
:new.id := 1;
else
:new.id := nextid+1;
end if;
end if;
END;
/
整体思路是:先判断插入的数据中ID列new.id是否为空,如果不等于空就退出。如果等于空则查询表中ID列的最大值赋值给变量nextid。如果nextid为空则表中无数据,把new.id=1,退出。如果nextid不为空则把new.id := nextid+1,退出。