MySQL 5.0.xx 에서 트리거의 특징

 MySQL 5.0.xx 에서 트리거의 특징 
 
 
Chapter 18. 트리거

18.1. CREATE TRIGGER 신텍스

18.2. DROP TRIGGER 신텍스

18.3. 트리거 사용하기

 

트리거에 대한 지원은 MySQL 5.0.2버전에서부터 지원한다. 트리거란 이름이 있는 데이터 베이스 오브젝트(named database object )로서, 테이블과 연관되어 있으며, 특정 이벤트가 테이블에 대해 발생하면 동작을 하게 된다. 예를 들면, 아래의 명령문은 하나의 테이블과 하나의 INSERT 트리거를 생성한다. 트리거는 테이블의 컬럼에 삽입된 값들을 더하게 한다.:

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));

Query OK, 0 rows affected (0.03 sec)

 

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account

-> FOR EACH ROW SET @sum = @sum + NEW.amount;

Query OK, 0 rows affected (0.06 sec)

이 장에서는 트리거를 생성하고 없애는 신텍스를 설명할 것이며, 트리거를 어떻게 사용하는지에 대한 예문을 보여 주기로 한다. 트리거 사용의 제약 사항은 Section I.1, “스토어드 루틴과 트리거상의 제약 사항”을 참조하기 바란다. 바이너리 로깅이 트리거에 적용될 때의 제약 사항은 Section 17.4, “스토어드 루틴과 트리거의 바이너리 로깅”에서 다루었다.

 

18.1. CREATE TRIGGER 신텍스

CREATE

[DEFINER = { user | CURRENT_USER }]

TRIGGER trigger_name trigger_time trigger_event

ON tbl_name FOR EACH ROW trigger_stmt

 

이 명령문은 새로운 트리거를 생성한다. 트리거는 테이블과 연관된 이름이 있는 데이터 베이스 오브젝트이며, 특정 이벤트가 테이블에 대해 발생할 경우 동작을 하게 된다. CREATE TRIGGER 는 MySQL 5.0.2에 추가된 기능이다. 현재까지는, 이것을 사용하기 위해서는 SUPER 권한이 필요하다.

트리거는 tbl_name 로 명기된 테이블과 연결되어 있는데, 이것은 영구(Permanent)테이블을 참조하여야 한다. 트리거를 TEMPORARY테이블 또는 하나의 뷰(view)에 연결할 수는 없다.

트리거가 활성화되면, DEFINER 구문은 이 섹션의 후반부에 설명하는 적용 가능한 권한을 판단하게 된다.

trigger_time 는 트리거의 동작 시간이다. 이는 트리거를 명령문 실행 전에 활성화 시킬지 또는 후에 활성화 시킬지를 나타내는 BEFORE 또는 AFTER 가 될 수 있다.

trigger_event 는 트리거를 활성화 시키는 명령문의 종류를 나타낸다. trigger_event 는 아래의 것 중에 하나가 될 수 있다:

INSERT: 트리거는 새로운 줄(row)이 테이블 속으로 삽입될 때마다 활성화 된다; 예를 들면, INSERT, LOAD DATA, 및 REPLACE 명령문을 통해서.
UPDATE: 트리거는 하나의 줄이 수정될 때마다 활성화된다; 예를 들면, UPDATE 명령문을 통해.
DELETE: 트리거는 하나의 줄이 테이블에서 삭제될 때마다 활성화 된다; 예를 들면, DELETE 및 REPLACE 명령문을 통해.
trigger_event 는 트리거를 활성화 시킬 때 테이블 동작의 형태를 표현하는 것과 같이 SQL명령문의 문자 타입을 표현하지 않는다는 점을 이해하는 것이 중요하다. 예를 들면, INSERT 트리거는 INSERT명령문 뿐만 아니라 LOAD DATA명령문에 의해서도 활성화 되어지는데, 이것은 두 가지 명령문 모두 테이블에 줄을 삽입하기 때문이다.

INSERT INTO ... ON DUPLICATE KEY UPDATE ... 는 혼란스러움을 줄 수 있는 예문이다: BEFORE INSERT 트리거는 모든 중에 대해 활성화가 되는데, AFTER INSERT 또는 BEFORE UPDATE 와 AFTER UPDATE 가 모두 따라오게 되는데, 줄이 이중화 되어 있는지에 따라 쓸 수 있다.

