道一纹字

种一棵树最好的时间是十年前，其次就是现在。
  博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理
sqlserver触发器实例_级联删除

参考原文：https://blog.csdn.net/legendaryhaha/article/details/80368114

示例数据： 

复制代码
IF OBJECT_ID(N'sc', N'U') IS  NOT  NULL 
DROP TABLE [dbo].[sc]
CREATE TABLE [dbo].[sc](
    [SNo] [char](5) NOT NULL,
    [CNo] [char](5) NOT NULL,
    [Score] [numeric](3, 1) NULL
)

IF OBJECT_ID(N'Student', N'U') IS  NOT  NULL 
DROP TABLE [dbo].[Student]
CREATE TABLE [dbo].[Student](
    [SNo] [char](5) PRIMARY KEY NOT NULL,
    [CLno] [char](6) NULL,
    [SName] [varchar](8) NOT NULL,
    [SSex] [char](2) NULL,
    [SBir] [datetime] NULL,
    [SAge] [int] NULL,
    [Email] [varchar](40) NULL
)

ALTER TABLE [dbo].[Student]  WITH CHECK ADD CHECK  (([SSex] = '女' or [SSex] = '男'))

ALTER TABLE [dbo].[sc]  WITH CHECK ADD  CONSTRAINT [FK_sc_Student] FOREIGN KEY([SNo])
REFERENCES [dbo].[Student] ([SNo])

