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