CREATE TABLE NCC (
MaNCC CHAR(10) PRIMARY KEY,
TenNCC NVARCHAR(100),
Diachi NVARCHAR(200),
SDT VARCHAR(15)
);
-- Bảng nhân viên
CREATE TABLE NHANVIEN (
MaNV CHAR(10) PRIMARY KEY,
TenNV NVARCHAR(100),
Chucvu NVARCHAR(50)
);
-- Bảng thuốc
CREATE TABLE THUOC (
Mathuoc CHAR(10) PRIMARY KEY,
Tenthuoc NVARCHAR(100),
DVtinh NVARCHAR(20),
Soluong INT
);
-- Bảng hóa Ä‘Æ¡n nháºp
CREATE TABLE HDNHAP (
MaHDN CHAR(10) PRIMARY KEY,
MaNCC CHAR(10),
MaNV CHAR(10),
Ngaynhap DATE DEFAULT GETDATE(),
FOREIGN KEY (MaNCC) REFERENCES NCC(MaNCC),
FOREIGN KEY (MaNV) REFERENCES NHANVIEN(MaNV)
);
-- Bảng chi tiết hóa Ä‘Æ¡n nháºp
CREATE TABLE CTHDNHAP (
MaHDN CHAR(10),
Mathuoc CHAR(10),
SLnhap INT,
DGnhap MONEY,
PRIMARY KEY (MaHDN, Mathuoc),
FOREIGN KEY (MaHDN) REFERENCES HDNHAP(MaHDN),
FOREIGN KEY (Mathuoc) REFERENCES THUOC(Mathuoc)
);
CREATE TRIGGER Chen_NCC
ON NCC
INSTEAD OF INSERT
AS
BEGIN
IF EXISTS(
SELECT 1
FROM NCC n
JOIN INSERTED i ON n.TenNCC=i.TenNCC AND n.SDT=i.SDT
)
BEGIN
RAISERROR(N'TRUNG!',16,1)
RETURN
END
INSERT INTO NCC(MaNCC,TenNCC,Diachi,SDT)
SELECT MaNCC,TenNCC,Diachi,SDT
FROM INSERTED
END
GO
CREATE FUNCTION Tongnhap(@Mathuoc CHAR(4))
RETURNS MONEY
AS
BEGIN
DECLARE @Tong MONEY
SELECT @Tong=SUM(SLnhap*DGnhap)
FROM CTHDNHAP
WHERE Mathuoc=@Mathuoc
RETURN ISNULL(@Tong,0)
END
Telegram: DB developers channel