SET LINESIZE 200
SET PAGESIZE 0
SET LONG 2000
SET HEADING OFF
SET TRIMSPOOL OFF
SET FEEDBACK OFF
SET LONGCHUNKSIZE 600

SPOOL C:\Temp\TRIGGER_TEXT.txt

CREATE TABLE TEST01
  (数値 NUMBER(6,1)
  ,新規登録日時 DATE
  ,新規登録ユーザ VARCHAR2(20)
  ,最終更新日時 DATE
  ,最終更新ユーザ VARCHAR2(20)
  )
;

CREATE OR REPLACE TRIGGER TEST01_TRIG
BEFORE INSERT OR UPDATE
ON TEST01
FOR EACH ROW
BEGIN
  IF INSERTING THEN
    :NEW.新規登録日時 := SYSDATE;
    :NEW.新規登録ユーザ := USER;
    :NEW.最終更新日時 := SYSDATE;
    :NEW.最終更新ユーザ := USER;
  ELSE
    :NEW.最終更新日時 := SYSDATE;
    :NEW.最終更新ユーザ := USER;
  END IF;
END;
/

SELECT DBMS_METADATA.GET_DDL('TABLE','TEST01','X') AS DDL FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('TRIGGER','TEST01_TRIG','X') AS DDL FROM DUAL;

INSERT INTO TEST01(数値) VALUES (1);
INSERT INTO TEST01(数値) VALUES (2);

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS';

SELECT * FROM TEST01;

UPDATE TEST01
  SET 数値 = 1
  WHERE 数値 = 1
;

SELECT * FROM TEST01;

UPDATE TEST01
  SET 最終更新日時 = TO_DATE('2999/12/31','YYYY/MM/DD')
  WHERE 数値 = 2
;

SELECT * FROM TEST01;

DROP TABLE TEST01;

SPOOL OFF