处理多表关系数据完整性

[Fqa]表与[FqaVisual]表，是一对多的关系，即是说[FqaVisual]表中有一[FqaId]字段是FOREIGN KEY也是[Fqa]表的主键。
还有一点重点，就是[Fqa]表的[VisualPassed]字段，与[FqaVisual]表的[Result]字段，这两个字段的数据类型均为BIT。
当[VisulaPasswd]字段值为0时，[FqaVisual]表可添加一笔或多笔记录，但最后一笔记录的[Result]字段值为1，一旦[Result]字段值为1时，还要更新[Fqa]表的[VisualPassed]字段为1。
当[VisulaPasswd]字段值为1时，无法再对[FqaVisual]表添加记录。

为了实现这些要求，只有对[FqaVisual]表写三个存储过程tri_FqaVisual_Insert，tri_FqaVisual_Update和tri_FqaVisual_Delete

tri_FqaVisual_Insert存储过程：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[tri_FqaVisual_Insert] ON [dbo].[FqaVisual]
FOR INSERT
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @FqaId INT,@Result BIT    
    SELECT @FqaId = [FqaId],@Result = [Result] FROM INSERTED
        
    IF @Result = 1
    --如果添加记录[Result]字段值为1时，同时得更新[Fqa]的字段[VisualPassed]为1
    UPDATE [dbo].[Fqa] SET [VisualPassed] = 1 WHERE [FqaId] = @FqaId
END

 tri_FqaVisual_Update存过程：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[tri_FqaVisual_Update] ON [dbo].[FqaVisual]
FOR UPDATE
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @FqaVisualId INT,@MaxId INT, @FqaId INT,@Result BIT
    SELECT @FqaVisualId = [FqaVisualId],@FqaId = [FqaId],@Result = [Result] FROM INSERTED
    
    --找出最后一笔记录。
    SET @MaxId = (SELECT MAX([FqaVisualId]) FROM [dbo].[FqaVisual] WHERE [FqaId] = @FqaId)
    
    --如果不是最后一笔记录，都不能把[Result]值更新为1
    IF ((@FqaVisualId <> @MaxId) AND (@Result = 1))     
    UPDATE [dbo].[FqaVisual] SET [Result] = 0 WHERE [FqaVisualId] = @FqaVisualId
    
    --如果是最后一笔记录，去更新[Fqa]表的[VisualPassed]的字段值
    IF (@FqaVisualId = @MaxId)
    UPDATE [dbo].[Fqa] SET [VisualPassed] = @Result WHERE [FqaId] = @FqaId
END

 
tri_FqaVisual_Delete存储过程：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[tri_FqaVisual_Delete] ON [dbo].[FqaVisual]
FOR DELETE
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @FqaId INT,@Result BIT    
    SELECT @FqaId = [FqaId],@Result = [Result] FROM DELETED
    
    IF @Result = 1
    --如果把[FqaVisual]表最后一笔[Result]字段值为1的记录删除，还得把[Fqa]表的字段[VisualPassed]更新为0
    UPDATE [dbo].[Fqa] SET [VisualPassed] = 0 WHERE [FqaId] = @FqaId
END