동일한 트리거 동작 시간 및 이벤트를 갖는 테이블에 대해서는 두 개의 트리거를 가질 수 없다. 예를 들면, 한 테이블에 대해 두 개의 BEFORE UPDATE 트리거를 가질 수 없다. 하지만, 한 개의 BEFORE UPDATE 와 한 개의 BEFORE INSERT 트리거는 가질 수 있거나, 또는 한 개의 BEFORE UPDATE 와 한 개의 AFTER UPDATE 트리거는 가질 수 있다.

trigger_stmt 는 트리거가 활성화될 때 실행할 수 있는 명령문이다. 만일, 다중 명령문을 실행하고자 한다면, BEGIN ... END 복합 명령문 구성을 사용하면 된다. 이것은 스토어드 루틴내에서 사용 가능한 동일한 명령문에서도 적용할 수 있다 Section 17.2.5, “BEGIN ... END 복합 명령문 신텍스” 참조.

Note: 현재의 버전에서는, 연속적인 외국어 키 동작(cascaded foreign key actions )으로 트리거를 활성화 시킬 수는 없다. 이 제약 사항은 가능한 한 빠른 시간 내에 수정될 예정이다.

Note: MySQL 5.0.10 이전에는, 이름을 가지고 직접 테이블을 참조할 수는 없었다. MySQL 5.0.10이후에는, 이 예문에서 보듯이, testref 로 명기된 것과 같은 트리거를 사용할 수 있게 된다:

CREATE TABLE test1(a1 INT);

CREATE TABLE test2(a2 INT);

CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

CREATE TABLE test4(

a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

b4 INT DEFAULT 0

);

 

DELIMITER |

 

CREATE TRIGGER testref BEFORE INSERT ON test1

FOR EACH ROW BEGIN

INSERT INTO test2 SET a2 = NEW.a1;

DELETE FROM test3 WHERE a3 = NEW.a1;

UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;

END;

|

 

DELIMITER ;

 

INSERT INTO test3 (a3) VALUES

(NULL), (NULL), (NULL), (NULL), (NULL),

(NULL), (NULL), (NULL), (NULL), (NULL);

 

INSERT INTO test4 (a4) VALUES

(0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

아래의 값을 테이블 test1에 아래와 같이 삽입을 한다고 가정하자:

mysql> INSERT INTO test1 VALUES

-> (1), (3), (1), (7), (1), (8), (4), (4);

Query OK, 8 rows affected (0.01 sec)

Records: 8 Duplicates: 0 Warnings: 0

 

결과적으로는, 아래와 같이 네 개의 테이블에 데이터가 있게 된다:

mysql> SELECT * FROM test1;

+------+

| a1 |

+------+

| 1 |

| 3 |

| 1 |

| 7 |

| 1 |

| 8 |

| 4 |

| 4 |

+------+

8 rows in set (0.00 sec)

 

mysql> SELECT * FROM test2;

+------+

| a2 |

+------+

| 1 |

| 3 |

| 1 |

| 7 |

| 1 |

| 8 |

| 4 |

| 4 |

+------+

8 rows in set (0.00 sec)

 

mysql> SELECT * FROM test3;

+----+

| a3 |

+----+

18.3. 트리거 사용하기

트리거에 대한 지원은 MySQL 5.0.2에서부터 포함된다. 이 섹션에서는 트리거를 어떻게 사용하는지 그리고 트리거를 사용하는데 있어서 어떤 제약 사항이 있는지에 대해 설명하기로 한다. 트리거의 제약 사항에 대한 추가적이 내용은 Section I.1, “스토어드 루틴과 트리거상의 제약 사항”에서 제공하기로 한다.

트리거는 테이블과 연관된 이름이 있는 데이터 베이스 오브젝트이며, 테이블에 대해 특정 이벤트가 발생하면 활성화된다. 어떤 트리거는 테이블 안으로 삽입하고자 하는 값을 검사하는 기능을 수행하거나 또는 업데이트에서 호출한 값을 계산하는 기능을 수행한다.

트리거는 테이블과 관련이 있고, 테이블에 대한 INSERT, DELETE 또는 UPDATE명령문이 실행될 때에 활성화가 정의된다. 트리거는 이를 실행하는 명령문의 앞에서 또는 뒤에서 활성화 되도록 설정할 수 있다. 예를 들면, 트리거가 테이블에서 삭제되는 각 줄(row)전에 활성화 되도록 할 수 있거나 또는 각 줄이 업데이트된 후에 활성화 되도록 할 수도 있다.

트리거를 생성 또는 드롭 시키기 위해서는, CREATE TRIGGER 또는 DROP TRIGGER 명령문을 사용한다. 이러한 명령문에 대한 신텍스는 Section 18.1, “CREATE TRIGGER 신텍스”, 및 Section 18.2, “DROP TRIGGER 신텍스”에서 설명하였다.

INSERT명령문에 대한 테이블을 갖는 트리거와 관련된 간단한 예문을 보여주기로 한다. 이것은 테이블의 컬럼 중에 하나에 삽입된 값을 더하는 연산자의 역할을 한다.

아래의 명령문은 테이블을 생성하고 이에 해당하는 트리거를 만들게 된다:

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account

-> FOR EACH ROW SET @sum = @sum + NEW.amount;

CREATE TRIGGER 명령문은 ins_sum이라는 이름의 트리거를 생성하며 이것은 account라는 테이블과 연관된다. 이것은 또한 트리거의 동작 시간, 트리거하는 이벤트, 그리고 트리거를 활성화 하면서 해야 할 일들을 지정한다:

키워드 BEFORE는 트리거 실행 시간을 가리킨다. 이와 같은 경우, 트리거는 각 줄이 테이블에 삽입되기 전에 실행되어야 한다. 다른 사용 가능한 키워드는 AFTER이다.
키워드 INSERT는 트리거를 실행시키는 이벤트를 가리킨다. 예를 들면, INSERT 명령문은 트리거 실행을 일으킨다. 여러분은 또한 DELETE 와 UPDATE 명령문을 위한 트리거도 생성할 수가 있다.
FOR EACH ROW에 따라오는 명령문은 트리거 실행을 일으키는 명령문을 정의하는데, 트리거를 하는 명령문에 의해 영향을 받는 각 줄에 한번씩 발생하게 된다. 예문에서 보면, 트리거가 된 명령문은 amount 컬럼 안으로 삽입되는 값을 합하는 단순한 SET이 된다. 그 명령문은 “새로운 줄 속으로 삽입되어지는 amount 컬럼의 값”을 의미하는 NEW.amount 의 형태로 컬럼을 참조하고 있다.
트리거를 사용하기 위해서는, 누산기 변수를 0으로 설정하고, INSERT 명령문을 실행하고, 그 다음에는 변수의 값이 어떻게 나오는지 보자:

mysql> SET @sum = 0;

mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);

