Oracle 序列+触发器
删除序列
DROP IAM_USER_PUSH_ID_SEQ;
添加序列
CREATE SEQUENCE "IAM_USER_PUSH_ID_SEQ" INCREMENT BY 1 MINVALUE 1 MAXVALUE 999999999999999999999999 CYCLE NOCACHE ORDER ;
触发器 -简单
create or replace trigger IAM_USER_PUSH_ID_TRI
before insert on IAM_USER_PUSH
for each row
begin
select IAM_USER_PUSH_ID_SEQ.nextval into :new.id from dual;
end;
触发器-如果存在会替换
CREATE OR REPLACE TRIGGER "IAM_USER_PUSH_ID_TRI" BEFORE INSERT OR UPDATE ON IAM_USER_PUSH
FOR EACH ROW
DECLARE
v_count NUMBER(24) :=0;
v_newVal NUMBER(24) := 0;
BEGIN
IF INSERTING AND :new.id IS NULL THEN
SELECT IAM_USER_PUSH_ID_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
-- If this is the first time this table have been inserted into (sequence == 1)
SELECT COUNT(1) INTO v_count FROM IAM_USER_PUSH WHERE ID = v_newVal;
IF(v_count <> 0) THEN
LOOP
EXIT WHEN v_count = 0;
SELECT IAM_USER_PUSH_ID_SEQ.nextval INTO v_newVal FROM dual;
SELECT COUNT(1) INTO v_count FROM IAM_USER_PUSH WHERE ID = v_newVal;
END LOOP;
END IF;
-- assign the value from the sequence to emulate the identity column
:new.id := v_newVal;
END IF;
END;