Trigger的用法及Inserted Deleted的作用

最新推荐文章于 2019-04-12 15:15:57 发布
最新推荐文章于 2019-04-12 15:15:57 发布
阅读量1.3k 收藏 2
点赞数
分类专栏： Sql Server 专栏 文章标签： insert delete table null 测试 go

Table Name:   TB_1,TB_2   （两个table 结构完全一样）

Create Table TB_1(ID Int, Name_1 varchar(20),Status varchar(20) ,Type Varchar(20))

Create Table TB_2(ID Int, Name_1 varchar(20),Status varchar(20) ,Type Varchar(20))

-- 为Tb_1创建Trigger, 当 TB_1 发生变化时，变化的信息（Insert ,Update,Delete）将保存于 TB_2 中,Update是两条记录，Update 之前和之后 都要保存盐类

Create trigger Trig_t2
On Tb_1
For Insert,Update,Delete
As
declare @id int,
          @name_1 varchar(20),
   @status varchar(20)

If not exists(Select 1 from deleted)   ----Insert
    Begin
     select @id=id, @name_1=name_1,@status=status from inserted
     insert into tb_2(id,name_1,status,type)values(@id,@name_1,@status,'Insert')
    End

If exists(select 1 from inserted) and exists(select 1 from deleted) ---Update
Begin
     /* Update Before */
     select @id=id, @name_1=name_1,@status=status from deleted
     insert into tb_2(id,name_1,status,type)values(@id,@name_1,@status,'UpdateBefor')

     /* Update End */
     select @id=id, @name_1=name_1,@status=status from inserted
     insert into tb_2(id,name_1,status,type)values(@id,@name_1,@status,'UpdateEnd')
End

If not exists(select 1 from inserted) ---Delete
Begin
    
     select @id=id, @name_1=name_1,@status=status from inserted
     insert into tb_2(id,name_1,status,type)values(@id,@name_1,@status,'Delete')
End

----测试----

insert into tb_1(id,name_1,status)values('1','1','1')
select * from tb_1
select * from tb_2

id          name_1               status               type                                              
----------- -------------------- -------------------- --------------------------------------------------
1           1                    1                    NULL

(1 row(s) affected)

id          name_1               status               type                                              
----------- -------------------- -------------------- --------------------------------------------------
1           1                    1                    Insert


-----------测试Update---------------

update tb_1 set name_1='2'where id='1'

select * from tb_1
select * from tb_2

id          name_1               status               type                                              
----------- -------------------- -------------------- --------------------------------------------------
1           2                    1                    NULL

(1 row(s) affected)

id          name_1               status               type                                              
----------- -------------------- -------------------- --------------------------------------------------
1           1                    1                    Insert
1           1                    1                    UpdateBefor
1           2                    1                    UpdateEnd 

 

 

==================================================================================

这是个比较简单的例子，我再写个复杂点的例子，TB1 和TB2 是两个主表，TB3 是从表，其TYPE 字段决定引用哪个表，这里三个表都加了触发器，用来维护三表之间的引用完整性，提示信息业根据记录情况变化：

 

-- 测试表  

CREATE TABLE TB1(

ID INT,

INF1    VARCHAR(10)

)

GO

 

CREATE TABLE TB2(

ID INT,

INF2    VARCHAR(10)

)

GO

 

CREATE TABLE TB3(

ID INT,

INF3    VARCHAR(10),

TYPE    INT,

RID     INT

)

GO

 

-- 触发器

CREATE TRIGGER TR_TB1 ON TB1

FOR DELETE,UPDATE           -- 只需要检查删改操作

AS

SET NOCOUNT ON

DECLARE     @MSG    VARCHAR(200)

 

IF NOT EXISTS (SELECT 1 FROM INSERTED)  -- 删除操作

BEGIN

    SELECT  @MSG=ISNULL(@MSG+',','')+CAST(D.ID AS VARCHAR)+'('+D.INF1+') 被TB3 记录'+CAST(A.ID AS VARCHAR)+'('+A.INF3+') 引用'

    FROM    TB3 A,DELETED D

    WHERE   A.RID=D.ID      -- 删除的纪录被TB3 引用

    AND A.TYPE=1        -- 引用TB1

   

    IF @MSG IS NOT NULL

    BEGIN

        SET @MSG=' 不能删除TB1 记录'+@MSG

        RAISERROR (@MSG, 16, 1)

    ROLLBACK TRANSACTION

        RETURN

    END

END

 

IF EXISTS (SELECT 1 FROM INSERTED)  -- 修改操作

    AND UPDATE(ID)          -- 修改了ID

