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;