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

/*
 Navicat Premium Data Transfer

 Source Server         : 172.18.32.32 正式库
 Source Server Type    : Oracle
 Source Server Version : 110200
 Source Host           : 172.18.32.32:1521
 Source Schema         : HGIBOSS

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

 Date: 10/07/2020 10:10:41
*/


-- ----------------------------
-- Table structure for TP_MST_WORKSTATIONUSERDETAIL
-- ----------------------------
DROP TABLE "HGIBOSS"."TP_MST_WORKSTATIONUSERDETAIL";
CREATE TABLE "HGIBOSS"."TP_MST_WORKSTATIONUSERDETAIL" (
  "WORKSTATIONUSERDETAILID" NUMBER NOT NULL,
  "WORKSTATIONUSERID" NUMBER,
  "WORKSTATIONID" NUMBER,
  "USERID" NUMBER NOT NULL,
  "USERCODE" NVARCHAR2(20) NOT NULL,
  "STAFFID" NUMBER NOT NULL,
  "STAFFSTATUS" NUMBER 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,
  "UJOBSID" 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 "HGIBOSS"."TP_MST_WORKSTATIONUSERDETAIL"."WORKSTATIONUSERDETAILID" IS '工位用户配置明细ID';
COMMENT ON COLUMN "HGIBOSS"."TP_MST_WORKSTATIONUSERDETAIL"."WORKSTATIONUSERID" IS '工位用户配置ID';
COMMENT ON COLUMN "HGIBOSS"."TP_MST_WORKSTATIONUSERDETAIL"."WORKSTATIONID" IS '工位ID';
COMMENT ON COLUMN "HGIBOSS"."TP_MST_WORKSTATIONUSERDETAIL"."USERID" IS '工号ID';
COMMENT ON COLUMN "HGIBOSS"."TP_MST_WORKSTATIONUSERDETAIL"."USERCODE" IS '工号';
COMMENT ON COLUMN "HGIBOSS"."TP_MST_WORKSTATIONUSERDETAIL"."STAFFID" IS '员工ID';
COMMENT ON COLUMN "HGIBOSS"."TP_MST_WORKSTATIONUSERDETAIL"."STAFFSTATUS" IS '员工状态';
COMMENT ON COLUMN "HGIBOSS"."TP_MST_WORKSTATIONUSERDETAIL"."REMARKS" IS '备注';
COMMENT ON COLUMN "HGIBOSS"."TP_MST_WORKSTATIONUSERDETAIL"."ACCOUNTID" IS '帐套ID (所属帐套ID)';
COMMENT ON COLUMN "HGIBOSS"."TP_MST_WORKSTATIONUSERDETAIL"."VALUEFLAG" IS '有效标识 (1：正常  0：停用)';
COMMENT ON COLUMN "HGIBOSS"."TP_MST_WORKSTATIONUSERDETAIL"."CREATETIME" IS '创建时间 (更新时，不更新该数据)';
COMMENT ON COLUMN "HGIBOSS"."TP_MST_WORKSTATIONUSERDETAIL"."CREATEUSERID" IS '创建数据操作员ID (更新时，不更新该数据)';
COMMENT ON COLUMN "HGIBOSS"."TP_MST_WORKSTATIONUSERDETAIL"."UPDATETIME" IS '更新时间 (新建时，取新建时间；更新时，取最新时间更新)';
COMMENT ON COLUMN "HGIBOSS"."TP_MST_WORKSTATIONUSERDETAIL"."UPDATEUSERID" IS '更新数据操作员ID (新建时，取创建者ID；更新时，取更新者ID)';
COMMENT ON COLUMN "HGIBOSS"."TP_MST_WORKSTATIONUSERDETAIL"."OPTIMESTAMP" IS '操作时间戳 (防止多用户同时对单一记录操作的时间戳)';
COMMENT ON COLUMN "HGIBOSS"."TP_MST_WORKSTATIONUSERDETAIL"."UJOBSID" IS '工种ID (工号中包含的工种（Jobs）)';
COMMENT ON TABLE "HGIBOSS"."TP_MST_WORKSTATIONUSERDETAIL" IS '工位工号明细';

-- ----------------------------
-- Primary Key structure for table TP_MST_WORKSTATIONUSERDETAIL
-- ----------------------------
ALTER TABLE "HGIBOSS"."TP_MST_WORKSTATIONUSERDETAIL" ADD CONSTRAINT "PK_MST_WORKSTATIONUSERDETAIL" PRIMARY KEY ("WORKSTATIONUSERDETAILID");

-- ----------------------------
-- Checks structure for table TP_MST_WORKSTATIONUSERDETAIL
-- ----------------------------
ALTER TABLE "HGIBOSS"."TP_MST_WORKSTATIONUSERDETAIL" ADD CONSTRAINT "SYS_C00210509" CHECK ("WORKSTATIONUSERDETAILID" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE "HGIBOSS"."TP_MST_WORKSTATIONUSERDETAIL" ADD CONSTRAINT "SYS_C00210512" CHECK ("USERID" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE "HGIBOSS"."TP_MST_WORKSTATIONUSERDETAIL" ADD CONSTRAINT "SYS_C00210513" CHECK ("USERCODE" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE "HGIBOSS"."TP_MST_WORKSTATIONUSERDETAIL" ADD CONSTRAINT "SYS_C00210514" CHECK ("STAFFID" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE "HGIBOSS"."TP_MST_WORKSTATIONUSERDETAIL" ADD CONSTRAINT "SYS_C00210515" CHECK ("STAFFSTATUS" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE "HGIBOSS"."TP_MST_WORKSTATIONUSERDETAIL" ADD CONSTRAINT "SYS_C00210516" CHECK ("ACCOUNTID" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE "HGIBOSS"."TP_MST_WORKSTATIONUSERDETAIL" ADD CONSTRAINT "SYS_C00210517" CHECK ("VALUEFLAG" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE "HGIBOSS"."TP_MST_WORKSTATIONUSERDETAIL" ADD CONSTRAINT "SYS_C00210518" CHECK ("CREATETIME" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE "HGIBOSS"."TP_MST_WORKSTATIONUSERDETAIL" ADD CONSTRAINT "SYS_C00210519" CHECK ("CREATEUSERID" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE "HGIBOSS"."TP_MST_WORKSTATIONUSERDETAIL" ADD CONSTRAINT "SYS_C00210520" CHECK ("UPDATETIME" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE "HGIBOSS"."TP_MST_WORKSTATIONUSERDETAIL" ADD CONSTRAINT "SYS_C00210521" CHECK ("UPDATEUSERID" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE "HGIBOSS"."TP_MST_WORKSTATIONUSERDETAIL" ADD CONSTRAINT "SYS_C00210522" CHECK ("OPTIMESTAMP" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE "HGIBOSS"."TP_MST_WORKSTATIONUSERDETAIL" ADD CONSTRAINT "SYS_C00210523" CHECK ("UJOBSID" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;

-- ----------------------------
-- Triggers structure for table TP_MST_WORKSTATIONUSERDETAIL
-- ----------------------------
CREATE TRIGGER "HGIBOSS"."TRG_MST_WORKSTATIONDETAIL_B_I" BEFORE INSERT ON "HGIBOSS"."TP_MST_WORKSTATIONUSERDETAIL" REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW 
DECLARE
BEGIN
  IF :NEW.WORKSTATIONUSERDETAILID IS NULL THEN
    SELECT SEQ_MST_WORKSTATIONDETAIL_ID.NEXTVAL INTO :NEW.WORKSTATIONUSERDETAILID FROM DUAL;
  END IF;
END TRG_MST_WORKSTATIONDETAIL_B_I;
/
CREATE TRIGGER "HGIBOSS"."TRG_MST_WORKSTATIONDETAIL_B_U" BEFORE UPDATE ON "HGIBOSS"."TP_MST_WORKSTATIONUSERDETAIL" 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_WORKSTATIONDETAIL_B_U;

/
-- Create sequence 
create sequence SEQ_MST_WORKSTATIONDETAIL_ID
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
cache 20;