BEGIN

    SELECT  @MSG=ISNULL(@MSG+',','')+CAST(D.ID AS VARCHAR)+'('+D.INF1+') 的ID 被TB3 记录'+CAST(A.ID AS VARCHAR)+'('+A.INF3+') 引用'

    FROM    TB3 A,DELETED D

    WHERE   A.RID=D.ID      -- 删除的纪录被TB3 引用

    AND A.TYPE=1        -- 引用TB1

   

    IF @MSG IS NOT NULL

    BEGIN

        SET @MSG=' 不能修改TB1 记录'+@MSG

        RAISERROR (@MSG, 16, 1)

    ROLLBACK TRANSACTION

        RETURN

    END

END

   

GO

 

-- 触发器

CREATE TRIGGER TR_TB2 ON TB2

FOR DELETE,UPDATE           -- 只需要检查删改操作

AS

SET NOCOUNT ON

DECLARE     @MSG    VARCHAR(200)

 

IF NOT EXISTS (SELECT 1 FROM INSERTED)  -- 删除操作

BEGIN

    SELECT  @MSG=ISNULL(@MSG+',','')+CAST(D.ID AS VARCHAR)+'('+D.INF2+') 被TB3 记录'+CAST(A.ID AS VARCHAR)+'('+A.INF3+') 引用'

    FROM    TB3 A,DELETED D

    WHERE   A.RID=D.ID      -- 删除的纪录被TB3 引用

    AND A.TYPE=2        -- 引用TB2

   

    IF @MSG IS NOT NULL

    BEGIN

        SET @MSG=' 不能删除TB2 记录'+@MSG

        RAISERROR (@MSG, 16, 1)

    ROLLBACK TRANSACTION

        RETURN

    END

END

 

IF EXISTS (SELECT 1 FROM INSERTED)  -- 修改操作

    AND UPDATE(ID)          -- 修改了ID

BEGIN

    SELECT  @MSG=ISNULL(@MSG+',','')+CAST(D.ID AS VARCHAR)+'('+D.INF2+') 的ID 被TB3 记录'+CAST(A.ID AS VARCHAR)+'('+A.INF3+') 引用'

    FROM    TB3 A,DELETED D

    WHERE   A.RID=D.ID      -- 删除的纪录被TB3 引用

    AND A.TYPE=2        -- 引用TB2

   

    IF @MSG IS NOT NULL

    BEGIN

        SET @MSG=' 不能修改TB2 记录'+@MSG

        RAISERROR (@MSG, 16, 1)

    ROLLBACK TRANSACTION

        RETURN

    END

END

   

GO

 

 

-- 触发器

CREATE TRIGGER TR_TB3 ON TB3

FOR INSERT,UPDATE           -- 需要检查增改操作，不需要检查删除

AS

SET NOCOUNT ON

DECLARE     @MSG    VARCHAR(200)

 

-- 不必分操作类型检测

SELECT  @MSG=ISNULL(@MSG+',','')+CAST(I.ID AS VARCHAR)+'('+I.INF3+')'

FROM    INSERTED I

WHERE   NOT EXISTS (

    SELECT  1

    FROM    TB1 T

    WHERE   I.RID=T.ID     

    AND I.TYPE=1        -- 引用TB1

    )

AND     NOT EXISTS (

    SELECT  1

    FROM    TB2 T

    WHERE   I.RID=T.ID     

    AND I.TYPE=2        -- 引用TB2

    )

 

IF @MSG IS NOT NULL

BEGIN

    SET @MSG=' 不能增加或者修改TB3 记录'+@MSG+', 没有主表记录'

    RAISERROR (@MSG, 16, 1)

     ROLLBACK TRANSACTION

    RETURN

END

   

GO

 

 

-- 测试

-- 准备数据

INSERT TB1 

SELECT  1,'AAA'

UNION ALL SELECT 2,'BBB'

GO

 

INSERT TB2 

SELECT  1,'CCC'

UNION ALL SELECT 3,'DDD'

GO

 

INSERT TB3 

SELECT  1,'EEE',1,1

UNION ALL SELECT 2,'FFF',1,2

UNION ALL SELECT 3,'GGG',2,1

UNION ALL SELECT 4,'HHH',2,3

 

GO

 

-- 结果 到现在没有问题

--TB1 删改(TB2 操作基本相同)

 

DELETE TB1

WHERE ID=1

GO

 

结果

