HEGII.ss.mes.web/wwwroot/sqlupdate/工位管理/TP_MST_WORKSTATIONUSER.sql

/*
 Navicat Premium Data Transfer

 Source Server         : 172.18.32.35 hgtest
 Source Server Type    : Oracle
 Source Server Version : 110200
 Source Host           : 172.18.32.35:1521
 Source Schema         : HGTEST

 Target Server Type    : Oracle
 Target Server Version : 110200
 File Encoding         : 65001

 Date: 01/06/2020 11:16:30
*/


-- ----------------------------
-- Table structure for TP_MST_WORKSTATIONUSER
-- ----------------------------
DROP TABLE "HGTEST"."TP_MST_WORKSTATIONUSER";
CREATE TABLE "HGTEST"."TP_MST_WORKSTATIONUSER" (
  "WORKSTATIONUSERID" NUMBER NOT NULL,
  "WORKSTATIONID" NUMBER NOT NULL,
  "USERID" NUMBER NOT NULL,
  "USERCODE" NVARCHAR2(20) NOT NULL,
  "REMARKS" NVARCHAR2(128),
  "ACCOUNTID" NUMBER NOT NULL,
  "VALUEFLAG" NUMBER(1,0) DEFAULT 1    NOT NULL,
  "CREATETIME" DATE DEFAULT sysdate    NOT NULL,
  "CREATEUSERID" NUMBER NOT NULL,
  "UPDATETIME" DATE DEFAULT sysdate    NOT NULL,
  "UPDATEUSERID" NUMBER NOT NULL,
  "OPTIMESTAMP" TIMESTAMP(6) DEFAULT systimestamp    NOT NULL,
  "ORGANIZATIONID" NUMBER NOT NULL
)
TABLESPACE "USERS"
LOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 1
STORAGE (
  INITIAL 65536 
  NEXT 1048576 
  MINEXTENTS 1
  MAXEXTENTS 2147483645
  BUFFER_POOL DEFAULT
)
PARALLEL 1
NOCACHE
DISABLE ROW MOVEMENT
;
COMMENT ON COLUMN "HGTEST"."TP_MST_WORKSTATIONUSER"."WORKSTATIONUSERID" IS '工位用户配置ID';
COMMENT ON COLUMN "HGTEST"."TP_MST_WORKSTATIONUSER"."WORKSTATIONID" IS '工位ID';
COMMENT ON COLUMN "HGTEST"."TP_MST_WORKSTATIONUSER"."USERID" IS '工号ID';
COMMENT ON COLUMN "HGTEST"."TP_MST_WORKSTATIONUSER"."USERCODE" IS '工号';
COMMENT ON COLUMN "HGTEST"."TP_MST_WORKSTATIONUSER"."REMARKS" IS '备注';
COMMENT ON COLUMN "HGTEST"."TP_MST_WORKSTATIONUSER"."ACCOUNTID" IS '帐套ID (所属帐套ID)';
COMMENT ON COLUMN "HGTEST"."TP_MST_WORKSTATIONUSER"."VALUEFLAG" IS '有效标识 (1：正常  0：停用)';
COMMENT ON COLUMN "HGTEST"."TP_MST_WORKSTATIONUSER"."CREATETIME" IS '创建时间 (更新时，不更新该数据)';
COMMENT ON COLUMN "HGTEST"."TP_MST_WORKSTATIONUSER"."CREATEUSERID" IS '创建数据操作员ID (更新时，不更新该数据)';
COMMENT ON COLUMN "HGTEST"."TP_MST_WORKSTATIONUSER"."UPDATETIME" IS '更新时间 (新建时，取新建时间；更新时，取最新时间更新)';
COMMENT ON COLUMN "HGTEST"."TP_MST_WORKSTATIONUSER"."UPDATEUSERID" IS '更新数据操作员ID (新建时，取创建者ID；更新时，取更新者ID)';
COMMENT ON COLUMN "HGTEST"."TP_MST_WORKSTATIONUSER"."OPTIMESTAMP" IS '操作时间戳 (防止多用户同时对单一记录操作的时间戳)';
COMMENT ON COLUMN "HGTEST"."TP_MST_WORKSTATIONUSER"."ORGANIZATIONID" IS '工号所属部门';
COMMENT ON TABLE "HGTEST"."TP_MST_WORKSTATIONUSER" IS '工位工号配置';

-- ----------------------------
-- Primary Key structure for table TP_MST_WORKSTATIONUSER
-- ----------------------------
ALTER TABLE "HGTEST"."TP_MST_WORKSTATIONUSER" ADD CONSTRAINT "PK_MST_WORKSTATIONUSER" PRIMARY KEY ("WORKSTATIONUSERID");

