SQL SERVER触发器记录指定的几笔资料更新记录

1.目的

为了记录数据库表中资料数据动态的变更，实时动态且方便调整记录的范围。

2.分析

需求:记录UPDATE 修改的记录

if exists(select 1 from inserted) and exists(select 1 from deleted) ：修改
if (exists (select 1 from inserted) and not exists (select 1 from deleted))：新增
if (not exists (select 1 from inserted) and exists (select 1 from deleted))：删除

3.实践

1.创建一个LOG表结构，最好字段栏位跟原表A一致或大体接近

*特别要注意！！！若是字符类型，长度一定要一致或者LOG表的字符长度大于原表！！！

否则更新后无法插入LOG记录！！！因为插入会报截断字符的错误！！！资料异常的情况下啊，误以为一直没有记录！！！

2.建立触发器

创建用 CREATE TRIGGER TRIG_A_TEST .... 即创建用 CREATE TRIGGER 触发器名 ....
后续修改及调整用 ALTER TRIGGER TRIG_A_TEST 即后续修改及调整用 ALTER TRIGGER 触发器名
删除 DROP TRIGGER TRIG_A_TEST 即删除 DROP TRIGGER TRIG_A_TEST 触发器名
```
DROP   TRIGGER TRIG_A_TEST
```
想要只需要部分TEST_NO的变更进行记录使用： SELECT 1 FROM INSERTED WHERE TEST_NO IN ('1','2','4')
想要所有TEST_NO的变更进行记录使用： SELECT 1 FROM INSERTED ，可以不用WHERE TEST_NO IN ('1','2','4') 进行限制

CREATE TRIGGER TRIG_A_TEST  --创建触发器-- DROP TRIGGER TRIG_A_TEST --删除触发器
ON A_TEST
FOR UPDATE,DELETE
AS
BEGINSET NOCOUNT OFF; DECLARE @DATE AS DATETIME DECLARE @CNT AS INT SELECT @DATE = GETDATE()IF UPDATE([DEST]) OR UPDATE([SER_NO]) BEGINIF EXISTS(SELECT 1 FROM INSERTED WHERE TEST_NO  IN ('1','2','4')) AND EXISTS(SELECT 1 FROM DELETED WHERE TEST_NO  IN ('1','2','4'))--修改BEGINSELECT @CNT = COUNT(*) FROM INSERTED  WHERE TEST_NO  IN  ('1','2','4')IF @CNT > 1 BEGININSERT INTO A_TEST_LOG    SELECT  *,CONVERT(VARCHAR,@CNT),@DATE FROM INSERTED  WHERE TEST_NO  IN  ('1','2','4')ENDELSEBEGINIF ( (SELECT SER_NO FROM INSERTED WHERE TEST_NO  IN  ('1','2','4')) != 0  AND   (SELECT SER_NO FROM INSERTED WHERE TEST_NO  IN  ('1','2','4') ) != (SELECT SER_NO FROM DELETED WHERE TEST_NO  IN  ('1','2','4'))  )  OR (  (SELECT DEST FROM INSERTED WHERE TEST_NO  IN  ('1','2','4') ) != (SELECT DEST FROM DELETED WHERE TEST_NO  IN  ('1','2','4')) ) BEGININSERT INTO A_TEST_LOG    SELECT *,'0',@DATE FROM INSERTED  WHERE TEST_NO  IN ('1','2','4')END	     	        END	END	ENDEND