为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:DM8
【操作系统】:kylin V10
【CPU】:
【问题描述】*:
oracle触发器和序列迁移至达梦如何实现自增?
原oracle主键使用序列加触发器实现自增,如下:
create sequence USER1.SEQ_TEST
minvalue 1
maxvalue 999999999999999
start with 600
increment by 1
cache 20;
CREATE OR REPLACE TRIGGER USER1.TR_TEST
BEFORE INSERT ON USER1.TEST
for each row
where (NEW.ID is NULL)
begin
select USER1.SEQ_TEST.Nextval into :NEW.ID from dual;
end;
迁移至达梦后,如下:
create sequence "USER1"."SEQ_TEST"
INCREMENT BY 1
START WITH 630
MAXVALUE 999999999999999
NOCYCLE
CACHE 20
NOORDER;
create or replace trigger "USER1"."TR_TEST"
before INSERT
on "USER1"."TEST"
referencing OLD ROW AS "OLD" NEW ROW AS "NEW"
for each row
where (NEW.ID is NULL)
begin
select USER1.SEQ_TEST.Nextval into :NEW.ID from dual;
end;
用insert语句插入不带主键ID的数据,发现没使用序列的start with,是否触发器语法不对?
其实没必要用触发器。达梦支持设置列的默认值为序列的下一个值
我看你触发器的语句有问题,应该是when (NEW.ID is NULL)