QLbenhvienTrigger.sql

SELECT * FROM BENHNHAN
SELECT * FROM BENHVIEN
SELECT * FROM KHOAKHAM

ALTER PROC TINHTONG(@MAKHOA CHAR(10), @TONG MONEY OUT)
AS
BEGIN
	IF (NOT EXISTS(SELECT * FROM KHOAKHAM WHERE MAKHOA = @MAKHOA))
		PRINT(@MAKHOA + 'KHONG TON TAI')
	ELSE
		SELECT @TONG = SUM(SONGAYNV) * 80000
		FROM BENHNHAN
		WHERE MAKHOA = @MAKHOA
		GROUP BY MAKHOA
END

DECLARE @TONG1 MONEY

EXEC TINHTONG 'HS', @TONG1 OUT
PRINT @TONG1



ALTER TRIGGER CAU4 ON BENHNHAN
FOR INSERT
AS
BEGIN
	DECLARE @SOBN INT
	SELECT @SOBN = KHOAKHAM.SOBENHNHAN
	FROM KHOAKHAM INNER JOIN INSERTED 
	ON INSERTED.MAKHOA = KHOAKHAM.MAKHOA
	
	IF @SOBN > 200
		BEGIN
			RAISERROR('LOI', 16, 1)
			ROLLBACK TRANSACTION
		END
	ELSE
		UPDATE KHOAKHAM SET SOBENHNHAN = KHOAKHAM.SOBENHNHAN +1
		FROM KHOAKHAM INNER JOIN INSERTED
		ON KHOAKHAM.MAKHOA = inserted.MAKHOA
END



INSERT INTO BENHNHAN VALUES
('BN10', N'TRẦN DẦN1', '1/22/2000', 0, 25, 'TM')

SELECT * FROM BENHNHAN
SELECT * FROM BENHVIEN
SELECT * FROM KHOAKHAM