Today
- SQL 프로그래밍
-- 2024-02-23
-- 트리거(Trigger) : 특정 Event 발생 시 설정해놓은 행동을 하도록 프로그래밍 하는 것
/*
[기본형식]
CREATE TRIGGER 트리거명 동작할시간 동작할이벤트
ON 테이블명 FOR EACH ROW
BEGIN
트러거구문;
END;
◆ 동작할 시간 : AFTER / BEFORE
동작할 이벤트 : INSERT / UPDATE / DELETE
◆ 제약사항 :
1) 전역변수 사용 불가 / 지역변수만 사용 가능
2) 결과 set을 반환하지 않음
3) 외래키에 의해서 활성화 되지 않음 (외래키 제약조건으로 인해 발생한 INSERT / UPDATE / DELETE 에서는 활성화되지 않음.)
4) 트리거 내부에는 PREPARE, EXECUTE, DEALLOCATE PREPARE 문은 사용할 수 없습니다.
◆ 장점 : 내가 원하는 상황에서 동작을 알아서 함 (로그 남기는걸로 자주 사용함)
◆ 단점 : 일부 트리거 설정되어있는 DB는 복구가 어려울 수 있습니다.
◆ CREATE OR REPLACE로 사용할 수도 있지만, 해당 키워드로 사용시 다른 사람의 작업한거를 덮어쓸 수 있기 때문에 지양하는게 좋음
[트리거 내 임시테이블 사용방법]
| 이벤트 | NEW TEPM TABE | ON TABLE | OLD TEPM TABE |
|------------|---------------|-----------|---------------|
| INSERT | NEW ROW | NEW ROW | - |
| UPDATE | NEW ROW | NEW ROW | OLD ROW |
| DELETE | - | - | OLD ROW |
◆ NEW 임시테이블, OLD 임시테이블 2개와 대상 테이블(ON절에 작성한 테이블) 총 3개의 테이블에 접근 가능합니다.
◆ 위 NEW, OLD 임시테이블의 경우 트리거 내에서만 접근할 수 있으며, NEW.열이름 / OLD.열이름 으로 접근할 수 있습니다.
*/
# 트리거 써보기1 (INSERT)
DROP TABLE TMP_tbl_member;
CREATE TEMPORARY TABLE TMP_tbl_member (
memberId VARCHAR(20) NULL,
NAME VARCHAR(20) NULL,
regDate DATETIME NULL DEFAULT NOW()
)
COLLATE = 'utf8mb4_general_ci'
ENGINE = INNODB
;
CREATE TEMPORARY TABLE TMP_tbl_member_test (
content TEXT NULL,
event_type VARCHAR(20) NULL,
insert_date DATETIME NULL DEFAULT NOW()
)
COLLATE = 'utf8mb4_general_ci'
ENGINE = INNODB
;
DELIMITER $$
CREATE TRIGGER TRG_member_temp_insert AFTER INSERT
ON tbl_member FOR EACH ROW
BEGIN
DECLARE con TEXT;
SET con = CONCAT(NEW.memberID, NEW.name);
INSERT INTO TMP_tbl_member_test(content, event_type)
VALUES (con, 'INSERT');
END;
$$
INSERT INTO tbl_member(memberId, NAME, pwd) VALUES ('testTrigger1', '테스트','1234');
INSERT INTO tbl_member(memberId, NAME, pwd) VALUES ('testTrigger2', '테스트2','1234');
SELECT * FROM TMP_tbl_member_test;
# 트리거 써보기2(UPDATE)
DELIMITER $$
CREATE TRIGGER TRG_member_temp_update AFTER UPDATE
ON tbl_member FOR EACH ROW
BEGIN
DECLARE old_con TEXT;
DECLARE new_con TEXT;
SET old_con = CONCAT(OLD.memberID, OLD.name);
INSERT INTO TMP_tbl_member_test(content, event_type)
VALUES (old_con, 'UPDATE_OLD');
SET new_con = CONCAT(NEW.memberID, NEW.name);
INSERT INTO TMP_tbl_member_test(content, event_type)
VALUES (new_con, 'UPDATE_NEW');
END;
$$
UPDATE tbl_member
SET memberId = 'testTrigger1_1'
, NAME = '트리거테스트1'
WHERE memberId = 'testTrigger1';
UPDATE tbl_member
SET memberId = 'testTrigger2_1'
, NAME = '트리거테스트2'
WHERE memberId = 'testTrigger2';
SELECT * FROM TMP_tbl_member_test;
-- 실습 : 트리거 써보기3(DELETE)
/*
1. tbl_member에서 회원정보가 삭제되었을 때 동작하는 트리거를
TRG_member_temp_delete 으로 작성하세요.
*/
DELIMITER $$
CREATE TRIGGER TRG_member_temp_delete AFTER DELETE
ON tbl_member FOR EACH ROW
BEGIN
DECLARE old_con TEXT;
SET old_con = CONCAT(OLD.memberID, OLD.name);
INSERT INTO TMP_tbl_member_test(content, event_type)
VALUES (old_con, 'DELETE');
END;
$$
DELIMITER ;
DELETE FROM tbl_member WHERE memberId = 'testTrigger2_1';
DELETE FROM tbl_member WHERE memberId = 'testTrigger1_1';
SELECT * FROM TMP_tbl_member_test;
# INSERT BEFORE 트리거
DELIMITER $$
CREATE TRIGGER TRG_member_temp_insert_before BEFORE INSERT
ON tbl_member FOR EACH ROW
BEGIN
DECLARE con TEXT;
SET NEW.name = CONCAT('before',NEW.name); # BEFROE INSERT 이기 떄문에 INSERT가 수행되기전 NEW.name을 수정하기 때문에 수정된 이름으로 INSERT됨.
SET con = CONCAT(NEW.memberID, NEW.name);
INSERT INTO TMP_tbl_member_test(content, event_type)
VALUES (con, 'BEFORE INSERT');
END;
$$
DELIMITER ;
INSERT INTO tbl_member(memberId, NAME, pwd) VALUES ('testTrigger1', '테스트','1234');
INSERT INTO tbl_member(memberId, NAME, pwd) VALUES ('testTrigger2', '테스트2','1234');
SELECT * FROM TMP_tbl_member_test;
-- 시그널(SIGNAL) : 사용자가 지정한 오류를 생성하는 문으로, 주로 저장프로시저 또는 트리거에서 사용합니다.
/*
*/
# 위 만든 드리거 다 없애기
DROP TRIGGER TRG_member_temp_insert;
DROP TRIGGER TRG_member_temp_update;
DROP TRIGGER TRG_member_temp_delete;
DROP TRIGGER TRG_member_temp_insert_before;
# 시그널을 이용한 트리거 작성
DELIMITER $$
CREATE TRIGGER TRG_tbl_member_insert_before
BEFORE INSERT ON tbl_member FOR EACH ROW
BEGIN
DECLARE con TEXT;
IF NEW.name IS NULL THEN
SIGNAL SQLSTATE '90000'
SET MESSAGE_TEXT = '이름 컬럼이 null 입니다.'; # MESSAGE_TEXT는 시스템 변수! 우리가 지정해서 막 넣어놓음
ELSE
SET con = CONCAT(NEW.memberID, NEW.name);
INSERT INTO TMP_tbl_member_test(content, event_type)
VALUES (con, 'BEFORE INSERT');
END IF;
END;
$$
delimiter ;
INSERT INTO tbl_member(memberId, NAME, pwd) VALUES ('testTrigger1', NULL,'1234');
SELECT * FROM TMP_tbl_member_test;
SELECT * FROM tbl_member;
DELETE FROM tbl_member WHERE memberId LIKE 'testTrigger%';
# 오류 핸들러 한번 연습해보기
DELIMITER $$
BEGIN NOT ATOMIC
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1
@sql_state = RETURNED_SQLSTATE
, @errno = mysql_errno
, @err_text = MESSAGE_TEXT;
SELECT @sql_state, @errno, @err_text;
END;
INSERT INTO tbl_member(memberId, NAME, pwd) VALUES ('testTrigger1', NULL,'1234');
END;
$$
DELIMITER ;
# 멤버 로그테이블 만들어놓기
CREATE TABLE `tbl_member_log` (
`idx` INT NOT NULL AUTO_INCREMENT,
`memberId` VARCHAR(20) NOT NULL COMMENT '회원ID' COLLATE 'utf8mb4_general_ci',
`name` VARCHAR(20) NULL DEFAULT NULL COMMENT '이름' COLLATE 'utf8mb4_general_ci',
`pwd` VARCHAR(300) NOT NULL COMMENT '비밀번호' COLLATE 'utf8mb4_general_ci',
`jumin` VARCHAR(300) NULL DEFAULT NULL COMMENT '주민번호' COLLATE 'utf8mb4_general_ci',
`addr1` VARCHAR(100) NULL DEFAULT NULL COMMENT '주소1' COLLATE 'utf8mb4_general_ci',
`addr2` VARCHAR(100) NULL DEFAULT NULL COMMENT '주소2' COLLATE 'utf8mb4_general_ci',
`birthday` CHAR(10) NULL DEFAULT NULL COMMENT '생년월일(YYYY-MM-DD)' COLLATE 'utf8mb4_general_ci',
`jobCode` CHAR(2) NULL DEFAULT NULL COMMENT '직업코드' COLLATE 'utf8mb4_general_ci',
`mileage` DECIMAL(7,0) NULL DEFAULT '0' COMMENT '마일리지',
`memberState` CHAR(1) NULL DEFAULT 'N' COMMENT '회원상태' COLLATE 'utf8mb4_general_ci',
`regDate` DATETIME NOT NULL DEFAULT current_timestamp() COMMENT '등록일',
`leaveDate` DATETIME NULL DEFAULT NULL COMMENT '탈퇴일시',
`pwdChangeDate` DATETIME NULL DEFAULT NULL COMMENT '비밀번호 변경일',
`recommender` VARCHAR(20) NULL DEFAULT NULL COMMENT '추천인ID' COLLATE 'utf8mb4_general_ci',
`event_type` VARCHAR(20) NULL COMMENT '트리거이벤트' COLLATE 'utf8mb4_general_ci',
`logDate` DATETIME NULL DEFAULT NOW() COMMENT '로그등록일',
PRIMARY KEY (`idx`) USING BTREE
)
COMMENT='회원테이블'
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
# 로그 등록하는 트리거 작업
DELIMITER $$
CREATE OR REPLACE TRIGGER TRG_tbl_member_update
AFTER UPDATE ON tbl_member FOR EACH ROW
BEGIN
DECLARE old_con TEXT;
DECLARE new_con TEXT;
SET old_con = CONCAT(OLD.memberID, OLD.name);
INSERT INTO TMP_tbl_member_test(content, event_type)
VALUES (old_con, 'UPDATE_OLD');
SET new_con = CONCAT(NEW.memberID, NEW.name);
INSERT INTO TMP_tbl_member_test(content, event_type)
VALUES (new_con, 'UPDATE_NEW');
INSERT INTO tbl_member_log (memberId, NAME, pwd, addr1, addr2, birthday, jobCode, mileage, memberState, regDate, leaveDate, event_type)
VALUES (OLD.memberId, OLD.NAME, OLD.pwd, OLD.addr1, OLD.addr2, OLD.birthday, OLD.jobCode, OLD.mileage, OLD.memberState, OLD.regDate, OLD.leaveDate, 'UPDATE_OLD');
END;
$$
DELIMITER ;
INSERT INTO tbl_member(memberId, NAME, pwd) VALUES ('testTrigger1', '트리거1','1234');
UPDATE tbl_member SET memberId = 'testTrigger1_2', NAME = '트리거테스트1_2' WHERE memberId = 'testTrigger1_1';
SELECT * FROM tbl_member_log;
SELECT * FROM TMP_tbl_member_test;
# DELETE TRIGGER
DELIMITER $$
CREATE TRIGGER TRG_tbl_member_delete
AFTER DELETE ON tbl_member FOR EACH ROW
BEGIN
DECLARE old_con TEXT;
SET old_con = CONCAT(OLD.memberID, OLD.name);
INSERT INTO TMP_tbl_member_test(content, event_type)
VALUES (old_con, 'DELETE');
INSERT INTO tbl_member_log (memberId, NAME, pwd, addr1, addr2, birthday, jobCode, mileage, memberState, regDate, leaveDate, event_type)
VALUES (OLD.memberId, OLD.NAME, OLD.pwd, OLD.addr1, OLD.addr2, OLD.birthday, OLD.jobCode, OLD.mileage, OLD.memberState, OLD.regDate, OLD.leaveDate, 'DELETE_OLD');
END;
$$
DELIMITER ;
UPDATE tbl_member SET memberId = 'testTrigger1_1', NAME = '트리거테스트1_1' WHERE memberId = 'testTrigger1_2';
DELETE FROM tbl_member WHERE memberId = 'testTrigger1_1';
SELECT * FROM tbl_member_log;
SELECT * FROM TMP_tbl_member_test;
/*
선생님 TIP : 영화관 / 기차표 등등 어떻게 자리 잡아놓을까?
좌석 선택 순간 좌석 테이블의 해당 좌석을 -1 시키고, 결제가 완료되는 시점에 다시 체크해서 확정
*/
SELECT * FROM tbl_orderinfo;
SELECT * FROM tbl_orderdetail;
CREATE OR REPLACE TEMPORARY TABLE TMP_tbl_order_month(
idx INT AUTO_INCREMENT PRIMARY KEY
, orderYM CHAR(7) DEFAULT NULL COLLATE 'utf8mb4_general_ci'
, goodsCode CHAR(6) NOT NULL COLLATE 'utf8mb4_general_ci'
, monthAmount DECIMAL(7,0) DEFAULT 0
, monthCancelAmount DECIMAL(7,0) DEFAULT 0
)
;
# 주문 상세 태이블 정보가 INSERT 된 후에 트리거 추가
DELIMITER $
CREATE OR REPLACE TRIGGER TRG_tbl_orderDetail_insert
AFTER INSERT ON tbl_orderdetail FOR EACH ROW
BEGIN
DECLARE in_amount DECIMAL(7,0) DEFAULT 0;
DECLARE normal_amount DECIMAL(7,0) DEFAULT 0;
DECLARE cancel_amount DECIMAL(7,0) DEFAULT 0;
DECLARE order_date CHAR(7);
DECLARE cancel_yn CHAR(1);
DECLARE goodsCD CHAR(6);
SELECT LEFT(CONVERT(orderDate, CHAR),7), cancelYN
INTO order_date, cancel_yn
FROM tbl_orderinfo
WHERE orderNo = NEW.orderNo;
SET goodsCD = NEW.goodsCode;
SET in_amount = NEW.amount;
IF cancel_yn = 'N' THEN
SET normal_amount = in_amount;
ELSEIF cancel_yn = 'Y' THEN
SET cancel_amount = in_amount;
END IF;
IF (SELECT COUNT(goodsCode) FROM TMP_tbl_order_month WHERE goodsCode = goodsCD AND orderYM = order_date) = 1
THEN
UPDATE TMP_tbl_order_month
SET
monthAmount = monthAmount + normal_amount
, monthCancelAmount = monthCancelAmount + cancel_amount
WHERE (goodsCode = goodsCD) && (orderYM = order_date);
ELSE
INSERT INTO TMP_tbl_order_month (orderYM, goodsCode, monthAmount, monthCancelAmount)
VALUES (order_date, goodsCD, normal_amount, cancel_amount);
END IF;
END;
$$
DELIMITER ;
INSERT INTO tbl_orderinfo(orderNo, orderDate, memberId, orderAmount, cancelYN)
VALUES ('202302001', NOW(), 'gee3', 5000, 'N');
INSERT INTO tbl_orderinfo(orderNo, orderDate, memberId, orderAmount, cancelYN)
VALUES ('202302002', NOW(), 'gee1', 1000, 'N');
INSERT INTO tbl_orderdetail(orderNo, goodsCode, unitCode, unitPrice, orderedCnt, amount)
VALUES ('202302001', 'GDS005', '02', 1000, 5, 5000);
INSERT INTO tbl_orderdetail(orderNo, goodsCode, unitCode, unitPrice, orderedCnt, amount)
VALUES ('202302001', 'GDS002', '02', 100, 10, 1000);
INSERT INTO tbl_orderdetail(orderNo, goodsCode, unitCode, unitPrice, orderedCnt, amount)
VALUES ('202302002', 'GDS005', '02', 1000, 5, 5000);
INSERT INTO tbl_orderdetail(orderNo, goodsCode, unitCode, unitPrice, orderedCnt, amount)
VALUES ('202302002', 'GDS002', '02', 100, 10, 1000);
SELECT * FROM tbl_orderinfo;
SELECT * FROM TMP_tbl_order_month;
TRUNCATE TABLE TMP_tbl_order_month;
DELETE FROM tbl_orderdetail WHERE orderNo = '202302001';
DELETE FROM tbl_orderdetail WHERE orderNo = '202302002';
DELETE FROM tbl_orderinfo WHERE orderNo = '202302001';
DELETE FROM tbl_orderinfo WHERE orderNo = '202302002';
-- 실습
/*
1. tbl_orderdetail 정보가 취소되었을 때 AFTER UPDATE 트리거 추가
tbl_orderinfo의 취소금액을 변경하고 tbl_orderinfo.cancelYN => 'Y'
tbl_orderdetail => cancelAmount => amount 금액으로
TMP_tbl_order_month.monthAmount => monthAmount - cancelAmount
TMP_tbl_order_month.montCancelhAmount => cancelAmount
*/
SELECT * FROM tbl_orderinfo;
SELECT * FROM tbl_orderdetail;
# 지현 답 : 선생님과 많이 다르게 한것 같음...
DELIMITER $
CREATE OR REPLACE TRIGGER TRG_tbl_orderDetail_update
AFTER UPDATE ON tbl_orderdetail FOR EACH ROW
BEGIN
DECLARE in_amount DECIMAL(7,0) DEFAULT 0;
DECLARE normal_amount DECIMAL(7,0) DEFAULT 0;
DECLARE cancel_amount DECIMAL(7,0) DEFAULT 0;
DECLARE order_date CHAR(7);
DECLARE cancel_yn CHAR(1);
DECLARE goodsCD CHAR(6);
SELECT LEFT(CONVERT(orderDate, CHAR),7)
INTO order_date
FROM tbl_orderinfo
WHERE orderNo = NEW.orderNo;
IF NEW.cancelAmount = OLD.cancelAmount THEN SET cancel_Amount = 0;
ELSE SET cancel_Amount = (NEW.cancelAmount - OLD.cancelAmount);
END IF;
IF NEW.amount = OLD.amount THEN SET normal_amount = 0;
ELSE SET normal_amount = (NEW.amount - OLD.amount);
END IF;
SET goodsCD = NEW.goodsCode;
IF (SELECT COUNT(cancelAmount) FROM tbl_orderdetail WHERE (orderNo = NEW.orderNo) AND (cancelAmount != 0)) != 0 THEN
SET cancel_yn = 'Y';
UPDATE tbl_orderinfo
SET cancelYN = cancel_yn, cancelDate = NOW()
WHERE orderNo = NEW.orderNo;
ELSE
SET cancel_yn = 'N';
UPDATE tbl_orderinfo
SET cancelYN = cancel_yn, cancelDate = null
WHERE orderNo = NEW.orderNo;
END if;
IF (SELECT COUNT(goodsCode) FROM TMP_tbl_order_month WHERE goodsCode = goodsCD AND orderYM = order_date) = 1
THEN
UPDATE TMP_tbl_order_month
SET
monthAmount = (monthAmount + normal_amount)
, monthCancelAmount = monthCancelAmount + cancel_amount
WHERE (goodsCode = goodsCD) && (orderYM = order_date);
END IF;
END;
$$
DELIMITER ;
UPDATE tbl_orderdetail SET cancelAmount = 1000, amount = 0 WHERE orderNo = '202302001' && goodsCode = 'GDS002';
SELECT * FROM tbl_orderinfo;
SELECT * FROM tbl_orderdetail;
SELECT * FROM TMP_tbl_order_month;
-- 저장함수(Stored Funcions) : 내장함수처럼 사용자함수를 정의하는 것으로 단일 RETUNC값을 던질 수 있습니다.
/*
[작성법]
DELIMITER $$
CREATE FUNCTION 함수명 (매개변수,,,)
RETURNS 반환데이터 유형 [NO SQL] [DETERMINISTIC | NOT DETERMINISTIC]
BEGIN
함수바디;
RETURN 값;
END;
$$
◆ NO SQL / DETERMINISTRC / NOT DETEMINISTRIC 은 필수사항은 아니며, 함수 특성에 따라 프로그램을 최정화할 수 있게 해주는 키워드 입니다.
- NO SQL : 함수내 SQL문(SELECT / INSET / UPDATE 등) 이 없음을 의미
- DETERMINSI : 결정적 함수를 의미, 같은 인자를 던지면 항상 같은 값을 반환하는 함수
- NOT DETERMINISTIC : 비결정적 함수를 의미, 같은 인자를 던져도 다른 값을 반환할 수 있는 함수
◆ 매개변수를 사용할 수 있습니다!
단, 프로시저와 다르게 IN / OUT 종류가 없이 매개변수는 전부 함수 호출시 전달 받는 매개변수이고,
대신 단일 리턴값을 던질 수 있습니다. (값 여러개 리턴 불가 / 행 리턴도 부락)
◆ 저장 함수 내 결과 셋을 반환하는 명령문은 허용되지 않습니다.
◆ 재귀적으로 사용 불가
◆ 내부에는 PREPARE, EXECUTE, DEALLOCATE PREPARE 문은 사용할 수 없습니다.
◆ 함수를 저장하면 기본적으로 시스템 DB에 저장됩니다
하지만, 해당 DB에서만 사용하는 함수라면 해당 DB에만 저장되도록 하는것이 좋습니다. => 해당 DB에 종속적이어야 관리/백업이 편리함.
◆ 저장함수를 생성하는 권한은 기본적으로 OFF로 설정되어있습니다.
'log_bin_trust_function_creators' 변수를 ON으로 설정해서
◆ 함수명은 'FN' 또는 'FC'를 접두어로 많이 사용합니다.
[사용(호출)법]
SET변수 = 함수명(인자,,,);
SELECT 함수명(인자,,,);
◆ 단일 값이 리턴되는 점을 고려해서 사용할 수 있습니다.
*/
# 생성 권한 확인
SHOW VARIABLES LIKE '%log_bin%'; # 'log_bin_trust_function_creators' 확인 결과, 현재 나는 OFF 상태
# 저장권한 OFF인채로 함수 만들어보기
DELIMITER $$
CREATE FUNCTION FN_ADD(num1 INT, num2 INT)
RETURNS INT
BEGIN
RETURN num1 + num2;
END;
$$
DELIMITER ;
# 사용해보기
SELECT FN_ADD(10, 20);
SET @result = FN_ADD(30, 40);
SELECT @result;
#
DELIMITER $$
CREATE FUNCTION FN_ADD2(num1 INT, num2 INT)
RETURNs INT NO SQL DETERMINISTIC
RETURN num1 + num2;
$$
DELIMITER ;
SELECT FN_ADD2(10, 20);
SET @result2 = FN_ADD2(30, 40);
SELECT @result2;
-- 실습 : 생년월일(YYYY-MM-DD)을 입력받아, 나이를 계산하는 함수를 작성하세요
DELIMITER $$
CREATE OR REPLACE FUNCTION FN_CALCULATE_AGE(birth DATE)
RETURNS INT NO SQL
BEGIN
DECLARE age INT;
# 만 나이체계
IF to_char(birth, 'MM-DD') <= to_char(NOW(), 'MM-DD') THEN
SET age = (YEAR(NOW()) - YEAR(birth));
ELSE
SET age = (YEAR(NOW()) - YEAR(birth)) - 1;
END IF;
RETURN age;
# 원래 한국나이
# SET age = (YEAR(NOW()) - YEAR(birth)) + 1;
END;
$$
DELIMITER ;
SELECT FN_CALCULATE_AGE('1996-10-17') AS 'gee', FN_CALCULATE_AGE('1995-10-31') AS 'tae', FN_CALCULATE_AGE('1996-02-23') AS 'test' ;Review
- 9장 절차적 SQL프로그래밍 파트는 엄청 중요하다고 하심
TO DO
- Java 최종 정리(~2/25) => 실패...(~3/2일 까지로 연장)
- Maria DB 정리 수업진도 따라잡기
정리하고 있는 부분 : 332p / 진도 : 457p
블로그 이전 했습니다. 아래 블로그 아이콘(🏠) 눌러서 놀러오세요