SqlServer触发器

1、创建测试表

USE [test]
GO

/****** Object:  Table [dbo].[xmq_1]    Script Date: 10/09/2017 17:31:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[xmq_1](
	[id] [varchar](100) NOT NULL,
	[col1] [varchar](500) NULL,
	[col2] [varchar](500) NULL,
 CONSTRAINT [PK_xmq_1] 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

SET ANSI_PADDING OFF
GO

　　

2、创建测试日志表

USE [test]
GO

/****** Object:  Table [dbo].[xmq_1_log]    Script Date: 10/09/2017 17:32:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[xmq_1_log](
	[id] [varchar](100) NOT NULL,
	[src_id] [varchar](100) NOT NULL,
	[mod_date] [datetime] NOT NULL,
 CONSTRAINT [PK_log_xmq_1] 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

SET ANSI_PADDING OFF
GO

　

/**创建触发器**/
CREATE TRIGGER xmq_1_trigger
ON xmq_1
AFTER UPDATE
AS
	BEGIN
		
		/**数据获取**/
		declare @xmq_1_id varchar(100),@col1_old varchar(500),@col1_new varchar(500),@col2_old varchar(500),@col2_new varchar(500)
		/**赋值**/
		select @xmq_1_id=id,@col1_old=col1,@col2_old=col2 from DELETED
		select @col1_new=col1,@col2_new=col2 from INSERTED
		/**判断日志表是否有数据，有就更新、否则插入**/
		if(select count(*) from xmq_1_log where src_id=@xmq_1_id)>0
		begin
			/**不更新col2字段的时候更新日志表**/
			if not update(col2)
			begin
				update xmq_1_log set mod_date=getdate() where src_id=@xmq_1_id
			end
		end
		else
		begin
			insert into xmq_1_log values(replace(newid(),'-',''),@xmq_1_id,getdate())
		end
		
	END
/**修改ALTER TRIGGER xmq_1_trigger**/
/**禁用触发器**/
disable trigger xmq_1_trigger on xmq_1;
/**启用触发器**/
enable trigger xmq_1_trigger on xmq_1;
/**查询已存在的触发器**/
select * from sys.triggers;
select * from sys.objects where type = 'TR';
/**查看创建触发器语句**/
exec sp_helptext 'xmq_1_trigger';

　　

/**测试**/
insert into xmq_1 values(replace(newid(),'-',''),'a','b');
update xmq_1 set col1='a2';
update xmq_1 set col2='b2';
update xmq_1 set col1='a2',col2='b2';
select * from xmq_1;
select * from xmq_1_log;