oracle sql 为表创建序列和触发器

--1.为表创建序列,之后创建生成自增序列的触发器
--创建序列
CREATE SEQUENCE "TEST1"."SEQ_ZHUYUAN_RECORD" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 NOCACHE;

--创建【生成自增序列】触发器
CREATE TRIGGER "TEST1"."TR_ZHUYUAN_RECORD" BEFORE INSERT ON "TEST1"."ZHUYUAN_RECORD" REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW
BEGIN
SELECT SEQ_ZHUYUAN_RECORD.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
/

--应用
INSERT INTO "TEST1"."ZHUYUAN_RECORD"("PATIENT_ID", "ZHUYUAN_ID", "PATIENT_NAME") VALUES ('0000963345', '00223450001', '林佳');

--2.创建【自动生成更新时间】触发器
CREATE OR REPLACE TRIGGER TR_ZHUYUAN_RECORD_UPDATE
BEFORE UPDATE ON ZHUYUAN_RECORD
FOR EACH ROW
DECLARE
BEGIN
:NEW.UPDATE_TIME := SYSDATE;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

--应用
UPDATE ZHUYUAN_RECORD SET PATIENT_NAME='临时' WHERE ID='3'