190909 mysql trigger

mysql> create table testtbl( id int, txt varchar(10));
mysql> insert into testtbl values (1,'exid');
mysql> insert into testtbl values(2,'gfriend');
mysql> insert into testtbl values(3,'ioi');
mysql> delimiter $$
mysql> create trigger testtg --- 트리거 생성
    -> after delete -- 삭제가 되면 발생
    -> on testtbl -- testtbl 에 붙여주세요
    -> for each row -- 각 행마다
    -> begin
    -> set @msg = 'deleted'; -- 전역변수 msg에 deleted 넣어주세요
    -> end $$
mysql> delimiter ;                                                              
mysql> set @msg = ' ';
mysql> insert into testtbl values(4, 'wannaone'); -- 값 넣을때
mysql> select @msg; -- 발생 안
+------+
| @msg |
+------+
|      |
+------+

mysql> update testtbl set txt='apink' where id = 3; -- update시에도 발생 안함

mysql> select @msg;
+------+
| @msg |
+------+
|      |
+------+

mysql> delete from testtbl where id = 4; -- 지웠을때만 발생
mysql> select @msg;
+---------+
| @msg    |
+---------+
| deleted |
+---------+

mysql> delimiter $$
mysql> create trigger inttg
    -> after insert
    -> on testtbl
    -> for each row
    -> begin
    -> set @msg = 'in';
    -> end $$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> set @msg = ' ';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into testtbl values (6,'H.O.T.');
Query OK, 1 row affected (0.00 sec)

mysql> select @msg;
+------+
| @msg |
+------+
| in   |
+------+
1 row in set (0.00 sec)


mysql> create table backup_userTbl 
(userID char(8) not null primary key
    -> name varchar(10) not null,
    -> birthYear int not null,
    -> addr char(2) not null,
    -> mobile1 char(3),
    -> mobile2 char(8),
    -> height smallint,
    -> mDate date,
    -> modType char(2),
    -> modDate date,
    -> modUser varchar(256));
새로운 테이블은 insert,update 작업시에 변경할 새로운 데이터를 잠깐 저장해 놓는다 
즉 ,insert,update 트리거를 부착시켜 해당 명령이 수행되면 입력/변경 될 새 값이 새로운 테이블에 저장 된 후에 새로운 테이블의 값이 원 테이블에 입력, 변경 되는것이다 
a (입력,수정) -> a1 저장(new) -> a에 적용
그러므로 새로운 테이블을 조작하면 입력되는 새로운 값을 다른값으로 대체시킬수 있다<?
그리고 old 테이블은 delete와 update 작업수행을 하면 삭제변경 되기 전의 예전값이 저장된다. 
결론적으로 트리거의 작동시에 새로입력/변경되는 새로운값의 데이터를 참조하기 위해선 new 테이블을, 예전데이터를 
참조 하고 싶으면 old 테이블을 참조한다. 

트리거 생성
backUserTbl_UpdateTrg
update뒤에 발생
usertbl에 부착,
각 행별로 
insert into backup_userTbl values (OLD.userID, OLD.name, OLD.birthYear, OLD.addr, OLD.mobile1, OLD.mobil2, OLD.height, OLD.mDate,'modify',curdate(),current_user());

mysql> delimiter $$
mysql> create trigger backUserTbl_UpdtatTrg
    -> after update
    -> on userTbl
    -> for each row
    -> begin
    -> insert into backup_userTbl values(OLD.userID, OLD.name,OLD.birthYear,OLD.addr,OLD.mobile1, OLD.mobile2, OLD.height, OLD.mDate, 'modify',curdate(),current_user());
    -> end $$
mysql> delimiter ;
mysql> 


트리거 생성
backUsertbl_DeleteTrg
delete 뒤에 발생
userTbl에 부착
각 행별로
insert into backup_userTbl values(OLD.userID, OLD.name, OLD.birthYear, OLD.addr, OLD.mobile1, OLD.mobil2, OLD.height, OLD.mDate,'delete',curdate(),current_user())


