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
BEGIN
  SET NOCOUNT OFF; 
  DECLARE @DATE AS DATETIME 
  DECLARE @CNT AS INT 
  SELECT @DATE = GETDATE()
  IF UPDATE([DEST]) OR UPDATE([SER_NO]) 
  BEGIN
	IF 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'))--修改
	BEGIN
		SELECT @CNT = COUNT(*) FROM INSERTED  WHERE TEST_NO  IN  ('1','2','4')
		
		IF @CNT > 1 
		BEGIN
		     INSERT INTO A_TEST_LOG    
                            SELECT  *,CONVERT(VARCHAR,@CNT),@DATE 
                            FROM INSERTED  WHERE TEST_NO  IN  ('1','2','4')
		END
		ELSE
		BEGIN
		         IF ( (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')) 
				    ) 
				 BEGIN
				    INSERT INTO A_TEST_LOG    
                            SELECT *,'0',@DATE 
                            FROM INSERTED  WHERE TEST_NO  IN ('1','2','4')

				 END	     	        
		END	
	END	
  END

END

    3.查询触发器

•  exec sp_helptext '触发器名字'

 exec sp_helptext 'TRIG_A_TEST'