Example SQL: create/drop/purge table, create index, create Trigger

The following example sql file department.sql shows you how to create/drop/purge table, create index, sequence and triggers.

---------------------------------------------


DROP TABLE DEPARTMENT PURGE;

DROP SEQUENCE SEQ_DEPARTMENT;

CREATE TABLE DEPARTMENT (
  DEPARTMENT_SAK    NUMBER(8,0)    NOT NULL,
  DEPARTMENT_CODE    VARCHAR2(3 BYTE)    NOT NULL,
  DEPARTMENT_NM   VARCHAR2(100 BYTE)    NOT NULL,
  CREATE_USER_ID     VARCHAR2(20)    DEFAULT substr(USER,1,20)    NOT NULL,
  CREATE_TIMSTM      TIMESTAMP(6)    DEFAULT SYSTIMESTAMP    NOT NULL,
  LAST_UPDTD_USER_ID VARCHAR2(20)    DEFAULT substr(USER,1,20)    NOT NULL,
  LAST_UPDTD_TIMSTM  TIMESTAMP(6)    DEFAULT SYSTIMESTAMP    NOT NULL);

ALTER TABLE DEPARTMENT
ADD CONSTRAINT PK_DEPARTMENT PRIMARY KEY ( DEPARTMENT_SAK );
 
COMMENT ON COLUMN DEPARTMENT.DEPARTMENT_SAK IS 'Primary Key';
COMMENT ON COLUMN DEPARTMENT.DEPARTMENT_CODE IS 'Department Code';
COMMENT ON COLUMN DEPARTMENT.DEPARTMENT_NM IS 'department name in English';
COMMENT ON COLUMN DEPARTMENT.CREATE_USER_ID IS 'The ID of the Portal user who created the record';
COMMENT ON COLUMN DEPARTMENT.CREATE_TIMSTM IS 'System data timestamp';
COMMENT ON COLUMN DEPARTMENT.LAST_UPDTD_USER_ID IS 'The ID of the Portal user who updated the record';
COMMENT ON COLUMN DEPARTMENT.LAST_UPDTD_TIMSTM IS 'System data timestamp';

CREATE UNIQUE INDEX IDX_DEPARTMENT ON DEPARTMENT (DEPARTMENT_CODE);

CREATE SEQUENCE SEQ_DEPARTMENT
  INCREMENT BY 1
  MAXVALUE 99999999
  MINVALUE 1
  NOCACHE
  ORDER;
 
CREATE OR REPLACE TRIGGER T_DEPARTMENT_RBI
  BEFORE INSERT ON DEPARTMENT
  FOR EACH ROW
DECLARE
  CREATE_USER_ID$x VARCHAR2(20) := substr(USER,1,20);
  CREATE_TIMSTM$x   TIMESTAMP := SYSTIMESTAMP;
BEGIN
  :new.CREATE_USER_ID := CREATE_USER_ID$x;
  :new.CREATE_TIMSTM := CREATE_TIMSTM$x;
  :new.LAST_UPDTD_USER_ID := CREATE_USER_ID$x;
  :new.LAST_UPDTD_TIMSTM := CREATE_TIMSTM$x;
END T_DEPARTMENT_RBI;
/

CREATE OR REPLACE TRIGGER T_DEPARTMENT_RBU
  BEFORE UPDATE ON DEPARTMENT
  FOR EACH ROW
DECLARE
  LAST_UPDTD_TIMSTM$x   TIMESTAMP := SYSTIMESTAMP;
  LAST_UPDTD_USER_ID$x  VARCHAR2(20) := substr(USER,1,20);
BEGIN
  :new.CREATE_USER_ID := :old.CREATE_USER_ID;
  :new.CREATE_TIMSTM := :old.CREATE_TIMSTM;
  :new.LAST_UPDTD_USER_ID := LAST_UPDTD_USER_ID$x;
  :new.LAST_UPDTD_TIMSTM := LAST_UPDTD_TIMSTM$x;
END T_DEPARTMENT_RBU;
/