mysql> SELECT @sum AS 'Total amount inserted';

+-----------------------+

| Total amount inserted |

+-----------------------+

| 1852.48 |

+-----------------------+

이와 같은 경우, INSERT명령문이 실행된 다음에 나오는 @sum 의 값은 14.98 + 1937.50 - 100, or 1852.48이 된다.

트리거를 없애기 위해서는, DROP TRIGGER 명령문을 사용한다. 만일 트리거가 디폴트 스키마에 있지 않으면, 스키마의 이름을 정확히 지정해야 한다.:

mysql> DROP TRIGGER test.ins_sum;

트리거의 이름은 스키마 이름란에 존재해야 하고, 이것은 하나의 스키마에 있는 트리거들은 서로 다른 이름을 가져야 함을 의미한다. 서로 다른 스키마에 있는 트리거들은 같은 이름을 가져도 된다..

트리거가 하나의 스키마에 대해 중복되지 않는 이름을 가져야 한다는 조건에 이외에도, 트리거를 생성하는데 있어서 몇 가지 다른 제약 사항도 있다. 특히, 하나의 테이블에는 동일한 활성화 시간 및 이벤트를 갖는 두 개의 트리거를 가질 수 없다. 예를 들면, 하나의 테이블에 대해 두 개의 BEFORE INSERT 트리거 또는 두 개의 AFTER UPDATE 트리거를 가질 수 없다. 이것은 그다지 중요하지 않은 제약 사항인데, 그 이유는 FOR EACH ROW 다음에BEGIN ... END 복합 명령문 구성을 사용해서 다중 명령문을 실행하는 트리거를 정의할 수 있기 때문이다. (하나의 예문이 이 섹션 후반부에 있다.)

OLD 와 NEW 키워드를 사용해서 트리거에 의해 영향을 받는 줄에 있는 컬럼을 활성화 시킬 수 있다. (OLD 와 NEW 는 대소문자 구분을 하지 않음.) INSERT 트리거에는, NEW.col_name 만을 사용할 수 있다; 여기에는 이전 줄(old row)이 없다. DELETE 트리거에는, OLD.col_name 만을 사용할 수 있다; 여기에는 새로운 줄(new row)이 없다. UPDATE 트리거에는, 줄(row)이 업데이트되기 전에 그 줄에 있는 컬럼을 참조하도록 OLD.col_name 을 사용할 수 있으며, 줄이 업데이트된 후에 그 줄에 있는 컬럼을 참조하도록 NEW.col_name 을 사용할 수 있다.

