WEEK 5 · PRACTICE EXERCISES

Exercises & Answer Key

IT004BTTH5Trigger Exercises

The exercise statement is open to read freely. The answer key and detailed explanations are reserved for students currently enrolled in the IT004 practicum class and require an access code from the instructor.

EXERCISE

Trigger Exercises

Week 5 uses TRIGGER to implement business rules that a regular CHECK cannot handle (comparisons across multiple tables, aggregate calculations...), across both shared schemas, Sales Management and Academic Affairs Management. Full question details are in the attached file below.

Full exercise - Week 5 practice (BTTH5) PDF file · Trigger exercises

Download ↓

This exercise uses the shared Sales Management schema (KHACHHANG, NHANVIEN, SANPHAM, HOADON, CTHD) - see the full attributes, sample data, and database script. View schema ›

And the shared Academic Affairs Management schema (HOCVIEN, LOP, KHOA, MONHOC, DIEUKIEN, GIAOVIEN, GIANGDAY, KETQUATHI) - see the full attributes, sample data, and database script. View schema ›

Submission requirement: Save your work in a single script file named <MSSV>_<HoVaTen>_BTTH5.sql (MSSV is your student ID, HoVaTen is your full name).

Sales Management database (questions 1-4)

A member customer's purchase date (NGHD) must be greater than or equal to their membership registration date (NGDK).
An employee's sale date (NGHD) must be greater than or equal to their hire date.
An invoice's total value is the sum of the line totals (quantity * unit price) of the line items belonging to that invoice.
A customer's sales figure is the sum of the invoice totals that member customer has purchased.

Academic Affairs Management database (questions 1-12)

A class's class monitor must be a member of that class.
A department head must be a teacher belonging to the department and hold the degree "TS" or "PTS".
A student may only take an exam for a subject once their class has finished studying that subject.
In each semester of an academic year, a class may study at most 3 subjects.
A class's enrollment count must equal the number of students belonging to that class.
In the relation DIEUKIEN, the values of MAMH and MAMH_TRUOC within the same tuple may not be identical ("A","A"), and there may also not exist both the tuple ("A","B") and the tuple ("B","A").
Teachers with the same degree, academic rank, and salary coefficient must have the same salary.
A student may only retake an exam (attempt >1) if the score from the previous attempt was below 5.
The exam date of a later attempt must be later than the exam date of the previous attempt (for the same student, same subject).
A student may only take an exam for subjects their class has already finished studying.
When assigning a subject to be taught, the prerequisite order between subjects must be respected (a subject may only be taught after its prerequisite subjects have been completed).
A teacher may only be assigned to teach subjects belonging to the department they are responsible for.

ANSWER

View answer

Protected content

Enter the access code to view the Week 5 answer.

Access code provided by the instructor.

Verified - you now have access to the Week 5 answer.

Full answer key for Sales Management (4 questions) and Academic Affairs Management (12 questions). Each question includes an "Impact Scope Table" (which tables could violate the rule, and which Triggers are needed), a main answer using IF EXISTS/NOT EXISTS (correct regardless of how many rows are affected), and an "Alternative approach" using a local variable (only correct when the operation affects exactly 1 row - see theory section 07). Retype each answer into SSMS to remember it better - the Copy button here is purely for convenience.

QUESTION 1 (SALES) · TRIGGER AFTER UPDATE / INSERT

Exercise: A member customer's purchase date (NGHD) must be greater than or equal to their membership registration date (NGDK).

Impact Scope Table

Table	Operation to catch	Why it could violate the rule
`KHACHHANG`	`UPDATE`	Changing `NGDK` to a date later than an invoice that already exists for that customer.
`HOADON`	`INSERT`, `UPDATE`	Adding/updating an invoice with a `NGHD` earlier than the customer's `NGDK`.

Both 2 Triggers on 2 different tables are needed to fully protect the constraint from every direction data can change.

Trigger 1 - ON KHACHHANG AFTER UPDATE

