20.3.1 트리거 문법 및 예제

트리거를 만들거나 트리거를 삭제할은 섹션 13.1.19 "CREATE TRIGGER 구문" 및 섹션 13.1.30 "DROP TRIGGER 신텍스" 에서 설명하고있는 바와 같이, CREATE TRIGGER 또는 DROP TRIGGER 문을 사용 합니다.

다음은 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)

CREATE TRIGGER 문은 account 테이블에 연결된 ins_sum 라는 트리거를 만듭니다. 트리거 동작 시간, 트리거 이벤트 및 트리거가 활성화 될 때 수행 할 작업을 지정하는 조항도 포함되어 있습니다.

트리거를 사용하려면 가산기 변수를 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 또는 1852.48 입니다.

트리거를 삭제하려면 DROP TRIGGER 문을 사용합니다. 트리거가 디폴트 스키마에없는 경우, 스키마 이름을 지정해야합니다.

mysql> DROP TRIGGER test.ins_sum;

테이블을 삭제하면 테이블의 트리거도 삭제됩니다.

트리거 이름은 스키마의 네임 스페이스에 존재합니다. 즉, 모든 트리거가 스키마에서 고유 한 이름을 가져야합니다. 다른 스키마의 트리거는 같은 이름을 가질 수 있습니다.

트리거 이름은 스키마에 대해 고유한다는 요구 사항 이외에 만들 수있는 트리거 유형에 대해 다른 제한이 있습니다. 특히 소정의 테이블에 동일한 트리거 이벤트와 액션 타임을 가진 여러 트리거를 포함 할 수 없습니다. 예를 들어, 하나의 테이블에 2 개의 BEFORE UPDATE 트리거를 정의 할 수 없습니다. 이에 대처하기 위해 FOR EACH ROW 의 후에 BEGIN ... END 복합 문 구조 구문을 사용하여 여러 개의 명령문을 실행하는 트리거를 정의 할 수 있습니다. (예는이 섹션에서 나중에 설명합니다.)

트리거 본체에서 OLD 와 NEW 키워드를 사용하면 트리거의 영향을받는 행의 컬럼에 액세스 할 수 있습니다. OLD 와 NEW 는 트리거에 대한 MySQL의 확장입니다. 이들은 대소 문자를 구별하지 않습니다.

INSERT 트리거 내에서 NEW. col_name 만 사용할 수 있습니다. 오래된 행은 없습니다. DELETE 트리거는 OLD. col_name 만 사용할 수 있습니다. 새로운 라인은 없습니다. UPDATE 트리거는 OLD. col_name 을 사용하여 갱신되기 전에 행의 열을 참조 할 수 있으며 NEW. col_name 을 사용하여 업데이트 된 후에 행의 열을 참조 할 수 있습니다.

OLD 로 지명 된 열은 읽기 전용입니다. (그에 대한 SELECT 권한이있는 경우) 참조 할 수 있지만 수정할 수는 없습니다. NEW 으로 지명 된 컬럼은 그에 대한 SELECT 권한이있는 경우에 볼 수 있습니다. BEFORE 트리거는 그에 대한 UPDATE 권한이있는 경우, SET NEW. col_name = value 로 값을 변경할 수 있습니다. 이것은 트리거를 사용하여 새 행에 삽입 할 값 또는 행의 갱신에 사용되는 값을 변경할 수 있음을 의미합니다. (이러한 SET 문은 행의 변경은 이미 이루어지고 있기 때문에, AFTER 트리거는 효과가 없습니다.)

BEFORE 트리거는 AUTO_INCREMENT 컬럼의 NEW 값은 0이며, 새로운 행이 실제로 삽입 될 때 자동으로 생성되는 시퀀스 번호가 없습니다.

BEGIN ... END 구조 구문을 사용하여 여러 명령문을 실행하는 트리거를 정의 할 수 있습니다. BEGIN 블록 내에서는 조건문과 루프 같은 스토어드 루틴 내에서 허용 된 다른 구문을 사용할 수 있습니다. 그러나 스토어드 루틴의 경우와 마찬가지로, mysql 프로그램을 사용하여 여러 명령문을 실행하는 트리거를 정의 할 때 트리거 정의에서 ; 명령문 구분 문자를 사용할 수 있도록, mysql 문 구분자를 재정의 해야합니다. 다음의 예는 이러한 요점을 보여줍니다. 여기에서 각 행의 업데이트에 사용할 새 값을 확인하고 0에서 100의 범위에 맞게 값을 변경하는 UPDATE 트리거를 정의하고 있습니다. 행의 갱신에 사용되기 전에 값을 체크 할 필요가 있기 때문에 이것은 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 문을 사용하여 트리거에서 호출하는 편이 쉬울 때도 있습니다. 이것은 여러 트리거 내에서 동일한 코드를 실행하는 경우에도 유용합니다.

활성화 할 때 트리거가 실행 문에 볼 수 대상에는 제한이 있습니다.

섹션 D.1 "저장 프로그램 제한 사항" 을 참조하십시오.

MySQL은 다음과 같이 트리거 실행 중에 오류를 처리합니다.

다음 예제와 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

따라서 4 개의 테이블에 다음 데이터가 포함됩니다.

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 |
+----+
|  2 |
|  5 |
|  6 |
|  9 |
| 10 |
+----+
5 rows in set (0.00 sec)

mysql> SELECT * FROM test4;
+----+------+
| a4 | b4   |
+----+------+
|  1 |    3 |
|  2 |    0 |
|  3 |    1 |
|  4 |    2 |
|  5 |    0 |
|  6 |    0 |
|  7 |    1 |
|  8 |    1 |
|  9 |    0 |
| 10 |    0 |
+----+------+
10 rows in set (0.00 sec)