뷰(View)와 트리거(Trigger)

1. 뷰(View)

뷰(View)는 select문을 저장한 객체라고 할 수 있다. 데이터베이스 존재하는 일종의 가상 테이블을 의미하며 실제 테이블처럼 행과 열을 가지고 있지만, 실제로 데이터를 저장하고 있지는 않다. 본래 데이터베이스 객체로 등록할 수 없는 SELECT 명령을 객체로서 이름을 붙여 관리할 수 있도록 한 것이 뷰이다. 따라서 뷰를 참조하면 그에 정의된 SELECT명령의 실행결과를 테이블처럼 사용할 수 있다.

 

나중에 사용자가 뷰를 사용하게 되면 마치 뷰가 기본 테이블인 것 같이 만들어 제공한다. 따라서 뷰는 실행 시간에만 구체화되는 특수한 테이블이다.

---

1) 뷰의 생성

create view 뷰이름[원하는 속성] as select문 [with check option]; // []는 있어도 되고 없어도 되고

as select문 : 원하는 select문을 작성하며 union이나 order by는 사용할 수 없다.

with check option : 갱신이나 삽입 연산이 실행될 때 뷰의 정의 조건을 위배하면 실행을 거부한다.

mysql> create view emp_view as select first_name, last_name, gender from employees limit 10;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from emp_view;
+------------+-----------+--------+
| first_name | last_name | gender |
+------------+-----------+--------+
| Bezalel    | Simmel    | F      |
| Parto      | Bamford   | M      |
| Chirstian  | Koblick   | M      |
| Kyoichi    | Maliniak  | M      |
| Anneke     | Preusig   | F      |
| Tzvetan    | Zielinski | F      |
| Saniya     | Kalloufi  | M      |
| Sumant     | Peac      | F      |
| Duangkaew  | Piveteau  | F      |
| Mary       | Sluis     | F      |
+------------+-----------+--------+
10 rows in set (0.00 sec)

 

2) 뷰의 삭제

drop view 뷰이름 [restrict or cascade];

restrict : 뷰를 다른 곳에서 참조하고 있으면 제거가 취소된다.

cascade : 뷰를 참조하는 다른 뷰나 제약 조건까지 모두 제거한다.

mysql> drop view emp_view;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from emp_view;
ERROR 1146 (42S02): Table 'employees.emp_view' doesn't exist

 

[출처] https://kosaf04pyh.tistory.com/302

[데이터베이스] 뷰(View)

 

2. 트리거(Trigger)

- 테이블에 대한 특정 이벤트에 반응해 자동 실행되는 작업

 이벤트 : INSERT, UPDATE, DELETE

 

- 트리거 실행 시점을 이벤트 전(BEFORE)이나 이벤트 후(AFTER)로 지정하여 설정 = 활성화 시간

 INSERT 전/후, UPDATE 전/후, DELETE 전/후

 

- MySQL은 5.0.2버전부터 표준 트리거가 지원됨

 

- 데이터 상태의 관리를 자동화하는 데 사용

 데이터 작업 제한, 작업 기록, 변경 작업 감사 등

 

 

• 제약사항

- 하나의 스키마에서 트리거 이름은 중복불가

- 하나의 테이블에는 동일한 활성화 시간 및 이벤트를 갖는 두 개의 트리거를 가질 수 없음

  ex) INSERT 이벤트 전(BEFORE) 트리거 두 개 생성 불가능

 

- 저장 프로시저, 저장 함수, 트리거 정의할 때

SQL이 한줄이면 BEGIN, END를 생략할 수 있다. (DELIMITER 생략 가능)

 

1) 트리거 종류 및 키워드

• 트리거 종류

- 행 트리거

 테이블 안의 영향을 받은 레코드(rows) 각각에 대해 실행

 변경 전 또는 변경 후의 레코드(rows)는 OLD, NEW라는 가상 줄 변수를 이용해 사용 가능

 

- 문장 트리거

 INSERT, UPDATE, DELETE 문에 대해 한번만 실행된다.

 

• OLD / NEW 키워드

- 트리거에 대한 MySQL의 확장 기능

- 트리거 본문 내에서 OLD.컬럼명 및 NEW.컬럼명 을 사용하면 트리거의 영향을 받는 행의 열에 액세스 가능

 

- OLD : 이벤트가 일어나기 이전 데이터 , NEW : 이벤트가 일어난 이후 데이터

 

- 대소 문자를 구분하지 않음 - 이벤트에 따라 사용할 수 있는 키워드가 정해져 있음

 INSERT : NEW(after)

