SQL Server 触发器一点点真实应用举例

最新推荐文章于 2023-12-14 12:38:02 发布

原创最新推荐文章于 2023-12-14 12:38:02 发布 · 187 阅读

0 ·

CC 4.0 BY-SA版权

SQL Server 笔记专栏收录该内容

11 篇文章

订阅专栏

本文介绍如何使用SQL触发器实现主从表数据的一致性管理，当删除主表T_B_Dish中的记录时，通过触发器同步删除从表T_B_DISH_AR中相关联的数据。

参考1: https://www.cnblogs.com/karkash/p/12269907.html

参考2: https://blog.youkuaiyun.com/sxrenchao/article/details/42969367

数据库中有两主从表

表1 T_B_DISH：

表2 T_B_DISH_AR:

最先想着单独删除表1的数据, 同时触发也删除表二相关的数据:

delete from T_B_Dish where fDishID=''

参考网上信息编写触发器如下

CREATE TRIGGER tgr_dish on T_B_Dish
FOR DELETE
AS
BEGIN
  DECLARE @fDishID varchar(50)
  SELECT @fDishID= fDishID from deleted
  DELETE T_B_DISH_AR WHERE fDishID= @fDishID
END
GO

也存在一次性删除表1的数据，同时触发删除所有表2的数据:

delete from T_B_Dish

参考网上信息编写触发器如下

create trigger tgr_dish
on T_B_Dish
for delete
as
  declare @fDishID varchar(20);
  declare cur_dish insensitive cursor 
    for select fDishID from deleted
  open cur_dish
  fetch next from cur_dish into @fDishID
    while @@FETCH_STATUS <> -1
    begin
      delete T_B_DISH_AR where fDishID= @fDishID
      fetch next from cur_dish into @fDishID
   END
  close cur_dish
  deallocate cur_dish
GO

满足使用需要，先记录，后期再补充。