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;
/
