CÂU 11

CREATE PROC BO_SUNG_MH

(

@MACONGTY NVARCHAR(10),

@TENCONGTY NVARCHAR(100),

@TENGIAODICH NVARCHAR (100),

@DIACHI NVARCHAR(200),

@DIENTHOAI CHAR(13)           )

AS

BEGIN

      INSERT INTO MATHANG

      VALUES (@MACONGTY,@TENCONGTY,@TENGIAODICH,@DIACHI,@DIENTHOAI)

END

BO_SUNG_MH 'CONGTY1','CONG TY SO 1',

'GIAO DICH CT1','HAI DUONG','023102001251'

CÂU 12

CREATE PROC THONG_KE_MH1

(

@MAHANG NVARCHAR(10),

@DONDATHANG NVARCHAR (20),

@GIABAN FLOAT,

@SOLUONG INT

)

AS

DECLARE @MAHANG_FIND NVARCHAR(10)

BEGIN

      SELECT MAHANG,COUNT(DONDATHANG) AS "SO HANG BAN DUOC"

      FROM CHITIETDATHANG

      WHERE MAHANG=@MAHANG_FIND

      GROUP BY MAHANG              

END

CÂU 13

CREATE TRIGGER CHITIETDATHANG_INSERT1

ON CHITIETDATHANG

FOR INSERT

AS

DECLARE @SOLUONG_CO INT

DECLARE @SOLUONG_BAN INT

SELECT @SOLUONG_CO = SOLUONG

      FROM CHITIETDATHANG

SELECT @SOLUONG_BAN = SOLUONG

FROM INSERTED

IF @SOLUONG_BAN<=@SOLUONG_CO

      UPDATE CHITIETDATHANG

      SET CHITIETDATHANG.SOLUONG=@SOLUONG_CO-@SOLUONG_BAN

      FROM CHITIETDATHANG INNER JOIN INSERTED

            ON CHITIETDATHANG.MAHANG=INSERTED.MAHANG

ELSE ROLLBACK TRANSACTION