TIGGER.sql

CREATE DATABASE QLSINHVIEN
USE QLSINHVIEN

CREATE TABLE KHOA
(
MAKHOA CHAR(10) PRIMARY KEY NOT NULL,
TENKHOA NVARCHAR(30) NOT NULL,
SODIENTHOAI CHAR(15) NOT NULL
)

CREATE TABLE LOP
(
MALOP CHAR(10) PRIMARY KEY NOT NULL,
TENLOP NVARCHAR(10) NOT NULL,
SISO INT NOT NULL,
MAKHOA CHAR(10),
CONSTRAINT FK1 FOREIGN KEY(MAKHOA) REFERENCES KHOA(MAKHOA)
)

CREATE TABLE SINHVIEN
(
MASV CHAR(10) NOT NULL PRIMARY KEY,
HOTEN NVARCHAR(30) NOT NULL,
GIOITINH CHAR(10) NOT NULL,
NGAYSINH DATETIME NOT NULL,
MALOP CHAR(10) NOT NULL,
CONSTRAINT FK2 FOREIGN KEY(MALOP) REFERENCES LOP(MALOP)
)


INSERT INTO KHOA VALUES
('K1', N'CÔNG NGHỆ', '1233'),
('K2', N'GIÁO DỤC', '324213'),
('K3', N'THỂ DỤC', '213213213')

INSERT INTO LOP VALUES 
('L1', 'LOP1', 25, 'K1'),
('L2', 'LOP2', 30, 'K3'),
('L3', 'LOP3', 35, 'K2')

INSERT INTO SINHVIEN VALUES 
('S1', N'NGÔ BÁ KHÁ', 'NAM', '2/22/2021', 'L1'),
('S2', N'NGÔ BÁ KHÁ 1', N'NỮ', '2/23/2021', 'L2'),
('S3', N'NGÔ BÁ KHÁ 2', 'NAM', '2/25/2021', 'L3'),
('S4', N'NGÔ BÁ KHÁ 3', N'NỮ', '2/27/2021', 'L1'),
('S5', N'NGÔ BÁ KHÁ 4', 'NAM', '2/10/2021', 'L2')

SELECT * FROM KHOA
SELECT * FROM LOP
SELECT * FROM SINHVIEN


CREATE TRIGGER CAUCUOI ON SINHVIEN
FOR UPDATE 
AS
BEGIN
	DECLARE @SISO INT
	DECLARE @MLMOI CHAR(10)
	SELECT @MLMOI = MALOP FROM INSERTED

	DECLARE @MLCU CHAR(10)
	SELECT @MLCU = MALOP FROM DELETED
	IF (SELECT SISO FROM LOP WHERE MALOP = @MLMOI) > 80
		BEGIN
		RAISERROR('LOI', 16, 1)
		ROLLBACK TRANSACTION
		END
	ELSE
		IF (UPDATE(MALOP))
			UPDATE LOP SET SISO = SISO - 1 WHERE MALOP = @MLCU
			UPDATE LOP SET SISO = SISO + 1 WHERE MALOP = @MLMOI
END


SELECT * FROM KHOA
SELECT * FROM LOP
SELECT * FROM SINHVIEN



UPDATE SINHVIEN SET MALOP = 'L3'
WHERE MASV = 'S1'