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