ORACLE TRIGGER DDL - mieyanman @ ウィキ

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