23장. Trigger CREATE TRIGGER 구문 DROP TRIGGER 구문 트리거 사용하기

Presentation on theme: "23장. Trigger CREATE TRIGGER 구문 DROP TRIGGER 구문 트리거 사용하기"— Presentation transcript:

1 23장. Trigger CREATE TRIGGER 구문 DROP TRIGGER 구문 트리거 사용하기
쉽게 배우는 MySQL 5.x

2 트리거란? 이름이 있는 데이터베이스 오브젝트(named database object)로서 데이터베이스가 미리 정해 놓은 조건을 만족하거나 어떤 동작이 수행되면 자동적으로 수행되는 저장 프로시저이다. 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 + NEW.amount; Query OK, 0 rows affected (0.06 sec) 하나의 테이블과 하나의 INSERT 트리거를 생성하고 생성된 트리거는 테이블의 칼럼에 삽입된 값들을 더하게 한다. 23.1 CREATE TRIGGER 구문 -CREATE TRIGGER 는 MySQL 5.0.2에 추가된 기능 -트리거를 사용하기 위해서는 SUPER 권한이 필요 -tbl_name으로 명기된 테이블과 연결되고 영구(Permanent)테이블을 참조 -트리거를 TEMPORARY테이블 또는 하나의 뷰(view)에 연결할 수는 없다. 쉽게 배우는 MySQL 5.x

3 • UPDATE : 트리거는 하나의 행이 수정될 때마다 활성화된다. 예를 들면, UPDATE 명령문을 사용
CREATE [DEFINER = { user | CURRENT_USER }]     TRIGGER trigger_name trigger_time trigger_event     ON tbl_name FOR EACH ROW trigger_stmt 트리거 구문 형식 설명 • trigger_time : 트리거 동작시간으로 트리거를 명령문 실행 전에 활성화 시킬지 또는 실행 후에 활성화 시킬지를 나타내는 BEFORE 또는 AFTER 가 될 수 있다. • trigger_event : 트리거를 활성화시키는 명령문의 종류를 나타낸다. 예를 들면, INSERT 트리거는 INSERT 명령문 뿐만 아니라 LOAD DATA명령문에 의해서도 활성화 된다. 이것은 두 가지 명령문이 모두 테이블에 행(row)을 삽입하기 때문이다. trigger_event 는 아래의 명령문중 하나가 될 수 있다. • INSERT : 트리거는 새로운 행(row)이 테이블 속으로 삽입될 때마다 활성화 된다. 예를 들면, INSERT, LOAD DATA, 및 REPLACE 명령문을 사용 • UPDATE : 트리거는 하나의 행이 수정될 때마다 활성화된다. 예를 들면, UPDATE 명령문을 사용 • DELETE : 트리거는 하나의 행이 테이블에서 삭제될 때마다 활성화 된다. 예를 들면, DELETE 및 REPLACE 명령문을 사용 쉽게 배우는 MySQL 5.x

4 • INSERT INTO ... ON DUPLICATE KEY UPDATE ... : 혼란스러움을 줄 수 있다.
BEFORE INSERT 트리거는 모든 행(row)에 대해 활성화가 되는데, AFTER INSERT 또는 BEFORE UPDATE 와 AFTER UPDATE 가 모두 따라오게 되는데, 행이 이중화 되어 있는지에 따라 쓸 수 있다. • 동일한 트리거 동작 시간 및 이벤트를 갖는 테이블에 대해서는 두 개의 트리거를 가질 수 없다. ① 한 테이블에 대해 두 개의 BEFORE UPDATE 트리거를 가질 수 없다. ② 한개의 BEFORE UPDATE, 한개의 BEFORE INSERT 트리거는 가질 수 있다. ③ 한개의 BEFORE UPDATE, 한개의 AFTER UPDATE 트리거는 가질 수 있다. • trigger_stmt : 트리거가 활성화될 때 실행할 수 있는 명령문 ① 다중 명령문을 실행하고자 한다면, BEGIN ... END 복합 명령문 구성하여 사용 ② 스토어드 루틴내에서 사용 가능한 동일한 명령문에서도 적용 • 주의사항 ① 현재의 버전에서는, 연속적인 외부 키 동작(cascaded foreign key actions)으로 트리거를 활성화 시킬 수는 없다. ② MySQL 이전에는, 이름을 가지고 직접 테이블을 참조할 수는 없었다. 쉽게 배우는 MySQL 5.x

