https://www.cnblogs.com/hoojo/archive/2011/07/20/2111316.html

以上是不错的数据库触发器博客，下面是我自己整理的一些，关于修改了表字段后，做一些判断的触发

USE [Test]

GO

/****** Object: Table [dbo].[TestTrigger] Script Date: 2018/2/7 下午 02:47:29 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[TestTrigger](

[ID] [INT] IDENTITY(1,1) NOT NULL,

[TestInt] [INT] NULL,

[TestNvarchar] [NVARCHAR](50) NULL,

[TestDate] [DATETIME] NULL,

CONSTRAINT [PK_TestTrigger] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

 

 

--创建insert插入类型触发器

if (object_id('tgr_TestTrigger_insert', 'tr') is not null)

drop trigger tgr_TestTrigger_insert

go

create trigger tgr_TestTrigger_insert

on TestTrigger

for insert --插入触发

as

--定义变量

declare @ID int, @TestInt int, @TestNvarchar NVARCHAR(50),@TestDate DATE;

--在inserted表中查询已经插入记录信息

select @ID = ID, @TestInt = TestInt,@TestNvarchar=TestNvarchar,@TestDate=TestDate from inserted;

IF(@TestNvarchar=N'b')

BEGIN

INSERT INTO [dbo].[TestTriggerStory](TestInt,TestNvarchar,TestDate) SELECT TestInt,TestNvarchar,TestDate FROM inserted;

END

go

--创建insert插入类型触发器

if (object_id('tgr_TestTrigger_update', 'tr') is not null)

drop trigger tgr_TestTrigger_update

go

create trigger tgr_TestTrigger_update

on TestTrigger

for update --插入触发

AS

--定义变量

declare @ID int, @TestInt int, @TestNvarchar NVARCHAR(50),@TestDate DATE,

@IDNew int, @TestIntNew int, @TestNvarcharNew NVARCHAR(50),@TestDateNew DATE;

--查看更新前的数据

select @ID = ID, @TestInt = TestInt,@TestNvarchar=TestNvarchar,@TestDate=TestDate FROM Deleted ;

--PRINT @TestNvarchar

IF(@TestNvarchar<>N'b')

BEGIN

PRINT '触发了1'

select @IDNew = ID, @TestIntNew = TestInt,@TestNvarcharNew=TestNvarchar,@TestDateNew=TestDate FROM inserted ;

IF(@TestNvarcharNew=N'b')

BEGIN

INSERT INTO [dbo].[TestTriggerStory](TestInt,TestNvarchar,TestDate) SELECT TestInt,TestNvarchar,TestDate FROM inserted;

PRINT '触发了2'

END

END

 

INSERT INTO dbo.TestTrigger

( TestInt, TestNvarchar, TestDate )

VALUES ( 0, -- TestInt - int

N'b', -- TestNvarchar - nvarchar(50)

GETDATE() -- TestDate - datetime

)

 

SELECT * FROM dbo.TestTrigger

UPDATE dbo.TestTrigger SET TestNvarchar=N'b' WHERE ID=1