SeekMyself
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SQL Inserted和deleted详解
01-29
在 SQL Server 中，`Inserted` 和 `Deleted` 是两个特殊的表，它们主要用于触发器（Trigger）中。当执行 INSERT、UPDATE 或 DELETE 操作时，系统会自动创建这两个虚拟表，并填充相应的数据。本文将详细介绍这两个表...
sql server TRIGGER与逻辑表INSTERED 和 DELETED
03-21
尽管描述部分没有提供具体内容，但基于标题和标签，我们可以推断出博文将详细探讨SQL Server触发器以及逻辑表INSERTED和DELETED的作用和用法。这篇博文可能还会介绍如何编写触发器代码，并提供一些最佳实践和示例，...
sql trigger中inserted 和deleted
编程资料大全
04-13 226
触发器语句中使用了两种特殊的表：deleted 表和 inserted 表。Microsoft® SQLServer2000 自动创建和管理这些表。可以使用这两个临时的驻留内存的表测试某些数据修改的效果及设置触发器操作的条件；然而，不能直接对表中的数据进行更改。 inserted 和 deleted 表主要用于触发器中： 扩展表间引用完整性。 在以视图为基础的基...
SQL中Inserted 和Deleted表 以及触发Trigger
dkoq40185的博客
01-19 260
什么是Inserted 和Deleted表 他们有什么用 trigger 的简单实用 1.什么是Inserted 和Deleted表 当插入数据的时候，其实是同时向目的表 和inserted表中插入数据，当没有出错时，向数据库中插入提交，inserted表中有刚插入的信息， 　　insert into student ([name],defaltage...
TRIGGER 触发器获得insert, delete, update行的信息
weixin_30363981的博客
10-29 215
触发器语句中使用了两种特殊的表：deleted 表和 inserted 表。Deleted 表用于存储 DELETE 和 UPDATE 语句所影响的行的复本。在执行 DELETE 或 UPDATE 语句时，行从触发器表中删除，并传输到 deleted 表中。Deleted 表和触发器表通常没有相同的行。 Inserted 表用于存储 INSERT 和 UPDATE 语句所影响的行的副本。在一个插...
trigger for delete insert and update
weixin_34302561的博客
10-10 74
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME= 'Audit') CREATE TABLE LogTable ( LogID [int]IDENTITY(1,1) NOT NULL, Type char(1), TableName varchar(128), Primar...
trigger语法经典语法全
05-21
INNER JOIN deleted ON inserted.EmployeeID = deleted.EmployeeID; END END; ``` 在这个例子中，当`Employees`表中的`Salary`列被更新时，触发器`trg_EmployeeSalaryUpdate`会被激活，并记录此次更新前后的工资...
Trigger 笔记
07-01
它的工作方式是先捕获数据的原始状态（在deleted表中），然后捕获更新后的状态（在inserted表中）。如果UPDATE语句涉及到特定列，如job_desc，可以通过IF UPDATE(job_desc)来判断，触发器仅在该列发生变化时执行。...
[SQL Server] TRIGGER 触发器获得insert, delete, update行的信息
weixin_33860528的博客
05-31 338
大家都知道SQL Server触发器分为DDL、DML和登录触发器。其中DML触发器对于一个表来说，又可分为Insert、Update、Delete三种触发器。也许触发器的功能作用大家都清楚了，可是大家知道当批量Update或者批量Delete数据的时候，触发器如何工作吗？在这种情况下，触发器不是逐条触发的（也就是说不是删除一条记录的同时，触发器开始工作一次），而是当批量数据操作完成后，只触发...
创建INSERT、DELETE、UPDATE 触发器示例
热门推荐
王伯仙的博客
02-28 8万+
触发器是基于一个表创建的，但是可以针对多个表进行操作。所以触发器可以用来对表实施复杂的完整性的约束，当触发器所保存的数据发生改变时，触发器所保存的数据发生改变时，触发器被自动激活，从而防止对数据的不正确修改。         下面对每个INSERT、DELETE、UPDATE触发器举一个小例子来加深理解： INSERT触发器：         INSERT触发器就是当对定义触发器的表执行IN
【SQL触发器】Inserted和deleted详解
FBI的博客
05-30 2万+
触发器语句中使用了两种特殊的表：deleted表和inserted表。 Deleted 表用于存储 DELETE 和 UPDATE 语句所影响的行的复本。在执行 DELETE 或 UPDATE 语句时，行从触发器表中删除，并传输到 deleted 表中。Deleted 表和触发器表通常没有相同的行。 Inserted 表用于存储 INSERT 和 UPDATE 语句所影响的行的副
触发器 inserted，deleted用法
weixin_43494908的博客
04-12 2852
一﹕ 触发器是一种特殊的存储过程﹐它不能被显式地调用﹐而是在往表中插入记录﹑更新记录或者删除记录时被自动地激活。所以触发器可以用来实现对表实施复杂的完整性约`束。 二﹕ SQL Server为每个触发器都创建了两个专用表﹕Inserted表和Deleted表。这两个表由系统来维护﹐它们存在于内存中而不是在数据库中。这两个表的结构总是与被该触发器作用的表的结构相同。触发器执行 完成后﹐与该触发器相关...
SQL Server DDL触发器的启用与作用
"本资源主要介绍了如何使DDL触发器重新生效以及触发器的基本概念和用法，特别是DML触发器的创建和执行过程。" 在SQL Server中，DDL（Data Definition Language）触发器是一种特殊类型的存储过程，它们会在特定的...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值