5 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); 쉽게 배우는 MySQL 5.x

6 테이블 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 SELECT * FROM test1; | a1   | |    1 | |    3 | |    7 | |    8 | |    4 | 8 rows in set (0.00 sec) SELECT * FROM test2; | a2   | |    1 | |    3 | |    7 | |    8 | |    4 | SELECT * FROM test3; +----+ | a3 | |  2 | |  5 | |  6 | |  9 | | 10 | 5 rows in set (0.00 sec) 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) Test3의 경우 test1의 열 값(1,3,4,7,8)을 삭제한 값만 출력 Test4의 경우 test1의 열 값(1,3,4,7,8)과 값이 같은 경우 b4에 1씩 가산해 준다. 예 : 1의 경우 3번 나오므로 3이 출력 쉽게 배우는 MySQL 5.x

7 23.2 DROP TRIGGER 구문 23.3 TRIGGER 사용하기
[형식] DROP TRIGGER [schema_name.] trigger_name -트리거를 드롭(drop) 시킨다. -만일 스키마가 생략되면, 트리거는 디폴트 스키마로부터 드롭된다. -DROP TRIGGER은 MySQL 5.0.2에서 추가되었다. -DROP TRIGGER를 사용하기 위해서는 SUPER 권한이 요구된다. 23.3 TRIGGER 사용하기 -데이터베이스 오브젝트이고, 테이블과 연관된 이름으로, 테이블에 대해 특정 이벤트가 발생하면 활성화된다. -테이블 안으로 삽입하고자 하는 값을 검사하는 기능을 수행 -테이블 업데이트에서 호출한 값을 계산하는 기능을 수행 -테이블에 대한 INSERT, DELETE 또는 UPDATE 명령문이 실행될 때에 활성화 -트리거는 실행하는 명령문의 앞 또는 뒤에서 활성화 되도록 설정할 수 있다. 쉽게 배우는 MySQL 5.x

8 테이블의 칼럼 중 한개에 삽입된 값을 더하는 연산자의 역할을 한다.
mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2)); mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account     -> FOR EACH ROW + NEW.amount; -ins_sum 트리거를 생성하고, account라는 테이블과 연관성이 있다. -트리거의 동작 시간, 트리거가 행하는 이벤트, 그리고 트리거를 활성화 하면서 해야 할 일을 지정 -키워드 BEFORE는 트리거 실행 시간, 각 줄이 테이블에 삽입되기 전에 실행 -키워드 INSERT는 트리거를 실행시키는 이벤트를 가리킨다. -FOR EACH ROW에 따라오는 명령문은 트리거를 활성화를 위한 명령문 정의 -트리거를 사용하기 위해서는, 누산기 변수를 0으로 설정 mysql> = 0; mysql> INSERT INTO account VALUES(137,14.98),(141, ),(97, ); mysql> AS '삽입된 amount의 총합'; | 삽입된 amount의 총합 | |               | 1 row in set (0.00 sec) 쉽게 배우는 MySQL 5.x

