请求创建SQL触发器：删除客户时同步删除关联从属表记录

需求说明 #

删除Customers表中的客户记录时，自动同步删除所有与该客户关联的从属表（Orders、OrderedGoods）记录。

原触发器代码 #

CREATE TRIGGER DeleteKlient  ON  Сustomers INSTEAD OF DELETE
AS
IF EXISTS(SELECT * FROM deleted)
BEGIN
    SET NOCOUNT ON;

    DELETE Z
    FROM deleted d
    JOIN dbo.Orders Z ON d.CustomersID IN(Z.[CustomersID]);

    DELETE dbo.Customers
    FROM deleted
    WHERE deleted.CustomersID=Customers.CustomersID;
END;

数据库表结构（中文翻译及修正） #

Customers表（客户表） #

CREATE TABLE [dbo].[Customers](
    [CustomersID] [int] IDENTITY(1,1) NOT NULL, -- 客户ID（自增主键）
    [Customer] [nvarchar](50) NOT NULL, -- 客户名称
    [Phone] [dbo].[phone] NULL, -- 联系电话（自定义数据类型）
    [organizationid] [int] NULL, -- 组织ID
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
( [CustomersID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Orders表（订单表） #

CREATE TABLE [dbo].[Orders](
    [OrderID] [int] IDENTITY(1,1) NOT NULL, -- 订单ID（自增主键）
    [CustomersID] [int] NOT NULL, -- 关联客户ID
    [Numbernaklad] [nvarchar](50) NULL, -- 运单编号
    [DateOrders] [date] NOT NULL, -- 下单日期
    [Sum] [float] NOT NULL, -- 订单金额
    [State] [nvarchar](50) NOT NULL, -- 订单状态
    [Dateotgr] [date] NULL, -- 发货日期
    [Prim] [text] NULL, -- 备注
CONSTRAINT [PK__Orders__D9B7FE45369C06AC] PRIMARY KEY CLUSTERED 
(
    [OrderID] ASC -- 修正原代码拼写错误：原字段名为[ЗаказID]，改为[OrderID]
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_Customers] FOREIGN KEY([CustomersID])
REFERENCES [dbo].[Customers] ([CustomersID]) -- 修正原代码错误：原引用表为[Customer]，改为[Customers]
GO

ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Customers] -- 修正原代码错误：原表名为[Order]，改为[Orders]
GO

OrderedGoods表（订单项表） #

CREATE TABLE [dbo].[Ordered Goods](
    [OrdersID] [int] NOT NULL, -- 关联订单ID
    [WarehouseID] [int] NOT NULL, -- 关联仓库商品ID
    [Count] [real] NOT NULL, -- 商品数量
    [Price] [real] NOT NULL, -- 商品单价
    [Sale] [int] NULL -- 折扣比例
) ON [PRIMARY]

Warehouse表（仓库表） #

CREATE TABLE [dbo].[Warehouse](
    [WarehouseID] [int] NOT NULL, -- 仓库商品ID（主键）
    [ProductCode] [int] NOT NULL, -- 关联商品编码
    [Specific] [nvarchar](50) NOT NULL, -- 商品规格
    [Price] [real] NOT NULL, -- 仓库商品单价
    [Ostatok] [int] NOT NULL, -- 库存数量
    [Adres] [int] NOT NULL, -- 仓库地址ID
 CONSTRAINT [PK_Склад] PRIMARY KEY CLUSTERED 
( [WarehouseID] ASC ) -- 补充原代码缺失的主键列定义
) ON [PRIMARY]

ProductCatalog表（商品目录表） #

CREATE TABLE [dbo].[ProductCatalog](
    [ProductCode] [int] IDENTITY(1,1) NOT NULL, -- 商品编码（自增主键）
    [Name] [nvarchar](50) NOT NULL, -- 商品名称
 CONSTRAINT [PK__КаталогТ__02FE99B3065CA5D4] PRIMARY KEY CLUSTERED
( [ProductCode] ASC ) -- 补充原代码缺失的主键列定义
) ON [PRIMARY]

原触发器存在的问题 #

• 关联条件写法冗余：d.CustomersID IN(Z.[CustomersID])等价于d.CustomersID = Z.CustomersID，前者写法冗余，且可能影响查询性能。
• 未处理从属订单项：原触发器仅删除Orders表记录，但OrderedGoods作为Orders的直接从属表，未同步删除会产生无关联的孤立数据。
• 表名/字段拼写错误：原Orders表的主键字段、外键引用表名存在拼写错误，需先修正，否则触发器执行会失败。

修正后的触发器代码 #

CREATE TRIGGER DeleteCustomerCascade
ON dbo.Customers
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;

    -- 1. 级联删除关联的订单项
    DELETE og
    FROM deleted d
    JOIN dbo.Orders o ON d.CustomersID = o.CustomersID
    JOIN dbo.[Ordered Goods] og ON o.OrderID = og.OrdersID;

    -- 2. 删除关联的订单
    DELETE o
    FROM deleted d
    JOIN dbo.Orders o ON d.CustomersID = o.CustomersID;

    -- 3. 删除客户本身
    DELETE c
    FROM dbo.Customers c
    JOIN deleted d ON c.CustomersID = d.CustomersID;
END;

补充说明 #

• 触发器执行前必须先修正Orders表中的拼写错误，否则会出现对象不存在的报错。
• 若不需要额外逻辑，也可直接为Orders表的外键约束添加ON DELETE CASCADE属性，数据库会自动级联删除关联记录，无需编写触发器。

内容的提问来源于stack exchange，提问作者pokelawls