> 추가하기전의 OLD 데이터가 없기 때문에 NEW만 쓸수있다.

 

 UPDATE : OLD(before) , NEW(after)

> 이전의 데이터와 이후의 데이터가 있다.

 

 DELETE : OLD(before)

 

2) 트리거 생성 방법

 

mysql> DELIMITER //
mysql> CREATE TRIGGER <트리거 이름> <활성화시간> <이벤트> ON <테이블명>
		FOR EACH ROW
		BEGIN
		실행문 ;
		....
END //

mysql> DELIMITER ;

 

- 활성화 시간

 트리거 실행 시점

 BEFORE : 이벤트 발생 전에 실행

 AFTER : 이벤트 발생 후에 실행 - 이벤트

 트리거가 실행 될 이벤트 ( INSERT / UPDATE / DELETE )

 

- FOR EACH ROW

 이벤트가 발생하면 레코드(rows) 당 한번씩 실행문 수행 = 행트리거

 

3) 실습: 트리거 생성

delimiter //

mysql> create trigger trig_test after update on member
    -> for each row
    -> begin
    -> update trig_test set count=count+1 where event_time='UPDATE_AFTER';
    -> end //

delimiter ;

 

업데이트가 발생하면 업데이트 UPDATE_AFTER 컬럼에 명령어로 카운트 값을 증가시키는 트리거

 

4) 실습: 트리거 변경(Update)

- 성별을 '남'으로 바꾸시오.

sql> update member set sex='남' where sex='M';
Query OK, 10 rows affected (0.03 sec)
Rows matched: 10  Changed: 10  Warnings: 0
> 10개의 레코드의 성별이 바뀌었다.

mysql> select * from member;
+----+---------+-----------+------+----------+---------------------------------------------+----------+------+
| no | id      | name      | sex  | post_num | address                                     | tel      | age  |
+----+---------+-----------+------+----------+---------------------------------------------+----------+------+
|  1 | yjhwang | 황영주    | 남   | 100-011  | 서울시 중구 충무로1가                       | 234-8879 |   35 |
|  2 | khshul  | 설기형    | 남   | 607-010  | 부산시 동래구 명륜동                        | 764-3784 |   33 |
|  3 | chpark  | 박철호    | 남   | 503-200  | 광주시 남구 지석동                          | 298-9730 |   34 |
|  4 | shlee   | 이상훈    | 남   | 503-200  | 광주시 남구 도금동                          | 838-4347 |   32 |
|  5 | jyjang  | 장영숙    | W    | 503-201  | 부산시 영도구 봉래동5가                     | 399-9809 |   24 |
|  6 | yjbae   | 배용진    | 남   | 606-065  | 서울시 은평구 응암4동                       | 857-5683 |   30 |
|  7 | hbpark  | 박혜빈    | W    | 122-014  | 경기도 과천시 중앙동                        | 234-7677 |   22 |
|  8 | mskim   | 김문수    | 남   | 427-760  | 경기도 시흥시 신천동                        | 370-6003 |   63 |
|  9 | bkcha   | 차범길    | 남   | 429-020  | 대전시 서구 둔산1동                         | 432-9877 |   49 |
| 10 | kskim   | 김길수    | 남   | 302-121  | 경기도 수원시 장안구 파장동                 | 324-5875 |   54 |
| 11 | srkim   | 김수련    | 남   | 440-747  | 대구시 달서구 신당동                        | 987-3688 |   23 |
| 12 | srlee   | 이성현    | 남   | 704-701  | 경기도 수원시 권선구 매산로1가              | 243-6844 |   36 |
| 13 | hnjang  | 정한나    | W    | 441-081  | 광주시 서구 화정4동                         | 845-4547 |   58 |
| 14 | mylee   | 이명연    | W    | 502-791  | 광주시 서구 쌍촌동                          | 837-9432 |   33 |
| 15 | yskim   | 김영숙    | W    | 429-010  | 경기도 시흥시 대야동                        | 374-8438 |   53 |
+----+---------+-----------+------+----------+---------------------------------------------+----------+------+

mysql> select * from trig_test;
+---------------+-------+
| event_time    | count |
+---------------+-------+
| INSERT_BEFORE |     0 |
| INSERT_AFTER  |     0 |
| UPDATE_BEFORE |     0 |
| UPDATE_AFTER  |    10 |
| DELETE_BEFORE |     0 |
| DELETE_AFTER  |     0 |
+---------------+-------+
// UPDATE_AFTER가 10으로 바뀌었다.

-> 트리거가 작동되어 trig_test 테이블에 count값이 증가하였다.

 

[출처] https://girrr.tistory.com/126

데이터베이스 - 트리거(Trigger)