SQL Server触发器Trigger(1)

为了演示触发器，下面例子分别实现插入，删除触发器。

示例之前，先创建两个表，关关联起来。

代码

-- 创建一个表[Topic]
CREATE TABLE dbo.Topic
(
    TopicId INT IDENTITY ( 1 , 1 ) PRIMARY KEY NOT NULL ,
    Title NVARCHAR ( 300 ) NOT NULL ,
    Content NVARCHAR ( 2000 ) NOT NULL ,
    Author NVARCHAR ( 50 ) NOT NULL ,
    Reply INT DEFAULT ( 0 ) NOT NULL
)
GO

另外一个表，

代码

-- 创建另外一个表[SubTopic]，其中有一字段是表[Topic]的主键,即是Foreign key
CREATE TABLE dbo.SubTopic
(
    SubTopicId INT IDENTITY ( 1 , 1 ) PRIMARY KEY NOT NULL ,
    TopicId INT , -- 外键 reference table dbo.Topic primary key
    Author NVARCHAR ( 50 ) NOT NULL ,
    Content NVARCHAR ( 4000 ) NOT NULL
)
GO

接下来，就是触发器实现代码，当有论题被回复时，在主表中的Reply字段加1。

代码

-- 为表[SubTopic]创建一个插入的触发器
CREATE TRIGGER dbo.tri_SubTopic_Insert ON dbo.SubTopic
FOR INSERT
AS
BEGIN
     SET NOCOUNT ON
     DECLARE @key INT
     SELECT @key = [ TopicId ] FROM inserted
     -- 当有记录插入时，同时更新表[Topic]的[reply]字段，回复数量添加1.
     UPDATE [ dbo ] . [ Topic ] SET [ Reply ] = [ Reply ] + 1 WHERE [ TopicId ] = @key
END

GO

当有论题被删除时，在主表中的Reply字段也相应减1。

代码

-- 为表[SubTopic]创建一个删除触发器
CREATE TRIGGER dbo.tri_SubTopic_Delete ON dbo.SubTopic
FOR DELETE
AS
BEGIN
     SET NOCOUNT ON
     DECLARE @key INT
     SELECT @key = [ TopicId ] FROM deleted
     -- 在有记录删除时，同时更新表[Topic]的[reply]字段，回复数量减1.
     UPDATE [ dbo ] . [ Topic ] SET [ Reply ] = [ Reply ] - 1 WHERE [ TopicId ] = @key
END

GO

最后一个触发器，就是主表的论题被删除时，所有子表相应本论题的回复应也全部删除，

代码

CREATE TRIGGER dbo.tri_Topic_Delete ON dbo.Topic
FOR DELETE
AS
BEGIN
     SET NOCOUNT ON
     DECLARE @key INT
     SELECT @key = [ TopicId ] FROM deleted
     DELETE FROM [ dbo ] . [ SubTopic ] WHERE [ TopicId ] = @key
END

秒客网

SQL Server触发器Trigger(1)

相关文章