Maria DB TRIGGER

#트리거
#연달아 사용할때 사용한다.

 

#사용할 테이블 생성
CREATE TABLE testTBL(
id INT ,
txt CHAR(10)
);

 

#값입력
INSERT INTO testtbl VALUES (1, 'aaaa'), (2, 'bbbb'), (3, 'cccc');

 

#트리거 생성

DELIMITER //
CREATE  TRIGGER 트리거이름
(AFTER, BEFORE) #조건
ON 테이블이름  #testtbl테이블에서
FOR EACH ROW  #각행마다 실행시켜라
BEGIN
#트리거 실행 시 작동되는 코드
END //
DELIMITER ;

 

#예제
DELIMITER //
CREATE  TRIGGER testTrg
AFTER DELETE  
ON testtbl 
FOR EACH ROW  
BEGIN
SET @msg= '삭제가 되었어요..';  
END //
DELIMITER ;

 

#트리거 확인하기
SET @msg='';
INSERT INTO testtbl VALUES(4, 'dddd');
SELECT @msg;

UPDATE testtbl SET txt='xxxx' WHERE id=4;
SELECT @msg;

DELETE FROM testtbl WHERE  id=4;
SELECT * FROM testtbl;
SELECT @msg;  #트리거에서 지정한 메시지인 삭제가 되었다는 메시지가 뜸

 

 

 

 

 

#회원테이블에 update나 delete를 시도하면, 수정/삭제된 데이터를 별도의 테이블에 보관하고 

변경된 일자와 변경한 사람을 기록하자.

 

#테이블생성
CREATE  TABLE usertbl(
userID CHAR(8) 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
);

 

#테이블 데이터 입력
INSERT INTO usertbl VALUES ('HGD', '홍길동', 1980, '서울', '010', '11112222', 173, '2023-01-23');
SELECT*FROM usertbl;

 

#별도의 테이블 생성
CREATE  TABLE backupUsertbl(
userID CHAR(8) , 
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) #변경된 사용자
);

 

#예제 업데이트 시 트리거 생성
DELIMITER $$
CREATE TRIGGER backUserTbl_UpdateTrag
AFTER UPDATE ON usertbl FOR EACH ROW
BEGIN
INSERT INTO backUpusertbl VALUES(OLD.userID, OLD.name, OLD.birthYear, OLD.addr, OLD.mobile1,
OLD.mobile2, OLD.height, OLD.mDate, '수정', CURDATE(), CURRENT_USER());
END $$
DELIMITER ;

 

#트리거 확인
UPDATE usertbl SET height = 198 WHERE userID='HGD';
SELECT*FROM usertbl;
SELECT*FROM backupUserTbl;

UPDATE usertbl SET addr = '부산' WHERE userID='HGD';
SELECT*FROM usertbl;
SELECT*FROM backupUserTbl;

 

#예제 삭제 시 트리거 생성
DELIMITER $$
CREATE TRIGGER backUpUserTbl_DeleteTrag
AFTER DELETE ON usertbl FOR EACH ROW 
BEGIN
INSERT INTO backUpusertbl VALUES(OLD.userID, OLD.name, OLD.birthYear, OLD.addr, OLD.mobile1,
OLD.mobile2, OLD.height, OLD.mDate, '삭제', CURDATE(), CURRENT_USER());
END $$
delimiter ;

 

#트리거 확인
DELETE FROM usertbl WHERE userID='HGD';
SELECT*FROM usertbl;
SELECT*FROM backupUserTbl;

 

 

 

 

 

 

 

#예제
#데이터가 테이블에 입력될 때 출생년도를 검사해서 데이터에 문제가 있는지 확인하는 트리거
#값을 변경해서 입력시키는 Before Insert 트리거를 작성해보자.
#출생년도는 1900년 이상이어야 한다.
#출생년도가 현재 년도 보다 크면 현재 년도로 변경한다.

DELIMITER %%
CREATE TRIGGER birthChecktrg
BEFORE INSERT ON usertbl FOR EACH row
BEGIN  
if NEW.birthyear < 1900 then   #데이터가 입력될 때 임시테이블이 있음 new, old
SET NEW.birthYear=0;
ELSEIF NEW.birthyear > YEAR(CURDATE()) then
SET NEW.birthYear=YEAR(CURDATE());
END if;
END %%
DELIMITER ;

#트리거 확인
INSERT INTO usertbl VALUES ('Hdd3', '고길동', 1800, '서울', '010', '11112222', 173, '2023-01-23');
SELECT*FROM usertbl;
UPDATE usertbl SET birthYear =1800 WHERE userID='Hdddddd';
SELECT*FROM usertbl;
SELECT*FROM backupUserTbl;