sql server2008里面的触发器

来源：互联网发布：java开发手册 chm 编辑：程序博客网时间：2024/06/15 17:10

触发器是一种特殊的存储过程﹐它不能被显式地调用﹐而是在往表中插入记录﹑更新记录或者删除记录时被自动地激活。所以触发器可以用来实现对表实施复杂的完整性约束。

SQL Server为每个触发器都创建了两个专用表﹕Inserted表和Deleted表。

//1 在哪里建立触发器

通过数据库->你的数据库->表->触发器->右击->新建触发器.
和新建一个查询,然后再写上触发器是一样的..
只是前者,编辑器帮你写了个开头而已，

编写完后，点击“执行”按钮执行命令

//2 在插入操作时删除ID值最小的行，用于解决历史备份问题

USE [wwwTest]
GO
/****** Object: Trigger [dbo].[AfterInsert] Script Date: 05/07/2017 17:41:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================

ALTER TRIGGER [dbo].[AfterInsert] ON [dbo].[DuanSaiCheckResultTab]
FOR INSERT

AS
begin
declare @rowCount int
select @rowCount=count(RowID) from [DuanSaiCheckResultTab]

if(@rowCount>10)

delete from [DuanSaiCheckResultTab]
where RowID=
(select min(RowID) from [DuanSaiCheckResultTab])

end

//3 把删除的记录写进历史记录表

USE [wwwTest]
GO
/****** Object: Trigger [dbo].[AfterDelete] Script Date: 05/07/2017 17:48:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================

ALTER TRIGGER [dbo].[AfterDelete] ON [dbo].[DuanSaiCheckResultTab]
FOR delete
AS

INSERT INTO [DuanSaiCheckResultTab2]
SELECT
DuanSaiID,CheckTime,CheckStandard,SourcePicPath1,
SourcePicPath2,SourcePicPath3,CheckResult,CheckResultPicPath,
Addition
FROM deleted

0 0