9 -트리거의 이름은 스키마(데이터베이스) 이름란에 존재 -하나의 스키마에 있는 트리거들은 서로 다른 이름을 가져야 함을 의미
-서로 다른 스키마에 있는 트리거들은 같은 이름을 가져도 된다. -하나의 테이블에는 동일한 활성화 시간 및 이벤트를 갖는 두 개의 트리거를 가질 수 없다. -하나의 테이블에 대해 두 개의 BEFORE INSERT 트리거 또는 두 개의 AFTER UPDATE 트리거를 가질 수 없다. -OLD와 NEW 키워드를 사용해서 트리거에 의해 영향을 받는 행에 있는 칼럼을 활성화 시킬 수 있다. -INSERT 트리거는 NEW.col_name만 사용, 이전에 삽입된 행(old row)은 없다. -DELETE 트리거는 OLD.col_name만사용, 새로운 행(new row)은 없다. -UPDATE 트리거는 행이 업데이트되기 전에 그 행에 있는 칼럼을 참조하도록 OLD.col_name을 사용하고,  행이 업데이트된 후에 그 행에 있는 칼럼을 참조하도록 NEW.col_name을 사용할 수 있다. -OLD로 표시된 칼럼은 읽기 전용이므로, SELECT권한이 있다면 참조 가능하나 수정은 불가능 -NEW로 표시된 칼럼은 SELECT권한이 있다면 참조 가능하고,  UPDATE권한이 있는 경우에는 SET NEW.col_name = value 을 가지고 그 값을 변경할 수 있다. -BEFORE 트리거에 있어서 AUTO_INCREMENT 칼럼에 대한 NEW의 값은 0이 되며, 이 값은 실제로 새로운 데이터가 삽입될 때 나오게 되는 자동 생성 시퀀스 숫자는 아니다. 쉽게 배우는 MySQL 5.x

10 -BEGIN 블록 안에서는 조건문과 루프와 같은 스토어드 루틴 안에서 사용 가능한 다른 신텍스를 사용할 수도 있다.
-OLD와 NEW는 트리거에 대한 MySQL의 확장 기능이다. -BEGIN ... END 명령문 구성을 사용해서 다중 명령문을 실행하는 트리거를 정의할 수 있다. -BEGIN 블록 안에서는 조건문과 루프와 같은 스토어드 루틴 안에서 사용 가능한 다른 신텍스를 사용할 수도 있다. -트리거 정의에서  명령문 구획 문자를 사용하기 위해서는 MySQL명령문 구획 문자(delimiter)를 재 정의하는 것이 필요하다. 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 ; UPDATE 트리거는 각 행의 업데이트에 사용되는 새로운 값을 검사하도록 정의되며, 그 값이 0에서부터 100의 범위 내에 있도록 수정한다. BEFORE 트리거를 사용하는 이유는 값이 행을 업데이트하기 전에 검사되어야 하기 때문이다. 쉽게 배우는 MySQL 5.x

11 트리거가 활성화 될 때 실행하는 명령문에는 몇 가지 제약 사항
-트리거는 데이터를 클라이언트에 돌려주는 스토어드 프로시저 또는 동적SQL을 사용하는 스토어드 프로시저를 호출하는 CALL명령문은 사용할 수 없다. -스토어드 프로시저가 OUT 또는 INOUT파라미터를 통해서 데이터를 트리거에 돌려주는 것은 허용된다. -트리거는 START TRANSACTION, COMMIT, 또는 ROLLBACK과 같은  확정적으로 트랜잭션을 시작 또는 종료하는 명령문은 사용할 수 없다. -MySQL 이전 버전에서는, 트리거는 이름을 가지고 테이블 직접 참조를 할 수 없었다. -MySQL은 트리거 실행 동안에 발생하는 에러를 아래와 같이 처리한다. -BEFORE 트리거가 실패할 경우, 대응하는 행에 대한 동작은 수행되지 않는다. -AFTER 트리거는 BEFORE 트리거가 존재하는 경우와 행에 대한 동작 모두 성공적으로 실행될 경우에만 실행된다. -BEFORE 또는 AFTER 트리거의 실행 동안 발생되는 에러는 트리거 호출을 일으키는 전체 명령문의 실패를 일으킨다. -트리거의 실패는 명령문에 의해 진행된 변경된 모든 데이터는 롤백된다. -비 트랜잭션 테이블(non-transactional tables)에 대해서는, 이러한 롤백은 일어날 수 없으며, 따라서 비록 명령문이 실패한다 하더라도, 에러가 발생한 시점 이전에 진행된 어떠한 데이터 변경도 그대로 유지된다. 쉽게 배우는 MySQL 5.x