-- ----------------------------
-- Checks structure for table TP_MST_WORKSTATIONUSER
-- ----------------------------
ALTER TABLE "HGTEST"."TP_MST_WORKSTATIONUSER" ADD CONSTRAINT "SYS_C0016296" CHECK ("WORKSTATIONUSERID" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE "HGTEST"."TP_MST_WORKSTATIONUSER" ADD CONSTRAINT "SYS_C0016297" CHECK ("WORKSTATIONID" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE "HGTEST"."TP_MST_WORKSTATIONUSER" ADD CONSTRAINT "SYS_C0016298" CHECK ("USERID" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE "HGTEST"."TP_MST_WORKSTATIONUSER" ADD CONSTRAINT "SYS_C0016299" CHECK ("USERCODE" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE "HGTEST"."TP_MST_WORKSTATIONUSER" ADD CONSTRAINT "SYS_C0016300" CHECK ("ACCOUNTID" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE "HGTEST"."TP_MST_WORKSTATIONUSER" ADD CONSTRAINT "SYS_C0016301" CHECK ("VALUEFLAG" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE "HGTEST"."TP_MST_WORKSTATIONUSER" ADD CONSTRAINT "SYS_C0016302" CHECK ("CREATETIME" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE "HGTEST"."TP_MST_WORKSTATIONUSER" ADD CONSTRAINT "SYS_C0016303" CHECK ("CREATEUSERID" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE "HGTEST"."TP_MST_WORKSTATIONUSER" ADD CONSTRAINT "SYS_C0016304" CHECK ("UPDATETIME" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE "HGTEST"."TP_MST_WORKSTATIONUSER" ADD CONSTRAINT "SYS_C0016305" CHECK ("UPDATEUSERID" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE "HGTEST"."TP_MST_WORKSTATIONUSER" ADD CONSTRAINT "SYS_C0016306" CHECK ("OPTIMESTAMP" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE "HGTEST"."TP_MST_WORKSTATIONUSER" ADD CONSTRAINT "SYS_C0016307" CHECK ("ORGANIZATIONID" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE "HGTEST"."TP_MST_WORKSTATIONUSER" ADD CONSTRAINT "SYS_C0020118" CHECK ("WORKSTATIONUSERID" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE "HGTEST"."TP_MST_WORKSTATIONUSER" ADD CONSTRAINT "SYS_C0020119" CHECK ("WORKSTATIONID" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE "HGTEST"."TP_MST_WORKSTATIONUSER" ADD CONSTRAINT "SYS_C0020120" CHECK ("USERID" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE "HGTEST"."TP_MST_WORKSTATIONUSER" ADD CONSTRAINT "SYS_C0020121" CHECK ("USERCODE" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE "HGTEST"."TP_MST_WORKSTATIONUSER" ADD CONSTRAINT "SYS_C0020122" CHECK ("ACCOUNTID" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE "HGTEST"."TP_MST_WORKSTATIONUSER" ADD CONSTRAINT "SYS_C0020123" CHECK ("VALUEFLAG" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE "HGTEST"."TP_MST_WORKSTATIONUSER" ADD CONSTRAINT "SYS_C0020124" CHECK ("CREATETIME" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE "HGTEST"."TP_MST_WORKSTATIONUSER" ADD CONSTRAINT "SYS_C0020125" CHECK ("CREATEUSERID" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE "HGTEST"."TP_MST_WORKSTATIONUSER" ADD CONSTRAINT "SYS_C0020126" CHECK ("UPDATETIME" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE "HGTEST"."TP_MST_WORKSTATIONUSER" ADD CONSTRAINT "SYS_C0020127" CHECK ("UPDATEUSERID" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE "HGTEST"."TP_MST_WORKSTATIONUSER" ADD CONSTRAINT "SYS_C0020128" CHECK ("OPTIMESTAMP" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE "HGTEST"."TP_MST_WORKSTATIONUSER" ADD CONSTRAINT "SYS_C0020129" CHECK ("ORGANIZATIONID" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;

-- ----------------------------
-- Triggers structure for table TP_MST_WORKSTATIONUSER
-- ----------------------------
CREATE TRIGGER "HGTEST"."TRG_MST_WORKSTATIONUSER_B_I" BEFORE INSERT ON "HGTEST"."TP_MST_WORKSTATIONUSER" REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW 
DECLARE
BEGIN
  IF :NEW.WORKSTATIONUSERID IS NULL THEN
    SELECT SEQ_MST_WORKSTATIONUSER_ID.NEXTVAL INTO :NEW.WORKSTATIONUSERID FROM DUAL;
  END IF;
END TRG_MST_WORKSTATIONUSER_B_I;
/
CREATE TRIGGER "HGTEST"."TRG_MST_WORKSTATIONUSER_B_U" BEFORE UPDATE ON "HGTEST"."TP_MST_WORKSTATIONUSER" REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW 
DECLARE
BEGIN
  SELECT SYSDATE INTO :NEW.UPDATETIME FROM DUAL;
  SELECT SYSTIMESTAMP INTO :NEW.OPTIMESTAMP FROM DUAL;
END TRG_MST_WORKSTATIONUSER_B_U;
/
-- Create sequence 
create sequence SEQ_MST_WORKSTATIONUSER_ID
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
cache 20;