DETHISQLDE1.sql

CREATE DATABASE QLBANHANG
USE QLBANHANG

CREATE TABLE CONGTY
(
MACONGTY CHAR(10) NOT NULL PRIMARY KEY, 
TENCONGTY NVARCHAR(30) NOT NULL,
DIACHI NVARCHAR(30) NOT NULL
)

CREATE TABLE SANPHAM
(
MASANPHAM CHAR(10) NOT NULL PRIMARY KEY,
TENSANPHAM NVARCHAR(30) NOT NULL,
SOLUONGCO INT NOT NULL,
GIABAN MONEY NOT NULL
)

CREATE TABLE CUNGUNG
(
MACONGTY CHAR(10) NOT NULL,
MASANPHAM CHAR(10) NOT NULL,
SLCUNGUNG INT NOT NULL,
NGAYCU DATETIME NOT NULL
)

INSERT INTO CONGTY VALUES
('1', 'ABC', N'HÀ NỘI'),
('2', 'BCD', N'HÀ NAM'),
('3', 'T&T', N'HÀ TĨNH')

INSERT INTO SANPHAM VALUES
('S1', N'ĐAU ĐẦU', 30, 30000),
('S2', N'ĐAU HỌNG', 80, 40000),
('S3', N'ĐAU RĂNG', 50, 25000)

INSERT INTO CUNGUNG VALUES
('1', 'S3', 100, '3/22/2021'),
('2', 'S2', 200, '3/23/2021'),
('3', 'S1', 300, '3/24/2021'),
('1', 'S2', 150, '3/25/2021'),
('2', 'S3', 90, '3/26/2021')



SELECT * FROM CONGTY
SELECT * FROM SANPHAM
SELECT * FROM CUNGUNG


CREATE TRIGGER CAU4 ON CUNGUNG
FOR UPDATE
AS
BEGIN
	DECLARE @SLCUMOI INT
	DECLARE @SLCUCU INT
	DECLARE @MASP CHAR(10)

	SELECT @MASP = MASANPHAM FROM inserted
	SELECT @SLCUMOI = SLCUNGUNG FROM INSERTED
	SELECT @SLCUCU = SLCUNGUNG FROM DELETED

	IF (@SLCUMOI - @SLCUCU) > (SELECT SOLUONGCO FROM SANPHAM WHERE MASANPHAM = @MASP)
		BEGIN
		RAISERROR('LOI', 16, 1)
		ROLLBACK TRANSACTION
		END
	ELSE
		IF (UPDATE(SLCUNGUNG))
			UPDATE SANPHAM SET SOLUONGCO = @SLCUMOI - @SLCUCU
			WHERE MASANPHAM = @MASP
END

SELECT * FROM SANPHAM
SELECT * FROM CUNGUNG

UPDATE CUNGUNG SET SLCUNGUNG = 220
WHERE MASANPHAM = 'S3'

UPDATE CUNGUNG
SET MACONGTY = '3'
WHERE NGAYCU = '3/25/2021'