处理多表关系数据完整性
[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
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
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
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