OLD 로 표시된 컬럼은 읽기 전용(read-only)이다. 만일 SELECT권한이 있다면 이것을 참조할 수는 있으나, 수정할 수는 없다. NEW로 표시된 컬럼은, SELECT권한이 있다면, 참조될 수 있다. BEFORE 트리거에 있어서는, 이것에 대한 UPDATE권한이 있는 경우에는 SET NEW.col_name = value 을 가지고 그 값을 변경할 수 있다. 이것은 트리거를 사용해서 새로운 줄에 삽입될 값을 수정할 수 있거나 또는 줄을 업데이트할 수 있다는 것을 의미한다.

BEFORE 트리거에 있어서는, AUTO_INCREMENT 컬럼에 대한 NEW의 값은 0이 되며, 이 값은 실제로 새로운 기록이 삽입될 때 나오게 되는 자동 생성 시퀀스 숫자는 아니다.

OLD 와 NEW 는 트리거에 대한 MySQL의 확장 기능이다.

BEGIN ... END 명령문 구성을 사용해서, 다중 명령문을 실행하는 트리거를 정의할 수 있다. BEGIN 블록 안에서는, 조건문과 루프 같은 스토어드 루틴 안에서 사용 가능한 다른 신텍스를 사용할 수도 있다. 하지만, 스토어드 루틴에 대한 것과 마찬가지로, mysql 프로그램을 사용해서 다중 명령문을 실행하는 트리거를 정의하고자 한다면, 트리거 정의에서 ; 명령문 구획 문자를 사용하기 위해서는MYSQL명령문 구획 문자(delimiter)를 재 정의하는 것이 필요하다. 다음의 예문은 이런 점을 표현하는 것이다. 여기에서 UPDATE 트리거는 각 줄의 업데이트에 사용되는 새로운 값을 검사하도록 정의되며, 그리고 그 값이 0에서부터 100의 범위 내에 있도록 수정한다. 이것은 값이 줄을 업데이트 하기 전에 검사되어야 하기 때문에 BEFORE 트리거가 되어야 한다:

mysql> delimiter //

mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account

-> FOR EACH ROW

-> BEGIN

-> IF NEW.amount < 0 THEN

-> SET NEW.amount = 0;

-> ELSEIF NEW.amount > 100 THEN

-> SET NEW.amount = 100;

-> END IF;

-> END;//

mysql> delimiter ;

이렇게 하면 스토어드 프로시저를 개별적으로 정의하고 그 다음에 간단한 CALL명령문을 사용하는 트리거로부터 호출하는 것을 더 쉽게 할 수 있다. 이것은 또한 몇몇 트리거 내에서부터 동일한 루틴을 호출하고자 할 경우에 장점을 가지게 된다.

트리거가 활성화 될 때 실행하는 명령문에는 몇 가지 제약 사항이 존재한다:

트리거는 데이터를 클라이언트에 돌려 주는 스토어드 프로시저 또는 동적(dynamic)SQL을 사용하는 스토어드 프로시저를 호출하는 CALL명령문은 사용할 수 없다. (스토어드 프로시저가 OUT 또는 INOUT파라미터를 통해서 데이터를 트리거에 돌려 주는 것은 허용된다.)
트리거는 START TRANSACTION, COMMIT, 또는ROLLBACK과 같은 확정적(explicitly)으로 또는 암시적(implicitly)으로 트랜잭션을 시작 또는 종료하는 명령문은 사용할 수 없다.
MySQL 5.0.10 이전 버전에서는, 트리거는 이름을 가지고 테이블 직접 참조를 할 수 없다.
MySQL은 트리거 실행 동안에 발생하는 에러를 아래와 같이 처리한다:

BEFORE 트리거가 실패(fails)할 경우, 이에 대응하는 줄에 대한 동작은 수행되지 않는다.
AFTER 트리거는 BEFORE트리거(존재 한다면)와 줄에 대한 동작 모두 성공적으로 실행될 경우에만 실행된다.
BEFORE 또는 AFTER트리거의 실행 동안 발생되는 에러는 트리거 호출을 일으키는 전체 명령문의 실패를 일으킨다.
트랜잭션 테이블에 대해서는, 트리거의 실패(이에 따른 전체 명령문 실패)는 명령문에 의해 진행된 모든 데이터 변경의 롤백을 일으키게 된다. 비 트랜잭션 테이블(non-transactional tables)에 대해서는, 이러한 롤백은 일어날 수 없으며, 따라서 비록 명령문이 실패한다 하더라도, 에러가 발생한 시점 이전에 진행된 어떠한 데이터 변경도 그대로 유지된다.