[실습] 트리거

1. 삭제 연산

use academicDB;
drop table IF EXISTS testTBL;
create table testTBL (id int, txt varchar(20));
insert into testTBL values(1, '이멕스아이디');
insert into testTBL values(2, '애프터스쿨');
insert into testTBL values(3, '에이오에이');

drop trigger IF EXISTS testTrg;
DELIMITER //
create trigger testTrg
	AFTER DELETE
    ON testTBL
    FOR EACH ROW
BEGIN
	set @msg = '가수 그룹이 삭제됨';
END //
DELIMITER ;

set @msg = ' ';
insert into testTBL values(4, '나인뮤지스');
select @msg;

set SQL_SAFE_UPDATES = 0;
update testTBL set txt='에이핑크' where id=3;
select @msg;

delete from testTBL where id = 4;
select @msg;
set SQL_SAFE_UPDATES = 1;

 

2. 갱신, 삭제 연산 - 로그 데이터

use academicdb;
insert into student values(12345678, "김철수", "수학", "010-1234-5678");

drop table IF EXISTS backup_student;
create table backup_student
(sno int,
sname char(10) not null,
sdept char(20),
stel char(20),
modType char(2),
modDate date,
modUser varchar(50)
);

drop trigger IF EXISTS UpdateTrg;
DELIMITER //
create trigger UpdateTrg
	AFTER UPDATE
    ON student
    FOR EACH ROW
BEGIN
	insert into backup_student values (OLD.sno, OLD.sname, OLD.sdept, OLD.stel, '수정', CURDATE(), CURRENT_USER());
END //
 DELIMITER ;
 
 drop trigger IF EXISTS DeleteTrg;
 DELIMITER //
 create trigger DeleteTrg
	AFTER DELETE
    ON student
    FOR EACH ROW
    BEGIN
		insert into backup_student values (OLD.sno, OLD.sname, OLD.sdept, OLD.stel, '삭제', CURDATE(), CURRENT_USER());
	END //
DELIMITER ;

update student set sname = '홍길동' where sno=12345678;
delete from student where sno='12345678';
select * from backup_student;