BAITAP TRIGGER.sql

CREATE DATABASE QLBHBT
USE QLBHBT

CREATE TABLE MATHANG
(
MAHANG CHAR(10) PRIMARY KEY NOT NULL,
TENHANG NVARCHAR(30) NOT NULL,
SOLUONG INT NOT NULL
)

CREATE TABLE NHATKYBANHANG
(
STT INT NOT NULL PRIMARY KEY,
NGAY DATETIME NOT NULL,
NGUOIMUA NVARCHAR(30) NOT NULL,
MAHANG CHAR(10) NOT NULL, 
SOLUONG INT NOT NULL,
GIABAN MONEY NOT NULL,
CONSTRAINT FK1 FOREIGN KEY(MAHANG) REFERENCES MATHANG(MAHANG)
)


INSERT INTO MATHANG VALUES
('1', N'KẸO', 100),
('2', N'BÁNH', 200),
('3', N'THUỐC', 100)

INSERT INTO NHATKYBANHANG VALUES
('1', '9/2/1999', 'AB', '2', 230, 50000)

SELECT * FROM MATHANG
SELECT * FROM NHATKYBANHANG

ALTER TRIGGER CAUA ON NHATKYBANHANG
FOR INSERT 
AS
BEGIN
	UPDATE MATHANG
	SET MATHANG.SOLUONG = MATHANG.SOLUONG - inserted.SOLUONG 
	FROM MATHANG INNER JOIN inserted
	ON MATHANG.MAHANG = inserted.MAHANG
END


SELECT * FROM MATHANG
SELECT * FROM NHATKYBANHANG

INSERT INTO NHATKYBANHANG VALUES
('4', '3/23/2001', 'CD', '1', 1, 35000);



CREATE TRIGGER CAUB ON NHATKYBANHANG
FOR UPDATE
AS
BEGIN
	IF UPDATE(SOLUONG)
		UPDATE MATHANG
		SET MATHANG.SOLUONG = MATHANG.SOLUONG - (INSERTED.SOLUONG - DELETED.SOLUONG)
		FROM MATHANG INNER JOIN INSERTED ON MATHANG.MAHANG = inserted.MAHANG
					 INNER JOIN deleted ON DELETED.STT = INSERTED.STT
END

DROP TRIGGER CAUB
select * from mathang
select * from nhatkybanhang
UPDATE nhatkybanhang SET soluong=soluong+20 WHERE
stt=1
select * from mathang
select * from nhatkybanhang


CREATE TRIGGER CAUC ON NHATKYBANHANG
FOR INSERT
AS
BEGIN
	DECLARE @SLCO INT
	DECLARE @SLBAN INT
	DECLARE @MAHANG CHAR(10)
	
	SELECT @MAHANG = MAHANG
	FROM INSERTED

	SELECT  @SLCO = SOLUONG
	FROM MATHANG WHERE MAHANG = @MAHANG

	SELECT @SLBAN = SOLUONG
	FROM NHATKYBANHANG WHERE MAHANG = @MAHANG

	IF (@SLBAN > @SLCO)
		BEGIN
			RAISERROR('KHONG HOP LE', 16, 1)
			ROLLBACK TRANSACTION
		END
	ELSE
		UPDATE MATHANG
		SET MATHANG.SOLUONG = MATHANG.SOLUONG - @SLBAN
		WHERE MAHANG = @MAHANG
END

DROP TRIGGER CAUC
select * from mathang
select * from nhatkybanhang

INSERT INTO NHATKYBANHANG VALUES
('3', '6/21/2001', 'DE', '3', 10, 90000)

select * from mathang
select * from nhatkybanhang


ALTER FUNCTION CAUH(@TENHANG NVARCHAR(30))
RETURNS INT
AS
BEGIN
	DECLARE @TONG MONEY
	SELECT @TONG = SUM(NHATKYBANHANG.SOLUONG * GIABAN)
	FROM NHATKYBANHANG INNER JOIN MATHANG
	ON NHATKYBANHANG.MAHANG = MATHANG.MAHANG
	WHERE TENHANG = @TENHANG
	GROUP BY TENHANG, MATHANG.MAHANG
	RETURN @TONG
END


SELECT * FROM MATHANG
SELECT * FROM NHATKYBANHANG
SELECT DBO.CAUH(N'KẸO')


ALTER TRIGGER CAUD ON NHATKYBANHANG
FOR UPDATE
AS
BEGIN
	DECLARE @TRUOC INT
	DECLARE @SAU INT
	DECLARE @MAHANG CHAR(10)
	IF (SELECT COUNT(*) FROM INSERTED) > 1
		BEGIN
			RAISERROR('LOI', 16, 1)
			ROLLBACK TRANSACTION
		END
	ELSE
		IF (UPDATE(SOLUONG))
			SELECT @MAHANG = MAHANG FROM inserted

			SELECT @SAU = SOLUONG FROM INSERTED
		
			SELECT @TRUOC = SOLUONG FROM deleted
			WHERE MAHANG = @MAHANG

			UPDATE MATHANG
			SET MATHANG.SOLUONG = MATHANG.SOLUONG - (@SAU - @TRUOC)
			WHERE MATHANG.MAHANG = @MAHANG
END

SELECT * FROM MATHANG
SELECT * FROM NHATKYBANHANG

UPDATE NHATKYBANHANG SET SOLUONG = SOLUONG + 20
WHERE NGUOIMUA = 'EF'



CREATE TRIGGER CAUE ON NHATKYBANHANG
FOR DELETE
AS
BEGIN
	DECLARE @MAHANG CHAR(10) 
	DECLARE @SLXOA INT 
	IF (SELECT COUNT(*) FROM DELETED) > 1
	BEGIN
		RAISERROR('LOI XOA BAN GHI', 16, 1)
		ROLLBACK TRANSACTION
	END
	ELSE
		SELECT @MAHANG = MAHANG FROM DELETED
		SELECT @SLXOA = SOLUONG FROM DELETED

		UPDATE MATHANG
		SET SOLUONG = SOLUONG + @SLXOA
		WHERE MAHANG = @MAHANG
END


SELECT * FROM MATHANG
SELECT * FROM NHATKYBANHANG

DELETE NHATKYBANHANG 
WHERE NGUOIMUA = 'EF'