SQL정리(trigger)

### h_trigger 폴더 > a_트리거01 파일 ###

# 트리거(trigger)
# : '방아쇠', 자동 실행
# : 자동으로 수행하여 사용자가 추가 작업을 잊어버리는 실수를 방지

# 트리거 사용 목적
-- 수동으로 처리하는 일련 동작을 자동으로 처리하여 데이터의 오류를 방지
-- 데이터 무결성 처리에 도움

# insert, update, delete (DML, 데이터 조작 언어)작업이 발생하면 실행되는 코드
# : 이벤트가 발생할 때 작동

# 트리거 종류
# after 트리거*, before 트리거

# 트리거 문법 체계(기본 형식)
# : 스토어드 프로시저와 유사

# cf) 차이점: call 문으로 직접 실행 X
# 무조건 테이블에서 insert, update, delete 등의 이벤트가 발생하는 경우에만 자동 실행

use market_db;
create table if not exists trigger_table (
id int,
    txt varchar(10)
);

insert into trigger_table
values
(1, '레드벨벳'),
(2, '잇지'),
(3, '블랙핑크');

select * from trigger_table;

-- 트리거 생성
drop trigger if exists myTrigger;

delimiter $$
create trigger myTrigger
# 트리거 종류를 설정
    # : 트리거종류 이벤트종류
    # 1) 트리거종류: after, before
    # 2) insert, update, delete
    after delete -- delete문이 발생된 이후에 작동
    
    # on 조건으로 트리거를 부착할 테이블을 지정
    on trigger_table 
    for each row -- 각 행마다 적용시킴 (모든 트리거에 작성)

# 실제 트리거에서 작동할 부분
begin 
set @msg = '가수 그룹이 삭제됨';
end $$

delimiter ;

set @msg = '';
select @msg;

insert into trigger_table values (4, '마마무');
select @msg;

update trigger_table set txt = '블핑' where id = 3;
select @msg;

select * from trigger_table;
select @msg;

delete from trigger_table
where id = 4;
select @msg;

# 트리거 VS 트랜잭션
# 트리거: 특정 조건이 충족되거나 이벤트가 발생할 때 자동으로 실행
# 트랜잭션: 일련 동작의 묶음, 원자성을 보장

# cf) 원자성: 모두 성공 또는 모두 실패

 

 

 

### b_트리거02 ###

# 트리거 예제 #

use market_db;

# singer 테이블: member 테이블에서 데이터를 가져옴
create table singer (
select 
mem_id, mem_name, mem_number, addr 
    from 
`member`
);

-- 백업 테이블
-- : 변동 사항을 기록할 테이블
create table backup_singer (
mem_id char(8) not null,
    mem_name varchar(10) not null,
    mem_number int not null,
    addr char(2) not null,
    
    modType char(2), -- 변경된 타입 (수정 | 삭제)
    modDate date, -- 변경된 날짜
    modUser varchar(30) -- 변경한 사용자
);

# 변경(update)가 발생했을 때 작동하는 트리거
drop trigger if exists singer_updateTrg;

delimiter $$
create trigger singer_updateTrg -- 트리거명
after update -- 변경 후에 작동하도록 지정
on singer -- 트리거를 부착할 테이블
for each row 
begin
insert into backup_singer
    values 
(OLD.mem_id, OLD.mem_name, OLD.mem_number, OLD.addr
, '수정', curdate(), current_user()
        );
end $$
delimiter ;

# OLD 테이블
# : update나 delete가 수행될 때
# : 변경되기 전의 데이터가 잠깐 저장되는 임시 테이블

# curdate(): 현재 날짜
# current_user(): 현재 작업 중인 사용자
select current_user();

select * from singer;

update singer 
set addr = '미국' 
where 
mem_id = 'BLK';
    
update singer
set mem_number = 6
where
mem_id = 'BLK';
    
select * from backup_singer;

# 삭제가 발생했을 때 작동하는 트리거
# singer_deleteTrg
drop trigger if exists siger_deleteTrg;
delimiter $$
create trigger singet_deleteTrg after delete on singer for each row
begin 
insert into backup_singer
    values 
(OLD.mem_id, OLD.mem_name, OLD.mem_number, OLD.addr
, '삭제', curdate(), current_user()
        );
end $$
delimiter ;
delete from singer where mem_num>=7;
select * from backup_singer

 

 

 

use baseball_league;

select* from players;
select * from teams;

# 트리거 생성
# 선수가 새로 등록될때 마다 해당 선수의 팀에 속한 선수의 수를 자동으로 업데이트

create table if not exists player_insert_logs(
log_id int auto_increment primary key,
    team_id int,
    log_msg varchar(255),
    log_time datetime
);

drop trigger if exists after_player_insert;

delimiter &&
create trigger after_player_insert after insert on players for each row
begin insert into player_insert_logs(team_id, log_msg, log_time) values (new.team_id, concat('team', new.team_id), now());
end &&
# old: update, delete시 변경사항이 임시로 저장되는 테이블
# new: insert, update, delete 시 방금 삽입 혹은 갱신된 행을 참조
delimiter ;

insert into players values(106,'ff','타자','2000-01-01',1);
select * from player_insert_logs;