Trigger的用法及Inserted Deleted的作用

最新推荐文章于 2023-03-11 09:54:00 发布
转载 最新推荐文章于 2023-03-11 09:54:00 发布 · 1.4k 阅读 · 2
文章标签：

#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

 

结果

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 290
触发器语句中使用了两种特殊的表：deleted 表和 inserted 表。Microsoft® SQLServer2000 自动创建和管理这些表。可以使用这两个临时的驻留内存的表测试某些数据修改的效果及设置触发器操作的条件；然而，不能直接对表中的数据进行更改。 inserted 和 deleted 表主要用于触发器中： 扩展表间引用完整性。 在以视图为基础的基...
SQL中Inserted 和Deleted表 以及触发Trigger
dkoq40185的博客
01-19 347
什么是Inserted 和Deleted表 他们有什么用 trigger 的简单实用 1.什么是Inserted 和Deleted表 当插入数据的时候，其实是同时向目的表 和inserted表中插入数据，当没有出错时，向数据库中插入提交，inserted表中有刚插入的信息， 　　insert into student ([name],defaltage...
sqlserver触发器的使用以及inserted和deleted详解
热门推荐
Java领域
03-11 1万+
触发器是在对表进行插入、更新或删除操作时自动执行的特殊存储过程。触发器通常用于强制业务规则，触发器是一种高级约束，可以定义比CHECK约束更为复杂的约束：可以执行复杂的SQL语句（if/while/case）,可以引用其他表中的列。触发器定义在特定的表上，与表相关，自动触发执行，不能直接调用，是一个事务（可回滚）。
TRIGGER 触发器获得insert, delete, update行的信息
weixin_30363981的博客
10-29 256
触发器语句中使用了两种特殊的表：deleted 表和 inserted 表。Deleted 表用于存储 DELETE 和 UPDATE 语句所影响的行的复本。在执行 DELETE 或 UPDATE 语句时，行从触发器表中删除，并传输到 deleted 表中。Deleted 表和触发器表通常没有相同的行。 Inserted 表用于存储 INSERT 和 UPDATE 语句所影响的行的副本。在一个插...
create trigger 教师更新 after update on Laimy_Teachers for each row if update(lmy_Tno) begin update Laimy_Tea_Cour set lmy_Tno=i.lmy_Tno from deleted d,inserted i ,Laimy_Tea_Cour t where t.lmy_Tno=d.lmy_Tno end; begin update Laimy_Tea_Class set lmy_Tno=i.lmy_Tno from deleted d,inserted i,Laimy_Teac_Class c where c.lmy_Tno=d.lmy_Tno end; 语法上哪里有问题
最新发布
07-12
此外，更推荐的做法是使用 `COLUMNS_UPDATED()` 函数或直接查询 `inserted` 和 `deleted` 临时表来判断字段是否被修改 [^1]。 3. **分号结尾** SQL Server 在默认情况下不要求 T-SQL 语句以分号结束，但在某些上...
trigger
06-13
INNER JOIN deleted ON inserted.EmployeeID = deleted.EmployeeID; END; END; ``` 此触发器会在更新 `Employees` 表中的 `Salary` 列时，将旧工资和新工资记录到 `SalaryHistory` 表中[^1]。 ### 注意事项 虽然...
数据库触发器修改示例：ALTER TRIGGER的使用方法
在SQL Server中，触发器在执行时可以访问两个特殊的虚拟表：inserted和deleted。这些表包含触发器触发前后表中的行数据。inserted表包含了INSERT或UPDATE操作后的新行数据，而deleted表包含了DELETE或UPDATE操作前...
trigger for delete insert and update
weixin_34302561的博客
10-10 109
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...
[SQL Server] TRIGGER 触发器获得insert, delete, update行的信息
weixin_33860528的博客
05-31 439
大家都知道SQL Server触发器分为DDL、DML和登录触发器。其中DML触发器对于一个表来说，又可分为Insert、Update、Delete三种触发器。也许触发器的功能作用大家都清楚了，可是大家知道当批量Update或者批量Delete数据的时候，触发器如何工作吗？在这种情况下，触发器不是逐条触发的（也就是说不是删除一条记录的同时，触发器开始工作一次），而是当批量数据操作完成后，只触发...
mysql trigger触发器简述
梁吉林的博客
10-24 1150
简介触发器属于mysql的一个高级特性，它可以算是一个简化版的存储过程。与真正的存储过程不同的是，它不是被call调用的，而是在检测到满足条件的操作后自动执行的，类似于系统监控与告警的关系。mysql的触发器只支持基于行的触发，始终都是针对于1条记录的。mysql的触发器是针对于行的insert\update\delete操作来触发，具体由哪个操作触发由用户的实际定义来决定。但每个表的每一个事件，最
触发器
jc_benben的专栏
06-20 387
MSSQL：解释插入操作（Insert）：Inserted表有数据，Deleted表无数据 删除操作（Delete）：Inserted表无数据，Deleted表有数据 更新操作（Update）：Inserted表有数据（新数据），Deleted表有数据（旧数据） CREATE TRIGGER [dbo].[TR_TEST] ON [dbo].[TEST] FOR INSERT,DEL...
触发器(七)Trigger_Insert_VocDAY
pyy的博客
12-25 279
USE [AirDB_YiZhuang] GO /****** Object:  Trigger [dbo].[Trigger_Insert_VocDAY]    Script Date: 2017/12/25 17:06:59 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
sqlServer 自定义自动编号列，编号自动加1增长(触发器实现)
liufeifeinanfeng的博客
05-11 6385
sqlServer 定义自动增长列除了使用 标识之外，还可以用触发器实现自定义的自动增长列，对用于数据的特殊编码，增加数据库的可读性是非常有意义的。 go --删除已经存在的触发器 if exists(select * from sysobjects where id=object_id(N'generateCode') and objectproperty(id,N'IsTrigger')...
SQL触发器中的deleted表和inserted表
weixin_33985679的博客
03-07 372
判断是插入,更新还是删除 DECLARE @IsInsert bit, @IsUpdate bit, @IsDelete bit IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted) SET @IsInsert = 1 ELSE SET @IsInsert = 0 IF EXISTS(SELECT 1 FRO...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值