触发器学习

--创建销售表
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