【数据库】Oracle建表、创建序列、添加触发器生成自增主键

最新推荐文章于 2024-03-27 16:55:52 发布

原创最新推荐文章于 2024-03-27 16:55:52 发布 · 437 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#数据库 #oracle

这是一个关于在Oracle数据库中创建表格'T_ORDER'的SQL脚本，包括字段定义、约束条件和默认值。此外，还定义了一个名为'Auto_Trig_T_Order_copy1'的触发器，该触发器在插入记录时，如果'AUUID_0'字段为空，则自动赋予一个值。

CREATE TABLE "TEST"."T_ORDER" (
"AUUID_0" VARCHAR2 ( 255 ) NOT NULL ENABLE,
"Order_ID" VARCHAR2 ( 255 ) NOT NULL ENABLE,
"User_ID" VARCHAR2 ( 255 ) NOT NULL ENABLE,
"CREATE_DATE" TIMESTAMP ( 2 ) DEFAULT SYSDATE,
"UPDDATTIM_0" TIMESTAMP ( 2 ) DEFAULT SYSDATE,
CHECK ( "AUUID_0" IS NOT NULL ) ENABLE,
CHECK ( "Order_ID" IS NOT NULL ) ENABLE,
CHECK ( "User_ID" IS NOT NULL ) ENABLE
) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT ) TABLESPACE "USERS";
CREATE
OR REPLACE TRIGGER "TEST"."Auto_Trig_T_Order_copy1" BEFORE INSERT ON "TEST"."T_ORDER" REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW
WHEN ( new."AUUID_0" IS NULL ) BEGIN
SELECT
test_seq_SORDERQ.nextval INTO : new."AUUID_0"
FROM
dual;
END;
ALTER TRIGGER "TEST"."Auto_Trig_T_Order_copy1" ENABLE