NOTE

# MYSQL #

CREATE TABLE ADM_SIGBJD (
 SIG_BJD_CD       CHAR(10)        PRIMARY KEY

-- Create & Update --
,CREATED_AT    TIMESTAMP    NOT NULL    DEFAULT CURRENT_TIMESTAMP
,UPDATED_AT    TIMESTAMP    NOT NULL    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
,INDEX (CREATED_AT)
,INDEX (UPDATED_AT)
);

• Postgres에서는 Mysql의 ON UPDATE CURRENT_TIMESTAMP가 안된다.
• 함수와 트리거를 사용해야 한다.

HOW TO

함수 작성

CREATE OR REPLACE FUNCTION UPDATE_MODIFIED_COLUMN()
RETURNS TRIGGER AS $$
BEGIN
    NEW.modified = now();
    RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

테이블 생성, 트리거 연결

CREATE TABLE MY_TABLE_NAME (
-- ... columns

-- Create & Update --
,CREATED_AT    TIMESTAMP    NOT NULL    DEFAULT CURRENT_TIMESTAMP
,UPDATED_AT    TIMESTAMP    NOT NULL    DEFAULT CURRENT_TIMESTAMP
,INDEX (CREATED_AT)
,INDEX (UPDATED_AT)
);

CREATE TRIGGER **{대상 컬럼명}** BEFORE UPDATE ON **{테이블명}** FOR EACH ROW EXECUTE PROCEDURE UPDATE_MODIFIED_COLUMN();