INSERT [dbo].[Student] ([SNo], [CLno], [SName], [SSex], [SBir], [SAge], [Email]) VALUES (N'00001', N'01    ', N'马行空', N'男', CAST(0x00007AB300000000 AS DateTime), 24, N'zk_u@163.com')
INSERT [dbo].[Student] ([SNo], [CLno], [SName], [SSex], [SBir], [SAge], [Email]) VALUES (N'00002', N'01    ', N'马春花', N'女', CAST(0x00007AD300000000 AS DateTime), 24, N'zkuu@163.com')
INSERT [dbo].[Student] ([SNo], [CLno], [SName], [SSex], [SBir], [SAge], [Email]) VALUES (N'00003', N'01    ', N'徐铮', N'男', CAST(0x00007AD500000000 AS DateTime), 24, NULL)
INSERT [dbo].[Student] ([SNo], [CLno], [SName], [SSex], [SBir], [SAge], [Email]) VALUES (N'00004', N'01    ', N'商宝震', N'男', CAST(0x00007AD900000000 AS DateTime), 24, NULL)
INSERT [dbo].[Student] ([SNo], [CLno], [SName], [SSex], [SBir], [SAge], [Email]) VALUES (N'00005', N'01    ', N'田归农', N'男', CAST(0x00007C4700000000 AS DateTime), 23, NULL)
INSERT [dbo].[Student] ([SNo], [CLno], [SName], [SSex], [SBir], [SAge], [Email]) VALUES (N'00006', N'01    ', N'苗人凤', N'男', CAST(0x00007C4A00000000 AS DateTime), 23, NULL)
INSERT [dbo].[Student] ([SNo], [CLno], [SName], [SSex], [SBir], [SAge], [Email]) VALUES (N'00007', N'01    ', N'王剑英', N'女', CAST(0x00007C6700000000 AS DateTime), 23, NULL)
INSERT [dbo].[Student] ([SNo], [CLno], [SName], [SSex], [SBir], [SAge], [Email]) VALUES (N'00008', N'01    ', N'程灵素', N'女', CAST(0x00007DF900000000 AS DateTime), 22, NULL)
INSERT [dbo].[Student] ([SNo], [CLno], [SName], [SSex], [SBir], [SAge], [Email]) VALUES (N'00009', N'01    ', N'李沅芷', N'女', CAST(0x00007CAA00000000 AS DateTime), 23, NULL)
INSERT [dbo].[Student] ([SNo], [CLno], [SName], [SSex], [SBir], [SAge], [Email]) VALUES (N'00010', N'01    ', N'姜铁山', N'男', CAST(0x00007CC000000000 AS DateTime), 23, NULL)
INSERT [dbo].[Student] ([SNo], [CLno], [SName], [SSex], [SBir], [SAge], [Email]) VALUES (N'00011', N'02    ', N'汤沛', N'男', CAST(0x00007CDB00000000 AS DateTime), 23, NULL)
INSERT [dbo].[Student] ([SNo], [CLno], [SName], [SSex], [SBir], [SAge], [Email]) VALUES (N'00012', N'02    ', N'吕小妹', N'女', CAST(0x00007CFA00000000 AS DateTime), 23, NULL)
INSERT [dbo].[Student] ([SNo], [CLno], [SName], [SSex], [SBir], [SAge], [Email]) VALUES (N'00013', N'02    ', N'钟兆英', N'男', CAST(0x00007BAF00000000 AS DateTime), 24, NULL)
INSERT [dbo].[Student] ([SNo], [CLno], [SName], [SSex], [SBir], [SAge], [Email]) VALUES (N'00014', N'02    ', N'钟兆能', N'男', CAST(0x00007EBA00000000 AS DateTime), 22, NULL)
INSERT [dbo].[Student] ([SNo], [CLno], [SName], [SSex], [SBir], [SAge], [Email]) VALUES (N'00015', N'02    ', N'童怀道', N'男', CAST(0x00007D5B00000000 AS DateTime), 23, NULL)
INSERT [dbo].[Student] ([SNo], [CLno], [SName], [SSex], [SBir], [SAge], [Email]) VALUES (N'00016', N'02    ', N'南仁通', N'男', CAST(0x00007D8200000000 AS DateTime), 23, NULL)
INSERT [dbo].[Student] ([SNo], [CLno], [SName], [SSex], [SBir], [SAge], [Email]) VALUES (N'00017', N'02    ', N'孙刚峰', N'男', CAST(0x00007D7A00000000 AS DateTime), 23, NULL)
INSERT [dbo].[Student] ([SNo], [CLno], [SName], [SSex], [SBir], [SAge], [Email]) VALUES (N'00018', N'02    ', N'李廷豹', N'男', CAST(0x00007D8100000000 AS DateTime), 23, NULL)
INSERT [dbo].[Student] ([SNo], [CLno], [SName], [SSex], [SBir], [SAge], [Email]) VALUES (N'00019', N'02    ', N'余鱼同', N'男', CAST(0x00007B5E00000000 AS DateTime), 24, NULL)
INSERT [dbo].[Student] ([SNo], [CLno], [SName], [SSex], [SBir], [SAge], [Email]) VALUES (N'00020', N'02    ', N'苗若兰', N'女', CAST(0x00007B5E00000000 AS DateTime), 24, NULL)
INSERT [dbo].[Student] ([SNo], [CLno], [SName], [SSex], [SBir], [SAge], [Email]) VALUES (N'00021', N'03    ', N'王仲萍', N'女', CAST(0x00007B9A00000000 AS DateTime), 24, NULL)
INSERT [dbo].[Student] ([SNo], [CLno], [SName], [SSex], [SBir], [SAge], [Email]) VALUES (N'00022', N'03    ', N'田青文', N'男', CAST(0x00007D0200000000 AS DateTime), 23, NULL)
INSERT [dbo].[Student] ([SNo], [CLno], [SName], [SSex], [SBir], [SAge], [Email]) VALUES (N'00023', N'03    ', N'常赫志', N'男', CAST(0x00007D1100000000 AS DateTime), 23, NULL)
INSERT [dbo].[Student] ([SNo], [CLno], [SName], [SSex], [SBir], [SAge], [Email]) VALUES (N'00024', N'03    ', N'桑飞虹', N'女', CAST(0x00007D2A00000000 AS DateTime), 23, NULL)
INSERT [dbo].[Student] ([SNo], [CLno], [SName], [SSex], [SBir], [SAge], [Email]) VALUES (N'00025', N'03    ', N'任通武', N'男', CAST(0x00007CBE00000000 AS DateTime), 23, NULL)
INSERT [dbo].[Student] ([SNo], [CLno], [SName], [SSex], [SBir], [SAge], [Email]) VALUES (N'00026', N'03    ', N'刘之余', N'男', CAST(0x00007B5600000000 AS DateTime), 24, NULL)
INSERT [dbo].[Student] ([SNo], [CLno], [SName], [SSex], [SBir], [SAge], [Email]) VALUES (N'00027', N'03    ', N'文醉翁', N'男', CAST(0x00007B2100000000 AS DateTime), 24, NULL)
INSERT [dbo].[Student] ([SNo], [CLno], [SName], [SSex], [SBir], [SAge], [Email]) VALUES (N'00028', N'03    ', N'曹猛', N'男', CAST(0x00007B2700000000 AS DateTime), 24, NULL)
INSERT [dbo].[Student] ([SNo], [CLno], [SName], [SSex], [SBir], [SAge], [Email]) VALUES (N'00029', N'03    ', N'蓝秦', N'女', CAST(0x00007CD500000000 AS DateTime), 23, NULL)
INSERT [dbo].[Student] ([SNo], [CLno], [SName], [SSex], [SBir], [SAge], [Email]) VALUES (N'00030', N'03    ', N'凤南天', N'男', CAST(0x00007B6600000000 AS DateTime), 24, NULL)
INSERT [dbo].[Student] ([SNo], [CLno], [SName], [SSex], [SBir], [SAge], [Email]) VALUES (N'00050', NULL, N'李明', NULL, NULL, NULL, NULL)
INSERT [dbo].[sc] ([SNo], [CNo], [Score]) VALUES (N'00001', N'001  ', CAST(80.0 AS Numeric(3, 1)))
INSERT [dbo].[sc] ([SNo], [CNo], [Score]) VALUES (N'00001', N'002  ', CAST(60.0 AS Numeric(3, 1)))
INSERT [dbo].[sc] ([SNo], [CNo], [Score]) VALUES (N'00001', N'003  ', CAST(88.0 AS Numeric(3, 1)))
INSERT [dbo].[sc] ([SNo], [CNo], [Score]) VALUES (N'00001', N'004  ', CAST(45.0 AS Numeric(3, 1)))
INSERT [dbo].[sc] ([SNo], [CNo], [Score]) VALUES (N'00002', N'001  ', CAST(70.0 AS Numeric(3, 1)))
INSERT [dbo].[sc] ([SNo], [CNo], [Score]) VALUES (N'00002', N'002  ', CAST(50.0 AS Numeric(3, 1)))
INSERT [dbo].[sc] ([SNo], [CNo], [Score]) VALUES (N'00002', N'003  ', NULL)
INSERT [dbo].[sc] ([SNo], [CNo], [Score]) VALUES (N'00002', N'004  ', CAST(55.0 AS Numeric(3, 1)))
INSERT [dbo].[sc] ([SNo], [CNo], [Score]) VALUES (N'00003', N'002  ', CAST(47.0 AS Numeric(3, 1)))
INSERT [dbo].[sc] ([SNo], [CNo], [Score]) VALUES (N'00004', N'001  ', CAST(60.0 AS Numeric(3, 1)))
INSERT [dbo].[sc] ([SNo], [CNo], [Score]) VALUES (N'00004', N'002  ', CAST(92.0 AS Numeric(3, 1)))
INSERT [dbo].[sc] ([SNo], [CNo], [Score]) VALUES (N'00004', N'003  ', CAST(99.0 AS Numeric(3, 1)))
INSERT [dbo].[sc] ([SNo], [CNo], [Score]) VALUES (N'00004', N'004  ', CAST(89.0 AS Numeric(3, 1)))
INSERT [dbo].[sc] ([SNo], [CNo], [Score]) VALUES (N'00008', N'001  ', NULL)
INSERT [dbo].[sc] ([SNo], [CNo], [Score]) VALUES (N'00009', N'002  ', CAST(88.0 AS Numeric(3, 1)))
INSERT [dbo].[sc] ([SNo], [CNo], [Score]) VALUES (N'00010', N'001  ', CAST(50.0 AS Numeric(3, 1)))
INSERT [dbo].[sc] ([SNo], [CNo], [Score]) VALUES (N'00011', N'003  ', CAST(84.0 AS Numeric(3, 1)))
INSERT [dbo].[sc] ([SNo], [CNo], [Score]) VALUES (N'00012', N'003  ', CAST(87.0 AS Numeric(3, 1)))
INSERT [dbo].[sc] ([SNo], [CNo], [Score]) VALUES (N'00014', N'001  ', CAST(46.0 AS Numeric(3, 1)))
INSERT [dbo].[sc] ([SNo], [CNo], [Score]) VALUES (N'00014', N'003  ', CAST(89.0 AS Numeric(3, 1)))
INSERT [dbo].[sc] ([SNo], [CNo], [Score]) VALUES (N'00015', N'002  ', CAST(77.0 AS Numeric(3, 1)))
INSERT [dbo].[sc] ([SNo], [CNo], [Score]) VALUES (N'00015', N'003  ', CAST(44.0 AS Numeric(3, 1)))
INSERT [dbo].[sc] ([SNo], [CNo], [Score]) VALUES (N'00015', N'004  ', CAST(88.0 AS Numeric(3, 1)))
INSERT [dbo].[sc] ([SNo], [CNo], [Score]) VALUES (N'00016', N'002  ', CAST(66.0 AS Numeric(3, 1)))
INSERT [dbo].[sc] ([SNo], [CNo], [Score]) VALUES (N'00016', N'003  ', CAST(56.0 AS Numeric(3, 1)))
INSERT [dbo].[sc] ([SNo], [CNo], [Score]) VALUES (N'00018', N'001  ', CAST(91.0 AS Numeric(3, 1)))
INSERT [dbo].[sc] ([SNo], [CNo], [Score]) VALUES (N'00018', N'002  ', CAST(90.0 AS Numeric(3, 1)))
INSERT [dbo].[sc] ([SNo], [CNo], [Score]) VALUES (N'00021', N'001  ', CAST(88.0 AS Numeric(3, 1)))
INSERT [dbo].[sc] ([SNo], [CNo], [Score]) VALUES (N'00021', N'002  ', CAST(98.0 AS Numeric(3, 1)))
INSERT [dbo].[sc] ([SNo], [CNo], [Score]) VALUES (N'00022', N'002  ', CAST(87.0 AS Numeric(3, 1)))
INSERT [dbo].[sc] ([SNo], [CNo], [Score]) VALUES (N'00024', N'001  ', CAST(88.0 AS Numeric(3, 1)))
INSERT [dbo].[sc] ([SNo], [CNo], [Score]) VALUES (N'00025', N'001  ', CAST(44.0 AS Numeric(3, 1)))
INSERT [dbo].[sc] ([SNo], [CNo], [Score]) VALUES (N'00025', N'003  ', CAST(60.0 AS Numeric(3, 1)))
INSERT [dbo].[sc] ([SNo], [CNo], [Score]) VALUES (N'00026', N'002  ', CAST(95.0 AS Numeric(3, 1)))
INSERT [dbo].[sc] ([SNo], [CNo], [Score]) VALUES (N'00026', N'003  ', CAST(80.0 AS Numeric(3, 1)))
INSERT [dbo].[sc] ([SNo], [CNo], [Score]) VALUES (N'00028', N'003  ', CAST(40.0 AS Numeric(3, 1)))
INSERT [dbo].[sc] ([SNo], [CNo], [Score]) VALUES (N'00030', N'001  ', CAST(99.0 AS Numeric(3, 1)))
INSERT [dbo].[sc] ([SNo], [CNo], [Score]) VALUES (N'00030', N'002  ', CAST(78.0 AS Numeric(3, 1)))
INSERT [dbo].[sc] ([SNo], [CNo], [Score]) VALUES (N'00030', N'003  ', CAST(50.0 AS Numeric(3, 1)))
View Code
复制代码

需求：在表Student中建立删除触发器，实现表Student和表SC的级联删除，也就是只要删除表Student中的元组学号为s1，则表SC中SNO为s1的元组也要删除

复制代码
CREATE TRIGGER T_STU ON STUDENT
INSTEAD OF DELETE
AS
    BEGIN
        DECLARE @SNO VARCHAR(10)
        SELECT @SNO = SNO FROM DELETED
        DELETE FROM SC WHERE SNo = @SNO
        DELETE FROM STUDENT  WHERE SNO = @SNO 
    END
复制代码

逻辑说明：

因为sc表存在外键，无法直接针对student表进行删除，所以需要使用instead of 触发器，使其不执行delete本身，而是执行触发器中的sql。触发器中的sql先删除sc表中的纪录再删除student的纪录。

posted on   道一纹字  阅读(916)  评论(0)    收藏  举报
努力加载评论中...
点击右上角即可分享
微信分享提示