create trigger backUserTbl_UpdtatTrg 
after update on userTbl 
for each row 
begin insert into backup_userTbl 
values(OLD.userID, 
OLD.name,
OLD.birthYear,
OLD.addr,
OLD.mobile1, 
OLD.mobile2, 
OLD.height, 
OLD.mDate, 
'modify',
curdate(),
current_user()); 
end$$

delimiter $$
mysql> create trigger backUserTbl_DeleteTrg 
after delete on userTbl 
for each row 
begin 
insert into backup_userTbl values
(OLD.userID, 
OLD.name,
OLD.birthYear,
OLD.addr,
OLD.mobile1, 
OLD.mobile2, 
OLD.height, 
OLD.mDate, 
'delete',
curdate(),
current_user()); 
end$$
mysql> delimiter ;

mysql> update userTbl set height=165 where userID='BBK';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from backup_userTbl;
+--------+-----------+-----------+--------+---------+---------+--------+------------+---------+------------+----------------+
| userID | name      | birthYear | addr   | mobile1 | mobile2 | height | mDate      | modType | modDate    | modUser        |
+--------+-----------+-----------+--------+---------+---------+--------+------------+---------+------------+----------------+
| BBK    | 바비킴    |      1973 | 서울   | 010     | 0000000 |    176 | 2013-05-05 | modify  | 2019-09-09 | root@localhost |
+--------+-----------+-----------+--------+---------+---------+--------+------------+---------+------------+----------------+


-- 여기서 잠깐.. backup_userTbl에 primarykey 를 설정해버리면 update를 같은건 두번다시 못한다. 프라이머리 키 설정을 해제하자. 


mysql> update userTbl set height=165 where userID='BBK';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> select  * from backup_userTbl;
+--------+-----------+-----------+--------+---------+---------+--------+------------+---------+------------+----------------+
| userID | name      | birthYear | addr   | mobile1 | mobile2 | height | mDate      | modType | modDate    | modUser        |
+--------+-----------+-----------+--------+---------+---------+--------+------------+---------+------------+----------------+
| BBK    | 바비킴    |      1973 | 서울   | 010     | 0000000 |    165 | 2013-05-05 | modify  | 2019-09-09 | root@localhost |
+--------+-----------+-----------+--------+---------+---------+--------+------------+---------+------------+----------------+
1 row in set (0.00 sec)

mysql> update userTbl set height=176 where userID='BBK';                       Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select  * from backup_userTbl;
+--------+-----------+-----------+--------+---------+---------+--------+------------+---------+------------+----------------+
| userID | name      | birthYear | addr   | mobile1 | mobile2 | height | mDate      | modType | modDate    | modUser        |
+--------+-----------+-----------+--------+---------+---------+--------+------------+---------+------------+----------------+
| BBK    | 바비킴    |      1973 | 서울   | 010     | 0000000 |    165 | 2013-05-05 | modify  | 2019-09-09 | root@localhost |
| BBK    | 바비킴    |      1973 | 서울   | 010     | 0000000 |    165 | 2013-05-05 | modify  | 2019-09-09 | root@localhost |
+--------+-----------+-----------+--------+---------+---------+--------+------------+---------+------------+----------------+
2 rows in set (0.00 sec)

mysql> delete from userTbl where userID='LSG';
Query OK, 1 row affected (0.00 sec)

mysql> select * from backup_userTbl;
+--------+-----------+-----------+--------+---------+---------+--------+------------+---------+------------+----------------+
| userID | name      | birthYear | addr   | mobile1 | mobile2 | height | mDate      | modType | modDate    | modUser        |
+--------+-----------+-----------+--------+---------+---------+--------+------------+---------+------------+----------------+
| BBK    | 바비킴    |      1973 | 서울   | 010     | 0000000 |    165 | 2013-05-05 | modify  | 2019-09-09 | root@localhost |
| BBK    | 바비킴    |      1973 | 서울   | 010     | 0000000 |    165 | 2013-05-05 | modify  | 2019-09-09 | root@localhost |
| LSG    | 이승기    |      1987 | 서울   | 011     | 1111111 |    182 | 2008-08-08 | delete  | 2019-09-09 | root@localhost |
+--------+-----------+-----------+--------+---------+---------+--------+------------+---------+------------+----------------+
3 rows in set (0.00 sec)