Bài tập & đáp án

CREATE TRIGGER TRG_KHACHHANG_NGDK_BIENTAM
ON KHACHHANG
AFTER UPDATE
AS
BEGIN
    DECLARE @MAKH CHAR(4), @NGDK SMALLDATETIME, @NGHD_SomNhat SMALLDATETIME;

    SELECT @MAKH = MAKH, @NGDK = NGDK FROM inserted;
    SELECT @NGHD_SomNhat = MIN(NGHD) FROM HOADON WHERE MAKH = @MAKH;

    IF (@NGHD_SomNhat IS NOT NULL AND @NGDK > @NGHD_SomNhat)
    BEGIN
        RAISERROR(N'Ngày đăng ký thành viên phải nhỏ hơn hoặc bằng ngày mua hàng.', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END
END;

CREATE TRIGGER TRG_HOADON_NGHD_NGDK_BIENTAM
ON HOADON
AFTER INSERT, UPDATE
AS
BEGIN
    DECLARE @NGHD SMALLDATETIME, @NGDK SMALLDATETIME;

    SELECT @NGHD = i.NGHD, @NGDK = kh.NGDK
    FROM inserted i
    JOIN KHACHHANG kh ON kh.MAKH = i.MAKH;

    IF (@NGDK IS NOT NULL AND @NGHD < @NGDK)
    BEGIN
        RAISERROR(N'Ngày lập hóa đơn phải lớn hơn hoặc bằng ngày khách hàng đăng ký thành viên.', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END
END;

CREATE TRIGGER TRG_NHANVIEN_NGVL_BIENTAM
ON NHANVIEN
AFTER UPDATE
AS
BEGIN
    DECLARE @MANV CHAR(4), @NGVL SMALLDATETIME, @NGHD_SomNhat SMALLDATETIME;

    SELECT @MANV = MANV, @NGVL = NGVL FROM inserted;
    SELECT @NGHD_SomNhat = MIN(NGHD) FROM HOADON WHERE MANV = @MANV;

    IF (@NGHD_SomNhat IS NOT NULL AND @NGVL > @NGHD_SomNhat)
    BEGIN
        RAISERROR(N'Ngày vào làm phải nhỏ hơn hoặc bằng ngày lập hóa đơn.', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END
END;

CREATE TRIGGER TRG_HOADON_NGHD_NGVL_BIENTAM
ON HOADON
AFTER INSERT, UPDATE
AS
BEGIN
    DECLARE @NGHD SMALLDATETIME, @NGVL SMALLDATETIME;

    SELECT @NGHD = i.NGHD, @NGVL = nv.NGVL
    FROM inserted i
    JOIN NHANVIEN nv ON nv.MANV = i.MANV;

    IF (@NGHD < @NGVL)
    BEGIN
        RAISERROR(N'Ngày lập hóa đơn phải lớn hơn hoặc bằng ngày nhân viên vào làm.', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END
END;

CREATE TRIGGER TRG_CTHD_CAPNHAT_TRIGIA_BIENTAM
ON CTHD
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @SOHD INT;

    SELECT @SOHD = SOHD FROM inserted;
    IF (@SOHD IS NULL) SELECT @SOHD = SOHD FROM deleted;

    UPDATE HOADON
    SET TRIGIA = ISNULL((
        SELECT SUM(SL * GIA)
        FROM CTHD ct JOIN SANPHAM sp ON sp.MASP = ct.MASP
        WHERE ct.SOHD = @SOHD
    ), 0)
    WHERE SOHD = @SOHD;
END;

CREATE TRIGGER TRG_HOADON_CAPNHAT_DOANHSO_BIENTAM
ON HOADON
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @MAKH CHAR(4);

    SELECT @MAKH = MAKH FROM inserted;
    IF (@MAKH IS NULL) SELECT @MAKH = MAKH FROM deleted;

    IF (@MAKH IS NOT NULL)
    BEGIN
        UPDATE KHACHHANG
        SET DOANHSO = ISNULL((SELECT SUM(TRIGIA) FROM HOADON WHERE MAKH = @MAKH), 0)
        WHERE MAKH = @MAKH;
    END
END;

CREATE TRIGGER TRG_HOCVIEN_BAOVE_LOPTRUONG_BIENTAM
ON HOCVIEN
AFTER DELETE
AS
BEGIN
    DECLARE @MAHV CHAR(5), @MALOP CHAR(3);

    SELECT @MAHV = MAHV, @MALOP = MALOP FROM deleted;

    IF EXISTS (SELECT * FROM LOP WHERE TRGLOP = @MAHV AND MALOP = @MALOP)
    BEGIN
        RAISERROR(N'Học viên đang là lớp trưởng, không thể xóa.', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END
END;

CREATE TRIGGER TRG_KHOA_KIEMTRA_TRGKHOA_BIENTAM
ON KHOA
AFTER INSERT, UPDATE
AS
BEGIN
    DECLARE @TRGKHOA CHAR(4), @MAKHOA CHAR(4);

    SELECT @TRGKHOA = TRGKHOA, @MAKHOA = MAKHOA FROM inserted;

    IF (@TRGKHOA IS NOT NULL AND NOT EXISTS (
        SELECT * FROM GIAOVIEN
        WHERE MAGV = @TRGKHOA AND MAKHOA = @MAKHOA AND HOCVI IN (N'TS', N'PTS')
    ))
    BEGIN
        RAISERROR(N'Trưởng khoa phải là giáo viên thuộc khoa và có học vị TS hoặc PTS.', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END
END;

CREATE TRIGGER TRG_KETQUATHI_KIEMTRA_DAHOC_BIENTAM
ON KETQUATHI
AFTER INSERT, UPDATE
AS
BEGIN
    DECLARE @MAHV CHAR(5), @MAMH CHAR(10), @NGTHI SMALLDATETIME, @MALOP CHAR(3);

    SELECT @MAHV = MAHV, @MAMH = MAMH, @NGTHI = NGTHI FROM inserted;
    SELECT @MALOP = MALOP FROM HOCVIEN WHERE MAHV = @MAHV;

    IF NOT EXISTS (
        SELECT * FROM GIANGDAY
        WHERE MALOP = @MALOP AND MAMH = @MAMH AND DENNGAY <= @NGTHI
    )
    BEGIN
        RAISERROR(N'Học viên chỉ được thi môn học mà lớp mình đã học xong.', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END
END;

CREATE TRIGGER TRG_GIANGDAY_TOIDA_3MON_BIENTAM
ON GIANGDAY
AFTER INSERT, UPDATE
AS
BEGIN
    DECLARE @MALOP CHAR(3), @HOCKY TINYINT, @NAM SMALLINT, @SoMon INT;

    SELECT @MALOP = MALOP, @HOCKY = HOCKY, @NAM = NAM FROM inserted;
    SELECT @SoMon = COUNT(*) FROM GIANGDAY WHERE MALOP = @MALOP AND HOCKY = @HOCKY AND NAM = @NAM;

    IF (@SoMon > 3)
    BEGIN
        RAISERROR(N'Mỗi học kỳ của một năm học, một lớp chỉ được học tối đa 3 môn.', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END
END;

CREATE TRIGGER TRG_HOCVIEN_CAPNHAT_SISO_BIENTAM
ON HOCVIEN
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @MALOP CHAR(3);

    SELECT @MALOP = MALOP FROM inserted;
    IF (@MALOP IS NULL) SELECT @MALOP = MALOP FROM deleted;

    UPDATE LOP
    SET SISO = (SELECT COUNT(*) FROM HOCVIEN WHERE MALOP = @MALOP)
    WHERE MALOP = @MALOP;
END;

CREATE TRIGGER TRG_DIEUKIEN_KIEMTRA_BIENTAM
ON DIEUKIEN
AFTER INSERT, UPDATE
AS
BEGIN
    DECLARE @MAMH CHAR(10), @MAMH_TRUOC CHAR(10);

    SELECT @MAMH = MAMH, @MAMH_TRUOC = MAMH_TRUOC FROM inserted;

    IF (@MAMH = @MAMH_TRUOC)
    BEGIN
        RAISERROR(N'Một môn học không thể là điều kiện tiên quyết của chính nó.', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END

    IF EXISTS (SELECT * FROM DIEUKIEN WHERE MAMH = @MAMH_TRUOC AND MAMH_TRUOC = @MAMH)
    BEGIN
        RAISERROR(N'Không được tồn tại đồng thời (A,B) và (B,A) trong DIEUKIEN.', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END
END;

CREATE TRIGGER TRG_GIAOVIEN_KIEMTRA_MUCLUONG_BIENTAM
ON GIAOVIEN
AFTER INSERT, UPDATE
AS
BEGIN
    DECLARE @MAGV CHAR(4), @HOCVI VARCHAR(10), @HOCHAM VARCHAR(10),
            @HESO NUMERIC(4,2), @MUCLUONG MONEY;

    SELECT @MAGV = MAGV, @HOCVI = HOCVI, @HOCHAM = HOCHAM,
           @HESO = HESO, @MUCLUONG = MUCLUONG
    FROM inserted;

    IF EXISTS (
        SELECT * FROM GIAOVIEN
        WHERE HOCVI = @HOCVI
          AND ISNULL(HOCHAM, N'') = ISNULL(@HOCHAM, N'')
          AND HESO = @HESO
          AND MAGV <> @MAGV
          AND MUCLUONG <> @MUCLUONG
    )
    BEGIN
        RAISERROR(N'Giáo viên có cùng học vị, học hàm, hệ số lương thì mức lương phải bằng nhau.', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END
END;

CREATE TRIGGER TRG_KETQUATHI_KIEMTRA_THILAI_BIENTAM
ON KETQUATHI
AFTER INSERT
AS
BEGIN
    DECLARE @MAHV CHAR(5), @MAMH CHAR(10), @LANTHI TINYINT, @DiemLanTruoc NUMERIC(4,2);

    SELECT @MAHV = MAHV, @MAMH = MAMH, @LANTHI = LANTHI FROM inserted;

    IF (@LANTHI > 1)
    BEGIN
        SELECT @DiemLanTruoc = DIEM FROM KETQUATHI
        WHERE MAHV = @MAHV AND MAMH = @MAMH AND LANTHI = @LANTHI - 1;

        IF (@DiemLanTruoc IS NULL OR @DiemLanTruoc >= 5)
        BEGIN
            RAISERROR(N'Học viên chỉ được thi lại khi điểm lần thi trước đó dưới 5.', 16, 1);
            ROLLBACK TRANSACTION;
            RETURN;
        END
    END
END;

CREATE TRIGGER TRG_KETQUATHI_KIEMTRA_NGTHI_BIENTAM
ON KETQUATHI
AFTER INSERT, UPDATE
AS
BEGIN
    DECLARE @MAHV CHAR(5), @MAMH CHAR(10), @LANTHI TINYINT, @NGTHI SMALLDATETIME;

    SELECT @MAHV = MAHV, @MAMH = MAMH, @LANTHI = LANTHI, @NGTHI = NGTHI FROM inserted;

    IF EXISTS (
        SELECT * FROM KETQUATHI
        WHERE MAHV = @MAHV AND MAMH = @MAMH AND LANTHI <> @LANTHI
          AND (
              (LANTHI < @LANTHI AND NGTHI >= @NGTHI)
              OR (LANTHI > @LANTHI AND NGTHI <= @NGTHI)
          )
    )
    BEGIN
        RAISERROR(N'Ngày thi của lần thi sau phải lớn hơn ngày thi của lần thi trước.', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END
END;

CREATE TRIGGER TRG_GIANGDAY_KIEMTRA_TIENQUYET_BIENTAM
ON GIANGDAY
AFTER INSERT, UPDATE
AS
BEGIN
    DECLARE @MALOP CHAR(3), @MAMH CHAR(10), @TUNGAY SMALLDATETIME;

    SELECT @MALOP = MALOP, @MAMH = MAMH, @TUNGAY = TUNGAY FROM inserted;

    IF EXISTS (
        SELECT * FROM DIEUKIEN dk
        WHERE dk.MAMH = @MAMH
          AND NOT EXISTS (
              SELECT * FROM GIANGDAY gd
              WHERE gd.MALOP = @MALOP AND gd.MAMH = dk.MAMH_TRUOC AND gd.DENNGAY <= @TUNGAY
          )
    )
    BEGIN
        RAISERROR(N'Lớp phải học xong các môn tiên quyết trước khi được phân công học môn liền sau.', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END
END;

CREATE TRIGGER TRG_GIANGDAY_KIEMTRA_KHOA_BIENTAM
ON GIANGDAY
AFTER INSERT, UPDATE
AS
BEGIN
    DECLARE @MAGV CHAR(4), @MAMH CHAR(10), @MAKHOA_GV CHAR(4), @MAKHOA_MH CHAR(4);

    SELECT @MAGV = MAGV, @MAMH = MAMH FROM inserted;
    SELECT @MAKHOA_GV = MAKHOA FROM GIAOVIEN WHERE MAGV = @MAGV;
    SELECT @MAKHOA_MH = MAKHOA FROM MONHOC WHERE MAMH = @MAMH;

    IF (@MAKHOA_GV <> @MAKHOA_MH)
    BEGIN
        RAISERROR(N'Giáo viên chỉ được phân công dạy những môn học thuộc khoa mình phụ trách.', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END
END;