SQLite Triggers 사용하기

MMEX 1.6.3

거래 시점 날짜가 기록되는 TRANSDATE 필드가 문자열이다. 🤬
날짜 형식은 yyyy-MM-dd인데 시간도 기록하고 싶어서 트리거 기능을 사용하였다.
그 옆의 필드도 datetime 형식이 아니라 문자열이다. 내용은 datetime 양식이지만...

일일이 코딩하는게 귀찮아서 DB한테 떠넘길 때 사용한다.
명령어를 모르면 SQLite 편집기를 사용한다.
(DB 편집기에서 레코드를 등록해도 트리거가 동작한다)

 

SQLite Maestro에서 트리거 생성

테이블 또는 Triggers 메뉴에서 Create New Trigger...를 선택한다.

기준 테이블을 선택하고, 이름을 입력한다.

 

레코드를 삽입할 때는 어차피 트리거를 실행해줘야 하니 조건은 없어도 되고,

레코드를 수정할 때는 수정할 때마다 트리거를 당겨도 되지만, 특정 필드가 갱신됐을 때만 트리거를 실행하는 것이 더 효율적이다.

여기다 SQL 명령을 추가하면 된다.

바로 등록했다 치고, 등록된 트리거 항목을 더블클릭하면 편집 화면이 나타난다.

---

SQL문으로 트리거 생성

CREATE TRIGGER IF NOT EXISTS CHECKINGACCOUNT_TriggerAfterUpdate
  AFTER UPDATE
  ON CHECKINGACCOUNT_V1
BEGIN

END;

---

INSERT 트리거일 때 레코드 변경

BEGIN
     insert OR REPLACE into CHECKINGACCOUNT_JOIN (
            TRANSID,
            TRANSDATETIME
     )
     values (
            new.TRANSID,
            datetime(new.TRANSDATE)
     );
     
     update CHECKINGACCOUNT_V1
     set
        TRANSDATE = date(new.TRANSDATE)
     where
          TRANSID = new.TRANSID;
END

프로그램 코드에서 SQL INSERT를 할 때, TRASDATE 필드의 값을 yyyy-MM-dd hh:mm:ss 형식으로 넘겨야 한다.
CHECK~~JOIN이라는 별도의 테이블을 만들었지만, 기존 테이블에 필드만 추가해도 된다.
OR REPLACE는 TRANSID를 중복 불가로 설정했기 때문에, 이미 있는 레코드라면 교체하라는 명령이다.

new.필드명은 새로 입력되는 레코드 정보이고, old.필드명은 기존 레코드 정보이다.

datetime() 함수는 yyyy-MM-dd hh:mm:ss 형식, date() 함수는 yyyy-MM-dd 형식으로 바뀐다.

두 번째 SQL문에서 트리거 기준 테이블을 UPDATE로 하는 이유는 After 트리거이기 때문이다. 이미 등록 된 후에 발생.

---

UPDATE 트리거일 때 레코드 변경

BEGIN
     update CHECKINGACCOUNT_JOIN
     set
        TRANSDATETIME = datetime(new.TRANSDATE)
     where
          TRANSID = old.TRANSID;
     
     update CHECKINGACCOUNT_V1
     set
        TRANSDATE = date(new.TRANSDATE)
     where
          TRANSID = old.TRANSID;
END

너도 나도 레코드 갱신

레코드를 입력해보면 동작 결과를 쿼리를 통해 바로 확인할 수 있다.

간단하게 CRUD 만 사용했지만, with 등 복잡한 SQL문도 사용 가능하다.

---

별도의 필드나 테이블을 만들지 않고, 데이터만 변경해서 처리하려면 INSTEAD OF INSERT 처럼 AFTER | BEFORE 대신 INSTEAD OF를 붙이면 된다. 그럼 원본 SQL문은 무시되고 새로 작성한 SQL문으로 처리된다.

CREATE TRIGGER IF NOT EXISTS CHECKINGACCOUNT_TriggerAfterUpdate
  INSTEAD OF UPDATE
  ON CHECKINGACCOUNT_V1
BEGIN
     update CHECKINGACCOUNT_V1
     set
        TRANSDATE = date(new.TRANSDATE)
     where
          TRANSID = new.TRANSID;
END