SQL中觸發器的使用

創建觸發器 是特殊的存儲過程，自動執行，一般不要有返回值

?

類型：

　　1.后觸發器 （AFTER,FOR）先執行對應語句，后執行觸發器中的語句

　　2.前觸發器 ?并沒有真正的執行觸發語句（insert，update,delete），而是執行觸發后的語句

　　3.行級觸發器 （FOR EACH ROW） 在SQL server 中不存在

?

商品號為1的庫存量：

?

1.后觸發器（實現不同表之間的約束）

?

--實現在銷售量不大于庫存量時，每賣出n件商品，對應商品的庫存要減n，若銷售量大于庫存量，則回滾此次操作
IF EXISTS (SELECT *FROM sysobjects WHERE name='tr_SaleCommodity')DROP TRIGGER tr_SaleCommodity
GO
CREATE TRIGGER tr_SaleCommodity
ON OrderInfo FOR INSERT  --FOR/AFTER為后觸發器
ASBEGINIF EXISTS (SELECT  * FROM inserted I INNER JOIN CommodityInfo C ON I.CommodityId=C.CommodityIdWHERE I.Amount>C.Amount)BEGINROLLBACK  --后觸發器PRINT '商品的銷售量大于商品的庫存量'END    ELSEBEGINUPDATE CommodityInfoSET Amount=Amount-(SELECT Amount FROM inserted)WHERE CommodityId IN(SELECT CommodityId FROM inserted)ENDEND
GO

執行：

INSERT INTO OrderInfo(UserId,CommodityId,Amount,PayMoney,PayWay,OrderTime,Confirm,SendGoods)
VALUES('YOUYOU',1,10,600,'網上銀行','2014-11-11 00:00:00.000',1,1)

結果：

　　注意：1.上一行為銷售記錄，下一行為商品1的信息

　　　　　2.賣出10個，庫存量由48變為38　

　　　　 ? 3.可以看出以上的銷售記錄中的Paymoney是不正確的，它的值應該是Amount*OutPrice=10*300，所以需要前觸發器來約束

?

2.前觸發器（可以實現行級觸發器功能）

?

--實現了日期校驗和支付金額的計算
IF EXISTS(SELECT* FROM sysobjects WHERE name='tr_DateConfim')DROP TRIGGER tr_DateConfim
GO
CREATE TRIGGER tr_DateConfim
ON OrderInfo INSTEAD OF INSERT ,UPDATE
ASBEGINDECLARE @date datetimeSELECT @date=OrderTime FROM insertedIF @date BETWEEN '2012-1-1' AND '2015-1-1'BEGINDECLARE @UserId varchar(20) ,@CommodityId int,@Amount int,@PayMoney money,@PayWay varchar(20),@OrderTime datetime,@Confirm int,@SendGoods intSELECT @UserId=UserId,@CommodityId=CommodityId,@Amount=Amount,@PayWay=PayWay,@OrderTime=OrderTime,@Confirm=Confirm,@SendGoods=SendGoods FROM insertedDECLARE @outPrice moneySELECT @outPrice=OutPrice FROM CommodityInfo WHERE CommodityId=@CommodityIdSET @PayMoney=@outPrice*@AmountPRINT 'inserted 中的數據：'+CONVERT(varchar(20),@UserId)+' '+CONVERT(varchar(20),@CommodityId)+' '+CONVERT(varchar(20),@Amount)+' '+CONVERT(varchar(20),@PayMoney)+' '+CONVERT(varchar(20),@PayWay)+' '+CONVERT(varchar(20),@OrderTime)+' '+CONVERT(varchar(20),@Confirm)+' '+CONVERT(varchar(20),@SendGoods)+' '+CONVERT(varchar(20),@outPrice)INSERT INTO OrderInfo(UserId,CommodityId,Amount,PayMoney,PayWay,OrderTime,Confirm,SendGoods)SELECT UserId,CommodityId,Amount,@PayMoney,PayWay,OrderTime,Confirm,SendGoods FROM insertedENDELSE PRINT '你插入的數據中的時間只能在 2012-1-1 到 2015-1-1 中間'END
GO

執行：

INSERT INTO OrderInfo(UserId,CommodityId,Amount,PayWay,OrderTime,Confirm,SendGoods)
VALUES('YOUYOU',1,5,'網上銀行','2013-1-11',1,1)

　　?注意：這里插入時我并沒有定義PayMoney，PayMoney是通過觸發器來自動計算的

結果：

日期不正確：

?

日期正確：

打印信息對應：@UserId+' '+@CommodityId+' '+@Amount+' '+@PayMoney+' '+@PayWay+' '@OrderTime+' '@Confirm+' '+@SendGoods+' '@outPrice

?

3.行級觸發器（錯誤）

　　

執行結果：

可以看出在SQL server中并不支持行級觸發器