Exc-Midterm-DBMS.sql

CREATE TABLE SanPham(
  MaSP int primary key not null, 
  TenSP varchar(50), 
  SoLuongTon int, 
  MaLoai varchar(50)
)

CREATE TABLE NhanVien(
  MaNV int primary key not null, 
  HoTen varchar(50), 
  NgaySinh date, 
  Phai varchar(50)
)

CREATE TABLE PhieuXuat(
  MaPX int primary key not null, 
  NgayLap date, 
  MaNV int foreign key references NhanVien(MaNV)
)

CREATE TABLE ChiTietXuat(
  MaPX int foreign key references PhieuXuat(MaPX), 
  MaSP int foreign key references SanPham(MaSP), 
  SoLuong int
)

CREATE TABLE Loai(
  MaLoai int not null primary key,
  SoLuong int
)

---------------------

CREATE PROCEDURE P1
  @masp int,
  @sum int out
AS BEGIN 
  SELECT @sum = SUM(SoLuong) 
  FROM PhieuXuat px, ChiTietXuat ctpx
  WHERE 
    px.MaPX = ctpx.MaPX AND
    MaSP = @masp AND 
    px.NgayLap BETWEEN '01/01/2011' AND OEMonth('01/04/2011');
    
  IF (@sum IS NULL)
    @sum = 0;
END

---------------------
CREATE PROCEDURE P2
  @manv int
RETURNS @sanpham TABLE( MASP INT NOT NULL, TenSP varchar(50) NOT NULL )
AS BEGIN
  INSERT INTO @sanpham
  SELECT (MaSP, TenSP) 
    FROM SanPham sp, PhieuXuat px, ChiTietPhieuXuat ct 
    WHERE
        sp.MaSP = ct.MaSp AND
        px.MaPX = ct.MaPX AND 
        px.MaNV = @manv AND 
        px.NgayLap BETWEEN '01/01/2011' AND OEMonth('01/04/2011');
END

---------------------
CREATE PROCEDURE P3 (
  @t1 datetime, 
  @t2 datetime
)
RETURNS TABLE 
AS BEGIN
  SELECT * FROM PhieuXuat 
  WHERE NgayLap BETWEEN @t1 AND @t2
END

EXEC P3 '01/01/2011' '01/05/2011'

------------------
CREATE TRIGGER T1 
ON PhieuXuat 
FOR INSERT, UPDATE
AS BEGIN
    DECLARE @manv int;
    DECLARE @date datetime;
    
    SELECT @manv = MaNV, @date = NgayLap  FROM INSERTED;
    
    IF (SELECT COUNT(*) FROM PhieuXuat WHERE MaNV = @manv AND NgayLap = @date) > 10
    BEGIN
        RAISERROR ("Must me .....", 17, 0);
        ROLLBACK TRAN;
    END
END


---------------------------
CREATE TRIGGER T2
ON ChitietPhieuXuat
FOR INSERT, UPDATE
AS BEGIN
    DECLARE @MaSP int, @sl int, @slton int;
    SELECT @MaSP = MaSP, @sl = SoLuong FROM INSERTED;
    SELECT @slton = SoLuongTon FROM SanPham Where MaSP = @maSp;
    
    IF (@sl > @slton) 
    BEGIN
        RAISERROR('...........', 17, 0);
        ROLLBACK TRANSACTION;
    END
END