SQLSERVER触发器触发INSERT,UPDATE,DELETE三种状态

SQLSERVER触发器触发INSERT,UPDATE,DELETE三种状态

来源：https://blog.csdn.net/anbaojian/article/details/86488353

一个触发器内三种INSERT,UPDATE,DELETE状态

CREATE   TRIGGER   tr_T_A   ON     T_A   for   INSERT,UPDATE,DELETE         

  如IF   exists   (select   *   from   inserted)   and   not   exists   (select   *   from   deleted)   则为   INSERT   

  如IF   exists(select   *   from   inserted   )   and   exists   (select   *   from   deleted)   则为   UPDATE   

  如IF   exists   (select   *   from   deleted)   and   not   exists   (select   *   from   inserted)则为   DELETE  

 

插入操作（Insert）：Inserted表有数据，Deleted表无数据 

删除操作（Delete）：Inserted表无数据，Deleted表有数据 

更新操作（Update）：Inserted表有数据（新数据），Deleted表有数据（旧数据）

 

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

SQL中触发器的使用

原文地址：https://www.cnblogs.com/feiquan/archive/2018/04/01/8685722.html

 

创建触发器 是特殊的存储过程，自动执行，一般不要有返回值

 

类型：

　　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为后触发器
AS
    BEGIN
        IF EXISTS (
            SELECT  * FROM inserted I INNER JOIN CommodityInfo C ON I.CommodityId=C.CommodityId
            WHERE I.Amount>C.Amount
        )
            BEGIN
                ROLLBACK  --后触发器
                PRINT '商品的销售量大于商品的库存量'
            END    
        ELSE
            BEGIN
                UPDATE CommodityInfo
                SET Amount=Amount-(SELECT Amount FROM inserted)
                WHERE CommodityId IN
                (
                    SELECT CommodityId FROM inserted
                )
            END
    END
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
AS
    BEGIN
        DECLARE @date datetime
        SELECT @date=OrderTime FROM inserted
        IF @date BETWEEN '2012-1-1' AND '2015-1-1'
            BEGIN
                DECLARE @UserId varchar(20) ,@CommodityId int,@Amount int,@PayMoney money,@PayWay varchar(20),@OrderTime datetime,@Confirm int,@SendGoods int
                SELECT @UserId=UserId,@CommodityId=CommodityId,@Amount=Amount,@PayWay=PayWay,@OrderTime=OrderTime,@Confirm=Confirm,@SendGoods=SendGoods FROM inserted
                DECLARE @outPrice money
                SELECT @outPrice=OutPrice FROM CommodityInfo WHERE CommodityId=@CommodityId
                SET @PayMoney=@outPrice*@Amount
                PRINT '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 inserted
            END
        ELSE 
            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中并不支持行级触发器

如果是此文是转载文章，本人会附上转载链接，此篇文章的版权归原创作者所属，如果侵权请与我联系，我会删除此文。

若没有标明转载链接，此篇文章属于本人的原创文章，其版权所属：
作者：feiquan
出处：http://www.cnblogs.com/feiquan/
版权声明：本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
大家写文都不容易，请尊重劳动成果~ 这里谢谢大家啦(*/ω＼*)