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;