CREATE TRIGGER TRG_KHACHHANG_NGDK
ON KHACHHANG
AFTER UPDATE
AS
BEGIN
    IF EXISTS (
        SELECT *
        FROM inserted i
        JOIN HOADON hd ON hd.MAKH = i.MAKH
        WHERE i.NGDK > hd.NGHD
    )
    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;

Alternative approach (local variable - Only correct when UPDATE affects exactly 1 customer)

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;

SELECT @MAKH = MAKH, @NGDK = NGDK FROM inserted only picks up 1 row - if the UPDATE changes NGDK for several customers at once (e.g. UPDATE KHACHHANG SET NGDK = ... with no WHERE), this only checks 1 arbitrary customer out of the batch, silently skipping the rest.

Trigger 2 - ON HOADON AFTER INSERT, UPDATE

CREATE TRIGGER TRG_HOADON_NGHD_NGDK
ON HOADON
AFTER INSERT, UPDATE
AS
BEGIN
    IF EXISTS (
        SELECT *
        FROM inserted i
        JOIN KHACHHANG kh ON kh.MAKH = i.MAKH
        WHERE i.NGHD < kh.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;

Alternative approach (local variable - Only correct when adding/updating exactly 1 invoice)

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;

Same limitation as Trigger 1 - only safe when INSERT/UPDATE affects exactly 1 invoice. JOIN KHACHHANG (not LEFT JOIN) automatically skips invoices with no member customer attached (MAKH IS NULL) - matching the question's intent, which only constrains "member customers".

WHAT IS IT?

Both Triggers use IF EXISTS to compare the entire inserted set against the other table via JOIN, instead of assigning into a variable and comparing - so they stay correct regardless of whether UPDATE/INSERT affects 1 or many rows.

WHY?

The "NGHD ≥ NGDK" constraint can be broken from 2 directions: changing NGDK (the KHACHHANG side) or adding/updating an invoice (the HOADON side) - the impact scope table above is exactly the step that surfaces this. Writing only 1 Trigger on 1 table would let the constraint slip from the other direction.

QUESTION 2 (SALES) · TRIGGER AFTER UPDATE / INSERT

Exercise: An employee's sale date (NGHD) must be greater than or equal to their hire date.

Impact Scope Table

Table	Operation to catch	Why it could violate the rule
`NHANVIEN`	`UPDATE`	Changing `NGVL` to a date later than an invoice that employee already created.
`HOADON`	`INSERT`, `UPDATE`	Adding/updating an invoice with a `NGHD` earlier than the selling employee's `NGVL`.

Trigger 1 - ON NHANVIEN AFTER UPDATE

CREATE TRIGGER TRG_NHANVIEN_NGVL
ON NHANVIEN
AFTER UPDATE
AS
BEGIN
    IF EXISTS (
        SELECT *
        FROM inserted i
        JOIN HOADON hd ON hd.MANV = i.MANV
        WHERE i.NGVL > hd.NGHD
    )
    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;

Alternative approach (local variable)

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;

Only correct when UPDATE changes NGVL for exactly 1 employee - the exact same limitation noted in Question 1.

Trigger 2 - ON HOADON AFTER INSERT, UPDATE

CREATE TRIGGER TRG_HOADON_NGHD_NGVL
ON HOADON
AFTER INSERT, UPDATE
AS
BEGIN
    IF EXISTS (
        SELECT *
        FROM inserted i
        JOIN NHANVIEN nv ON nv.MANV = i.MANV
        WHERE i.NGHD < nv.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;

Alternative approach (local variable)

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;

Only correct when adding/updating exactly 1 invoice at a time.

WHAT IS IT?

Exact same structure as Question 1, just swapping the target: NHANVIEN.NGVL instead of KHACHHANG.NGDK.

WHY?

Same "2 directions of violation" reasoning as Question 1 - every row in NHANVIEN always has a MANV (unlike HOADON.MAKH which can be NULL), so no extra IS NOT NULL filter is needed on the join. In practice, Trigger 2 from this question and Trigger 2 from Question 1 could be merged into a single Trigger on HOADON checking both conditions - kept separate here per question for clarity when grading.

QUESTION 3 (SALES) · AUTO-UPDATE TRIGGER

Exercise: An invoice's total value is the sum of the line totals (quantity * unit price) of the line items belonging to that invoice.

Impact Scope Table

Table	Operation to catch	Why it could affect the value
`CTHD`	`INSERT`, `UPDATE`, `DELETE`	Adding/updating/deleting a single line item changes the total for the invoice it belongs to.

This is an automatic calculation Trigger (not a validation Trigger) - CTHD doesn't store a unit price itself, so GIA must be pulled from SANPHAM.

CREATE TRIGGER TRG_CTHD_CAPNHAT_TRIGIA
ON CTHD
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    UPDATE hd
    SET TRIGIA = ISNULL((
        SELECT SUM(ct.SL * sp.GIA)
        FROM CTHD ct
        JOIN SANPHAM sp ON sp.MASP = ct.MASP
        WHERE ct.SOHD = hd.SOHD
    ), 0)
    FROM HOADON hd
    WHERE hd.SOHD IN (
        SELECT SOHD FROM inserted
        UNION
        SELECT SOHD FROM deleted
    );
END;

Alternative approach (local variable - Only correct when exactly 1 CTHD row changes)

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;

@SOHD is first pulled from inserted (correct for INSERT/UPDATE); if that's empty (the DELETE case, where inserted has no rows), it falls back to deleted. This covers all 3 operations, but is still only correct when each INSERT/UPDATE/DELETE touches exactly 1 CTHD row - deleting several line items at once (e.g. DELETE FROM CTHD WHERE SOHD = 1001 for an invoice with many products) would only recompute for 1 invoice even if the batch actually spans several invoices.

WHAT IS IT?

UNION combines SOHD from both inserted and deleted into 1 list of invoices to recompute, covering all 3 operations (INSERT/UPDATE/DELETE) in a single Trigger; ISNULL(..., 0) ensures an invoice left with no line items at all gets TRIGIA = 0 instead of NULL.

WHY?

INSERT only has data in inserted, DELETE only in deleted, and UPDATE has both - using UNION across both virtual tables is the only way for 1 shared Trigger to handle every case correctly, while also recomputing every affected invoice if the batch touches CTHD rows belonging to several different invoices at once.

QUESTION 4 (SALES) · AUTO-UPDATE TRIGGER

Exercise: A customer's sales figure is the sum of the invoice totals that member customer has purchased.

Impact Scope Table

Table	Operation to catch	Why it could affect the value
`HOADON`	`INSERT`, `UPDATE`, `DELETE`	Adding/updating/deleting an invoice (or its `TRIGIA` being updated by Question 3's Trigger) changes a customer's total sales figure.

CREATE TRIGGER TRG_HOADON_CAPNHAT_DOANHSO
ON HOADON
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    UPDATE kh
    SET DOANHSO = ISNULL((
        SELECT SUM(hd.TRIGIA)
        FROM HOADON hd
        WHERE hd.MAKH = kh.MAKH
    ), 0)
    FROM KHACHHANG kh
    WHERE kh.MAKH IN (
        SELECT MAKH FROM inserted WHERE MAKH IS NOT NULL
        UNION
        SELECT MAKH FROM deleted WHERE MAKH IS NOT NULL
    );
END;

Alternative approach (local variable - Only correct when exactly 1 invoice changes)

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;

Same structure and same limitation as Question 3 - only correct when each change affects exactly 1 invoice.

WHAT IS IT?

Same structure as Question 3, just switching from computing an invoice's TRIGIA to a customer's DOANHSO; an extra MAKH IS NOT NULL filter is added since walk-in customer invoices have no MAKH.

WHY?

SQL Server allows nested triggers by default: when Question 3's Trigger runs UPDATE HOADON SET TRIGIA = ..., that very UPDATE automatically fires this AFTER UPDATE trigger on HOADON too - meaning a single change to CTHD automatically keeps both TRIGIA and DOANHSO in sync down the chain, with no manual call needed.

QUESTION 1 (ACADEMIC) · TRIGGER AFTER INSERT, UPDATE, DELETE

Exercise: A class's class monitor must be a member of that class.

Impact Scope Table

Table	Operation to catch	Why it could violate the rule
`LOP`	`INSERT`, `UPDATE`	Assigning `TRGLOP` to a student who doesn't belong to that class.
`HOCVIEN`	`DELETE`, `UPDATE`	Deleting the student who is currently a class monitor, or moving that student to a different class.

Trigger 1 - ON LOP AFTER INSERT, UPDATE

CREATE TRIGGER TRG_LOP_KIEMTRA_LOPTRUONG
ON LOP
AFTER INSERT, UPDATE
AS
BEGIN
    IF EXISTS (
        SELECT * FROM inserted i
        WHERE i.TRGLOP IS NOT NULL
          AND NOT EXISTS (
              SELECT * FROM HOCVIEN hv
              WHERE hv.MAHV = i.TRGLOP AND hv.MALOP = i.MALOP
          )
    )
    BEGIN
        RAISERROR(N'Lớp trưởng của một lớp phải là học viên của chính lớp đó.', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END
END;

i.TRGLOP IS NOT NULL allows a class to have no class monitor yet (a NULL value) - this valid case shouldn't be blocked.

Trigger 2 - ON HOCVIEN AFTER DELETE, UPDATE

CREATE TRIGGER TRG_HOCVIEN_BAOVE_LOPTRUONG
ON HOCVIEN
AFTER DELETE, UPDATE
AS
BEGIN
    IF EXISTS (
        SELECT *
        FROM deleted d
        JOIN LOP l ON l.TRGLOP = d.MAHV
        WHERE NOT EXISTS (
            SELECT * FROM inserted i
            WHERE i.MAHV = d.MAHV AND i.MALOP = l.MALOP
        )
    )
    BEGIN
        RAISERROR(N'Học viên đang là lớp trưởng - không thể xóa hoặc chuyển sang lớp khác.', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END
END;

Alternative approach (local variable - Only correct for exactly 1 student, only catches DELETE)

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;

Only handles DELETE for exactly 1 student - it doesn't catch the case of an UPDATE moving a class monitor's MALOP to a different class, and it's incorrect when several students are deleted at once.

WHAT IS IT?

Trigger 1 blocks a bad assignment from the LOP side; Trigger 2 blocks a class monitor from being deleted or "leaving" their class (via a MALOP change) from the HOCVIEN side, using NOT EXISTS to check whether that student (if they still exist after the operation) is still tied to the class they're monitoring.

WHY?

AFTER DELETE, UPDATE are combined on Trigger 2 because the same NOT EXISTS condition correctly handles both situations: on DELETE, inserted is empty so NOT EXISTS is always true (blocked); on UPDATE, it only blocks when the new row no longer matches the class they're monitoring.

QUESTION 2 (ACADEMIC) · TRIGGER AFTER INSERT, UPDATE

Exercise: A department head must be a teacher belonging to the department and hold the degree "TS" or "PTS".

Impact Scope Table

Table	Operation to catch	Why it could violate the rule
`KHOA`	`INSERT`, `UPDATE`	Assigning `TRGKHOA` to a teacher who doesn't belong to the department, or whose degree isn't TS/PTS.
`GIAOVIEN`	`UPDATE`	Changing `MAKHOA` or downgrading `HOCVI` for a teacher who is currently a department head.

Trigger 1 - ON KHOA AFTER INSERT, UPDATE

CREATE TRIGGER TRG_KHOA_KIEMTRA_TRGKHOA
ON KHOA
AFTER INSERT, UPDATE
AS
BEGIN
    IF EXISTS (
        SELECT * FROM inserted i
        WHERE i.TRGKHOA IS NOT NULL
          AND NOT EXISTS (
              SELECT * FROM GIAOVIEN gv
              WHERE gv.MAGV = i.TRGKHOA
                AND gv.MAKHOA = i.MAKHOA
                AND gv.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;

Alternative approach (local variable - Only correct for exactly 1 department)

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;

Trigger 2 - ON GIAOVIEN AFTER UPDATE

CREATE TRIGGER TRG_GIAOVIEN_BAOVE_TRGKHOA
ON GIAOVIEN
AFTER UPDATE
AS
BEGIN
    IF EXISTS (
        SELECT *
        FROM inserted i
        JOIN KHOA k ON k.TRGKHOA = i.MAGV
        WHERE i.MAKHOA <> k.MAKHOA OR i.HOCVI NOT IN (N'TS', N'PTS')
    )
    BEGIN
        RAISERROR(N'Giáo viên đang là trưởng khoa - không thể đổi khoa hoặc hạ học vị xuống dưới TS/PTS.', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END
END;

WHAT IS IT?

Trigger 1 checks right when a department head is assigned/changed; Trigger 2 protects the constraint from the reverse direction - blocking a teacher's record edit that would leave them no longer qualified to head the department they lead.

WHY?

This is the clearest example of why the impact scope table needs to be drawn up before writing a Trigger - looking at the question only from the "check when assigning a department head" angle (Trigger 1) makes it very easy to miss the case where the constraint gets broken from the GIAOVIEN side (Trigger 2), since that table isn't mentioned directly in the question.

QUESTION 3 (ACADEMIC) · TRIGGER AFTER INSERT, UPDATE

Exercise: A student may only take an exam for a subject once their class has finished studying that subject.

Impact Scope Table

Table	Operation to catch	Why it could violate the rule
`KETQUATHI`	`INSERT`, `UPDATE`	Adding/updating an exam result for a subject the student's class hasn't finished studying yet.

"Finished studying" is read as: GIANGDAY has a (class, subject) row whose DENNGAY is no later than the exam date (NGTHI).

CREATE TRIGGER TRG_KETQUATHI_KIEMTRA_DAHOC
ON KETQUATHI
AFTER INSERT, UPDATE
AS
BEGIN
    IF EXISTS (
        SELECT *
        FROM inserted i
        JOIN HOCVIEN hv ON hv.MAHV = i.MAHV
        WHERE NOT EXISTS (
            SELECT * FROM GIANGDAY gd
            WHERE gd.MALOP = hv.MALOP AND gd.MAMH = i.MAMH AND gd.DENNGAY <= i.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;

Alternative approach (local variable - Only correct for exactly 1 exam result)

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;

WHAT IS IT?

The inner NOT EXISTS looks for a GIANGDAY row proving the class already finished the subject (DENNGAY <= NGTHI); if no such row exists for a student currently taking the exam, that's a violation.

WHY?

"Finished studying" can't just check "was this subject ever assigned to the class" - it also needs the timing condition DENNGAY <= NGTHI, otherwise a student could still "take an exam" for a subject that's still being taught.

QUESTION 4 (ACADEMIC) · TRIGGER AFTER INSERT, UPDATE

Exercise: In each semester of an academic year, a class may study at most 3 subjects.

Impact Scope Table

Table	Operation to catch	Why it could violate the rule
`GIANGDAY`	`INSERT`, `UPDATE`	Adding/updating an assignment pushes a class past 3 subjects in the same semester/year.

CREATE TRIGGER TRG_GIANGDAY_TOIDA_3MON
ON GIANGDAY
AFTER INSERT, UPDATE
AS
BEGIN
    IF EXISTS (
        SELECT MALOP, HOCKY, NAM
        FROM GIANGDAY
        WHERE MALOP IN (SELECT MALOP FROM inserted)
        GROUP BY MALOP, HOCKY, NAM
        HAVING COUNT(*) > 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;

Alternative approach (local variable - Only correct for exactly 1 assignment)

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;

WHAT IS IT?

GROUP BY MALOP, HOCKY, NAM with HAVING COUNT(*) > 3 finds any (class, semester, year) group with more than 3 subjects, narrowed first with WHERE MALOP IN (SELECT MALOP FROM inserted) so the whole GIANGDAY table doesn't need to be scanned.

WHY?

No extra HOCKY/NAM filter is needed in the outer WHERE, since GROUP BY already splits the data correctly by (semester, year) - only the classes that were just assigned something new need to be narrowed down.

QUESTION 5 (ACADEMIC) · AUTO-UPDATE TRIGGER

Exercise: A class's enrollment count must equal the number of students belonging to that class.

Impact Scope Table

Table	Operation to catch	Why it could affect the value
`HOCVIEN`	`INSERT`, `UPDATE`, `DELETE`	Adding/removing a student, or changing a student's `MALOP`, all change the enrollment count of the class(es) involved.

CREATE TRIGGER TRG_HOCVIEN_CAPNHAT_SISO
ON HOCVIEN
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    UPDATE l
    SET SISO = (
        SELECT COUNT(*) FROM HOCVIEN hv WHERE hv.MALOP = l.MALOP
    )
    FROM LOP l
    WHERE l.MALOP IN (
        SELECT MALOP FROM inserted
        UNION
        SELECT MALOP FROM deleted
    );
END;

Alternative approach (local variable - Only correct for exactly 1 student)

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;

Misses the old class when UPDATE changes a student's MALOP - this local-variable version only updates the new class's enrollment (taken from inserted), never the old class (which has to come from deleted) that student just left. This is exactly why the IF EXISTS/UNION version above is the safer choice.

WHAT IS IT?

Recomputes SISO with a direct COUNT(*) on HOCVIEN for every affected class, identified via UNION between inserted and deleted - the same technique used in Sales Management Questions 3 and 4.

WHY?

When an UPDATE changes a student's MALOP, both the old and new class need their enrollment recomputed - UNION between inserted (new class) and deleted (old class) guarantees neither one is missed.

QUESTION 6 (ACADEMIC) · TRIGGER AFTER INSERT, UPDATE

Exercise: In the relation DIEUKIEN, the values of MAMH and MAMH_TRUOC within the same tuple may not be identical ("A","A"), and there may also not exist both the tuple ("A","B") and the tuple ("B","A").

Impact Scope Table

Table	Operation to catch	Why it could violate the rule
`DIEUKIEN`	`INSERT`, `UPDATE`	Adding/updating a row that forms an (A,A) pair, or forms the mirrored pair of an existing row.

CREATE TRIGGER TRG_DIEUKIEN_KIEMTRA
ON DIEUKIEN
AFTER INSERT, UPDATE
AS
BEGIN
    IF EXISTS (SELECT * FROM inserted WHERE 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 inserted i
        JOIN DIEUKIEN dk ON dk.MAMH = i.MAMH_TRUOC AND dk.MAMH_TRUOC = i.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;

Alternative approach (local variable - Only correct for exactly 1 row)

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;

WHAT IS IT?

The first IF blocks an (A,A) pair right within inserted; the second IF self-joins DIEUKIEN against itself (via the inserted virtual table) to find a mirrored (B,A) row already present that matches a newly inserted (A,B).

WHY?

These are 2 independent conditions, so they're split into 2 separate IF blocks, each with its own clear error message - combining them into a single IF ... OR ... would make the error message ambiguous about which violation actually occurred.

QUESTION 7 (ACADEMIC) · TRIGGER AFTER INSERT, UPDATE

Exercise: Teachers with the same degree, academic rank, and salary coefficient must have the same salary.

Impact Scope Table

Table	Operation to catch	Why it could violate the rule
`GIAOVIEN`	`INSERT`, `UPDATE`	Adding/updating a teacher with a salary that differs from another teacher sharing the same (degree, rank, coefficient).

CREATE TRIGGER TRG_GIAOVIEN_KIEMTRA_MUCLUONG
ON GIAOVIEN
AFTER INSERT, UPDATE
AS
BEGIN
    IF EXISTS (
        SELECT *
        FROM inserted i
        JOIN GIAOVIEN gv
          ON gv.HOCVI = i.HOCVI
         AND ISNULL(gv.HOCHAM, N'') = ISNULL(i.HOCHAM, N'')
         AND gv.HESO = i.HESO
         AND gv.MAGV <> i.MAGV
        WHERE gv.MUCLUONG <> i.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;

Alternative approach (local variable - Only correct for exactly 1 teacher)

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;

WHAT IS IT?

JOIN GIAOVIEN against itself (via inserted) to match on the 3 criteria HOCVI, HOCHAM, HESO; if another teacher (MAGV <> i.MAGV) matches all 3 but has a different MUCLUONG, that's a violation.

WHY?

HOCHAM can be NULL (a teacher without an academic rank yet) - in SQL, NULL = NULL doesn't evaluate to TRUE, it's unknown, so comparing gv.HOCHAM = i.HOCHAM directly would wrongly treat 2 teachers who both lack an academic rank as "different" and skip the match. Wrapping both sides in ISNULL(..., N'') converts NULL to an empty string before comparing, so 2 NULL values are correctly treated as equal.

QUESTION 8 (ACADEMIC) · TRIGGER AFTER INSERT

Exercise: A student may only retake an exam (attempt >1) if the score from the previous attempt was below 5.

Impact Scope Table

Table	Operation to catch	Why it could violate the rule
`KETQUATHI`	`INSERT`	Adding a retake (`LANTHI > 1`) while the immediately preceding attempt already passed (`DIEM >= 5`).

CREATE TRIGGER TRG_KETQUATHI_KIEMTRA_THILAI
ON KETQUATHI
AFTER INSERT
AS
BEGIN
    IF EXISTS (
        SELECT *
        FROM inserted i
        WHERE i.LANTHI > 1
          AND NOT EXISTS (
              SELECT * FROM KETQUATHI kq
              WHERE kq.MAHV = i.MAHV AND kq.MAMH = i.MAMH
                AND kq.LANTHI = i.LANTHI - 1 AND kq.DIEM < 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;

Alternative approach (local variable - Only correct for exactly 1 exam result)

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;

WHAT IS IT?

Only checks when LANTHI > 1 (the very first attempt has no condition to meet); NOT EXISTS looks for the immediately preceding attempt (LANTHI - 1) with DIEM < 5 - if no such row is found (meaning the previous attempt passed, or there simply is no previous attempt), that's a violation.

WHY?

NOT EXISTS automatically also handles the case where the previous attempt doesn't even exist (e.g. inserting LANTHI = 3 directly while skipping LANTHI = 2) - the local-variable version has to add an explicit @DiemLanTruoc IS NULL check to handle that correctly, showing that NOT EXISTS is both more compact and less prone to gaps.

QUESTION 9 (ACADEMIC) · TRIGGER AFTER INSERT, UPDATE

Exercise: The exam date of a later attempt must be later than the exam date of the previous attempt (for the same student, same subject).

Impact Scope Table

Table	Operation to catch	Why it could violate the rule
`KETQUATHI`	`INSERT`, `UPDATE`	Adding/updating `NGTHI` in a way that breaks the increasing order of exam dates across `LANTHI`.

CREATE TRIGGER TRG_KETQUATHI_KIEMTRA_NGTHI
ON KETQUATHI
AFTER INSERT, UPDATE
AS
BEGIN
    IF EXISTS (
        SELECT *
        FROM inserted i
        JOIN KETQUATHI kq
          ON kq.MAHV = i.MAHV AND kq.MAMH = i.MAMH AND kq.LANTHI <> i.LANTHI
        WHERE (kq.LANTHI < i.LANTHI AND kq.NGTHI >= i.NGTHI)
           OR (kq.LANTHI > i.LANTHI AND kq.NGTHI <= i.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;

Alternative approach (local variable - Only correct for exactly 1 exam result)

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;

WHAT IS IT?

JOINs each row just added/updated with the other attempts of the same student and subject (kq.LANTHI <> i.LANTHI); the 2 conditions in WHERE check both directions - an earlier attempt whose exam date isn't earlier than the current one, or a later attempt whose exam date isn't later.

WHY?

Both directions (earlier attempts and later attempts relative to the row being added/updated) must be checked, because UPDATE could edit the exam date of any attempt, not necessarily the last one - checking only against "the immediately preceding attempt" like Question 8 would miss the case of editing a middle attempt in a way that breaks order with the attempt after it.

QUESTION 10 (ACADEMIC) · SAME CONSTRAINT AS QUESTION 3

Exercise: A student may only take an exam for subjects their class has already finished studying.

This is the same business rule as Question 3 ("A student may only take an exam for a subject once their class has finished studying that subject") - just worded differently. The TRG_KETQUATHI_KIEMTRA_DAHOC Trigger created for Question 3 already fully handles this constraint - no new Trigger needs to be created. Creating a duplicate would just run 2 Triggers on the same event, which isn't wrong but is redundant and harder to maintain.

WHAT IS IT?

No new SQL answer - see the TRG_KETQUATHI_KIEMTRA_DAHOC Trigger from Question 3.

WHY?

Before writing a Trigger, it's worth quickly cross-checking against questions already answered earlier in the same exercise set - spotting that 2 questions share the same constraint avoids duplicate work, and doubles as a useful sanity check that the question wasn't misread.

QUESTION 11 (ACADEMIC) · TRIGGER AFTER INSERT, UPDATE

Exercise: When assigning a subject to be taught, the prerequisite order between subjects must be respected (a subject may only be taught after its prerequisite subjects have been completed).

Impact Scope Table

Table	Operation to catch	Why it could violate the rule
`GIANGDAY`	`INSERT`, `UPDATE`	Assigning subject X to a class that hasn't yet finished the prerequisite(s) of X.

CREATE TRIGGER TRG_GIANGDAY_KIEMTRA_TIENQUYET
ON GIANGDAY
AFTER INSERT, UPDATE
AS
BEGIN
    IF EXISTS (
        SELECT *
        FROM inserted i
        JOIN DIEUKIEN dk ON dk.MAMH = i.MAMH
        WHERE NOT EXISTS (
            SELECT * FROM GIANGDAY gd
            WHERE gd.MALOP = i.MALOP
              AND gd.MAMH = dk.MAMH_TRUOC
              AND gd.DENNGAY <= i.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;

Alternative approach (local variable - Only correct for exactly 1 assignment)

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;

WHAT IS IT?

JOIN DIEUKIEN to pull every prerequisite of the subject just assigned; the inner NOT EXISTS checks whether that class already has a GIANGDAY row showing each prerequisite was finished (DENNGAY <= TUNGAY of the new subject).

WHY?

A subject can have more than 1 prerequisite (e.g. the CSDL subject in the sample data requires both CTRR and CTDLGT) - the class must have finished every one of those prerequisites, not just any one of them, so NOT EXISTS is needed to guarantee none is still missing.

QUESTION 12 (ACADEMIC) · TRIGGER AFTER INSERT, UPDATE

Exercise: A teacher may only be assigned to teach subjects belonging to the department they are responsible for.

Impact Scope Table

Table	Operation to catch	Why it could violate the rule
`GIANGDAY`	`INSERT`, `UPDATE`	Assigning a teacher to a subject that doesn't belong to that teacher's department.

CREATE TRIGGER TRG_GIANGDAY_KIEMTRA_KHOA
ON GIANGDAY
AFTER INSERT, UPDATE
AS
BEGIN
    IF EXISTS (
        SELECT *
        FROM inserted i
        JOIN GIAOVIEN gv ON gv.MAGV = i.MAGV
        JOIN MONHOC mh ON mh.MAMH = i.MAMH
        WHERE gv.MAKHOA <> mh.MAKHOA
    )
    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;

Alternative approach (local variable - Only correct for exactly 1 assignment)

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;

WHAT IS IT?

JOINs both GIAOVIEN (to get the teacher's department) and MONHOC (to get the subject's department) at once, comparing the 2 department codes directly - a mismatch means a violation.

WHY?

This is the simplest constraint in the Academic Affairs Management set - it only needs to match 2 MAKHOA columns from 2 different tables, with no NOT EXISTS or GROUP BY required, so a direct JOIN is the most compact and readable choice.