22,207
社区成员
发帖
与我相关
我的任务
分享
create trigger tri_a ON tb_level2
FOR delete
AS
begin
delete tb_level3 where level2Id in (SELECT level2Id FROM DELETED)
end
go
--> 生成测试数据表: [tb_level1]
IF OBJECT_ID('[tb_level1]') IS NOT NULL
DROP TABLE [tb_level1]
GO
CREATE TABLE [tb_level1] ([level1Id] [int] PRIMARY KEY,[name] [nvarchar](10))
INSERT INTO [tb_level1]
SELECT '1','a' UNION ALL
SELECT '2','b'
--> 生成测试数据表: [tb_level2]
IF OBJECT_ID('[tb_level2]') IS NOT NULL
DROP TABLE [tb_level2]
GO
CREATE TABLE [tb_level2] ([level2Id] [int] PRIMARY KEY,[name] [nvarchar](10),[level1Id] [int] REFERENCES [tb_level1]([level1Id]) ON DELETE CASCADE)
INSERT INTO [tb_level2]
SELECT '1','aa','1' UNION ALL
SELECT '2','ab','1' UNION ALL
SELECT '3','ba','2' UNION ALL
SELECT '4','bb','2'
--> 生成测试数据表: [tb_level3]
IF OBJECT_ID('[tb_level3]') IS NOT NULL
DROP TABLE [tb_level3]
GO
CREATE TABLE [tb_level3] ([level3Id] [int],[name] [nvarchar](10),[level2Id] [int] REFERENCES [tb_level2]([level2Id]) ON DELETE CASCADE)
INSERT INTO [tb_level3]
SELECT '1','aaa','1' UNION ALL
SELECT '2','aab','1' UNION ALL
SELECT '3','aba','2' UNION ALL
SELECT '4','abb','2' UNION ALL
SELECT '5','baa','3' UNION ALL
SELECT '6','bba','4'
--SELECT * FROM [tb_level1]
--SELECT * FROM [tb_level2]
--SELECT * FROM [tb_level3]
-->SQL查询如下:
DELETE [tb_level1] WHERE level1Id=1
SELECT * FROM [tb_level1]
/*
level1Id name
----------- ----------
2 b
(1 行受影响)
*/
SELECT * FROM [tb_level2]
/*
level2Id name level1Id
----------- ---------- -----------
3 ba 2
4 bb 2
(2 行受影响)
*/
SELECT * FROM [tb_level3]
/*
level3Id name level2Id
----------- ---------- -----------
5 baa 3
6 bba 4
(2 行受影响)
*/
create trigger tri_a ON tb_level1
FOR delete
AS
begin
delete tb_level3 where level2Id in (select level2Id from tb_level2 where level1Id in (SELECT level1Id FROM DELETED)
delete tb_level2 where level1Id in (SELECT level1Id FROM DELETED)
end
go