--创建销售表

IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'U' AND NAME ='SALES')

DROP TABLE SALES

CREATE TABLE SALES --销售表

(

  SAID  INT IDENTITY(1,1) NOT NULL,

  BREED VARCHAR(40) NOT NULL,

  PURCHASE VARCHAR(40) NULL,

  QUANTITY INT NULL,

  PRICE MONEY NULL,

  AMOUNT MONEY NULL

)

SELECT * FROM SALES

--创建库存表

IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'U' AND NAME = 'STOCKINDTL')

DROP TABLE STOCKINDTL

CREATE TABLE STOCKINDTL

(

  BREED VARCHAR(40) PRIMARY KEY NOT NULL,

  QUANTITY INT NULL,

  PRICE MONEY NULL,

  AMOUNT MONEY NULL

)

SELECT * FROM STOCKINDTL

--当向库存表插入数据的时候自动算出金额

IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'TR' AND NAME = 'INSERT_STOCKINDTL' )

DROP TRIGGER INSERT_STOCKINDTL

CREATE TRIGGER INSERT_STOCKINDTL

ON STOCKINDTL

FOR INSERT

AS

BEGIN TRANSACTION

UPDATE STOCKINDTL SET AMOUNT = I.QUANTITY * I.PRICE FROM STOCKINDTL S,INSERTED I WHERE S.BREED = I.BREED

COMMIT TRANSACTION

INSERT INTO STOCKINDTL(BREED,QUANTITY,PRICE) VALUES('金威啤酒',10000,3.8)

INSERT INTO STOCKINDTL(BREED,QUANTITY,PRICE) VALUES('青岛啤酒',10000,3.8)

 

--创建触发器，当向销售表插入数据的时候，同时库存表要做相应的减少

IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'TR' AND NAME = 'INSERT_SALES')

DROP TRIGGER INSERT_SALES

GO

CREATE TRIGGER INSERT_SALES

ON SALES

FOR INSERT

AS

BEGIN TRANSACTION

   --检查数据的合法性：销售的卷烟是否有库存，或者库存是否大于零

   IF NOT EXISTS (SELECT QUANTITY FROM STOCKINDTL WHERE BREED IN (SELECT BREED FROM INSERTED))

   BEGIN

     RAISERROR('错识！,该商品不存在库存！不能销售',16,1)

     ROLLBACK

     RETURN

   END

   IF EXISTS (SELECT QUANTITY FROM STOCKINDTL WHERE BREED IN (SELECT BREED FROM INSERTED) AND QUANTITY <=0)

   BEGIN

     RAISERROR('错识！,该卷烟库存小于等于0，不能销售',16,1)

     ROLLBACK

     RETURN

   END

   DECLARE @BREED VARCHAR(40)

   SELECT  @BREED = BREED FROM INSERTED

   DECLARE @SAQUANTITY INT  -- 要销售的数量

   SELECT  @SAQUANTITY = QUANTITY FROM INSERTED

   DECLARE @STQUANTITY INT  -- 库存的数量

   SELECT  @STQUANTITY = QUANTITY FROM STOCKINDTL

   -- 检查销售数量大于库存数量

   IF(@SAQUANTITY > @STQUANTITY)

   BEGIN

   RAISERROR('错识！,销售数量不能大于库存数量',16,1)

   ROLLBACK

   RETURN

   END

 

   --对合法的数据进行处理

   UPDATE SALES SET AMOUNT = QUANTITY * PRICE  WHERE BREED IN (SELECT BREED FROM INSERTED)

   UPDATE STOCKINDTL SET QUANTITY = QUANTITY - @SAQUANTITY, AMOUNT = ( QUANTITY - @SAQUANTITY) * PRICE WHERE BREED = @BREED

COMMIT TRANSACTION

GO

 

--测试向销售表插入一条数据

INSERT INTO SALES(BREED,PURCHASE,QUANTITY,PRICE) VALUES('青岛啤酒','LIDAOHUA',5,3.8)       --符合业务逻辑的数量

INSERT INTO SALES(BREED,PURCHASE,QUANTITY,PRICE) VALUES('金威啤酒','LIDAOHUA',5,3.8)

INSERT INTO SALES(BREED,PURCHASE,QUANTITY,PRICE) VALUES('白沙烟','LIDAOHUA',25,3.8)       --不存在这个商品的情况

INSERT INTO SALES(BREED,PURCHASE,QUANTITY,PRICE) VALUES('金威啤酒','LIDAOHUA',10000,3.8)  --销售数量大于库存数量

 

--查询数据

SELECT * FROM SALES

SELECT * FROM STOCKINDTL

--清空表的数据

TRUNCATE TABLE SALES

TRUNCATE TABLE STOCKINDTL