dbms_trigger.sql

-- Cập nhật trạng thái phiếu mượn

CREATE DEFINER = CURRENT_USER TRIGGER `LoanDetails_AFTER_INSERT` 
AFTER INSERT ON `LoanDetails` FOR EACH ROW
BEGIN
	DECLARE _return_num INT;
	SELECT COUNT(is_return) INTO  _return_num FROM LoanDetails WHERE loan_id = NEW.loan_id AND is_return = 1;
	    IF _return_num = (SELECT COUNT(*) FROM LoanDetails WHERE loan_id = NEW.loan_id) THEN
		UPDATE Loans SET is_return = 1 WHERE loan_id = NEW.loan_id;
	 END IF;
END;

CREATE DEFINER = CURRENT_USER TRIGGER `LoanDetails_AFTER_UPDATE` 
AFTER UPDATE ON `LoanDetails` FOR EACH ROW
BEGIN
	DECLARE _return_num INT;
	SELECT COUNT(is_return) INTO  _return_num FROM LoanDetails WHERE loan_id = NEW.loan_id AND is_return = 1;
    IF _return_num = (SELECT COUNT(*) FROM LoanDetails WHERE loan_id = NEW.loan_id) THEN
		UPDATE Loans SET is_return = 1 WHERE loan_id = NEW.loan_id;
    END IF;
END;
    


-- Không thể xóa ngôn ngữ mặc định 
CREATE DEFINER = CURRENT_USER TRIGGER `ULib`.`Languages_BEFORE_DELETE` 
BEFORE DELETE ON `Languages` FOR EACH ROW
BEGIN
	IF `OLD`.`language_id` = 1 OR `OLD`.`language_id` = 2 THEN
		SIGNAL SQLSTATE '45000'
		SET MESSAGE_TEXT = 'Can not remove default language';
    END IF;
END;