SQL Server 如何创建触发器实现一张表数据更新时,同步增加、删除、修改另一张表的数据

一、触发器知识点摘要: 

实际应用中,我们经常会遇到在对SQL Server的某一张表进行增加、删除、修改操作时,需同步更新同一数据库或不同数据库的另一张表,此时我们可以使用SQL Server 的表触发器来实现。

使用触发器时,我们会用到触发器的两个临时虚拟表:Deleted表、Inserted表,它们用于存储【创建触发器表】的记录变动信息,即发生触发事件时对应的【一条旧记录】和【一条新记录】,各触发事件对应表数据如下:

触发事件虚拟表Inserted虚拟表Deleted
新增记录存放新增的记录
修改记录存放用来更新的新记录(新记录)存放更新前的记录(旧记录)
删除记录存放被删除的记录

举例说明: 

修改记录时,触发Update事件,Update的过程可以看作为:复制旧记录到Deleted表、生成新的记录到Inserted表,然后删除表记录并写入新纪录。

二、如何创建触发器: 

以SQL Server2008R2为基础,在【数据库A.dbo.表TA】表发生变化时,同步触发【数据库B.dbo.表TB】例,两张表结构如下图:

举例数据库表结构


 

创建触发器步骤如下: 

1、打开SQL Server企业管理器【SQL Server Management Studio】-展开【数据库】至需创建触发器的表,如下图示例中的【数据库A】.【表TA】-右键单击【触发器】-【新建触发器】,如下图:

创建触发器

2、右侧查询分析器窗口输入以下示例代码,创建UPDATE、INSERT事件触发器:

USE [A]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[Empty_Update]	--首次创建CREATE,后续修改ALTER:更新、新增触发器
   ON  [dbo].[TA]	----在TA表中创建触发器
   AFTER INSERT,UPDATE	--更新、新增事件触发
AS	--执行SQL语句


DECLARE @PerID AS int	--定义变量
SELECT @PerID=id FROM inserted	--变量赋值:临时表inserted中存放的新id

IF EXISTS(SELECT id_b FROM B..TB WHERE B..TB.id_b=@PerID)	--如果记录已存在,执行UPDATE操作
BEGIN

	UPDATE B..TB SET
		B..TB.code_b=code,
		B..TB.name_b=name
	FROM inserted WHERE B..TB.id_b=@PerID

END

ELSE IF @PerID IS NOT NULL	--如果记录不存在且id不为空,执行INSERT操作
BEGIN

	SET IDENTITY_INSERT B..TB ON	--允许主键插入显式值

	INSERT INTO B..TB(
	id_b,code_b,name_b
	)SELECT 
	id,code,name 
	FROM inserted

	SET IDENTITY_INSERT B..TB OFF	--禁止主键插入显式值

END

注:首次创建触发器时必须使用CREATE命令,以后如需对触发器进行修改,需将 CREATE 修改为:ALTER。 

3、触发器编写完成后,点击【√分析】,语法无误后再点击【执行】,如下图:

SQL分析与执行

4、成功【执行】后,将在[a].[dbo].[TA]触发器下CREATE一个名称为[Empty_Update]的触发器,如下图:

创建触发器示例

5、重复步骤1,在右侧查询分析器窗口输入以下示例代码,创建DELETE事件触发器:

USE [A]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[Empty_Delete]	--首次创建CREATE,后续修改ALTER:删除触发器
   ON  [dbo].[TA]	----在TA表中创建触发器
   AFTER DELETE	--删除事件触发
AS	--执行SQL语句

DECLARE @DelID AS int	--定义变量
SELECT @DelID=id FROM Deleted	--变量赋值:临时表Deleted中存放的被删除id

IF @DelID IS NOT NULL	--如果删除id不为空,执行DELETE操作

BEGIN

	DELETE FROM B..TB WHERE B..TB.id_b =@DelID

END

6、成功执行后,将在[a].[dbo].[TA]触发器下再次CREATE一个名称为[Empty_Delete]的触发器。

注意事项:

在Microsoft SQL Server Management Studio 企业管理器中,通过【导入数据】向导从其他表或EXCEL中导入数据时,触发器不触发。

扩展阅读:

触发器的工作原理:

    触发器是一种特殊类型的存储过程,它与表紧密联系在一起,在对表进行插入、删除和更新时,如该表(也称触发器表)有相应操作类型的触发器,则触发器便会自动触发执行。触发器分为INSERT触发器、DELETE触发器和UPDATE触发器3类。当向触发器表中插入数据时,INSERT触发器将触发执行,新的记录会增加到触发器表和inseted表中;当删除触发器表中的数据时,DELETE触发器将触发执行,被删除的记录会存放到deleted表中;当更新触发器表中的数据时,相当于插入一条新记录和删除一条旧记录,此时UPDATE触发器将触发执行,表中原有的记录存放到deleted表中,修改后的记录插入到inserted表中。其中inserted表和deleted表是两个逻辑表,由系统来维护,不允许用户直接对这两个表进行修改。它们存放于内存中,不存放在数据库中。这两个表的结构总是与被该触发器作用的表的结构相同。触发器工作完成后,与该触发器相关的这两个表也会被删除。deleted表用于存储SQL语言中DELETE和UPDATE语句所影响的行的复本。在执行DELETE或 UPDATE语句时,行从触发器表中删除,并传输到 deleted 表中。deleted 表和触发器表通常没有相同的行;inserted 表用于存储 SQL语言中INSERT 和 UPDATE 语句所影响的行的副本。在一个插入或更新事务处理中,新建行被同时添加到 inserted 表和触发器表中。inserted 表中的行是触发器表中新行的副本。

  • 12
    点赞
  • 57
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
SQL Server 中,可以使用触发器实现同步更新触发器是一种特殊的存储过程,它会在一个插入更新删除数据自动执行。 下面是一个简单的例子,演示如何使用触发器同步更新两个数据: ``` CREATE TRIGGER trgSyncUpdate ON table1 AFTER INSERT, UPDATE, DELETE AS BEGIN IF @@ROWCOUNT = 0 RETURN SET NOCOUNT ON; IF EXISTS (SELECT * FROM inserted) BEGIN UPDATE table2 SET column1 = i.column1, column2 = i.column2 FROM table2 t INNER JOIN inserted i ON t.id = i.id END IF EXISTS (SELECT * FROM deleted) BEGIN DELETE FROM table2 WHERE id IN (SELECT id FROM deleted) END END ``` 在这个例子中,我们创建了一个名为 `trgSyncUpdate` 的触发器,它会在 `table1` 插入更新删除数据自动执行。在触发器中,我们首先检查是否有数据插入更新删除。如果没有,我们就返回。然后,我们使用 `IF EXISTS` 语句检查是否有数据插入更新。如果有,我们就使用 `UPDATE` 语句将 `table2` 中对应的数据更新。最后,我们使用 `IF EXISTS` 语句检查是否有数据删除。如果有,我们就使用 `DELETE` 语句将 `table2` 中对应的数据删除。 需要注意的是,在使用触发器,我们需要小心避免死循环。如果两个互相触发对方的触发器,就会导致死循环。为了避免这种情况,我们可以使用 `DISABLE TRIGGER` 和 `ENABLE TRIGGER` 语句来暂禁用触发器

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值