[DB] 트리거를 사용한 업데이트 된 컬럼 확인

COLUMNS_UPDATED() 라는 명령이 있다.. 이거 확인해 보느라 혼났네 그려 ㅋ

왜 이걸 사용하느냐...
나는 특정 테이블이 업데이트 될때.
해당 테이블의 컬럼이 많고, 각 데이터가 업데이트 될때
해당 로그들을 일일이 저장해서 로그 형식으로 출력하고 싶은 프로젝트를 진행중이다..
일명 거창하게 말하면 Log Tracking System ㅋㅋ

MS-SQL 2008에서는 자체적인 추적 기능이 있긴 하지만..
그렇게 너무 많은 기능이 필요하지는 않다..
그리고 작업중인 DB는 2005 이므로 해당 DB에서 사용가능하게 테스트 소스를 구축하는 중이다..

아래의 소스를 보고 테스트 해보면 알수 있을듯 싶다..

 
/*
1번째컬럼 업데이트되었을때 값 :  1
2번째컬럼 업데이트되었을때 값 :  2
3번째컬럼 업데이트되었을때 값 :  4
4번째컬럼 업데이트되었을때 값 :  8
5번째컬럼 업데이트되었을때 값 :  16
6번째컬럼 업데이트되었을때 값 :  32
7번째컬럼 업데이트되었을때 값 :  64
8번째컬럼 업데이트되었을때 값 :  128
 각 컬럼 업데이트는 8자리까지 값을 가지고 있음
 컬럼의 갯수가 9개 이상일 경우에는 위 값을 자리수 별로 나누어야 함.
*/
Use TempDb
Go
Set NoCount On
Go
If Exists(Select * from sys.objects where name = 'Ssuby')
 Drop Table dbo.Ssuby
Go
Create Table dbo.Ssuby
(
 ColumnsIdx Int Identity(1,1) Not Null,
 Columns2 VarChar(10) Null,
 Columns3 VarChar(10) Null,
 Columns4 VarChar(10) Null,
 Columns5 VarChar(10) Null,
 Columns6 VarChar(10) Null,
 Columns7 VarChar(10) Null,
 Columns8 VarChar(10) Null
)
Go
Insert Into dbo.Ssuby(Columns2,Columns3,Columns4,Columns5,Columns6,Columns7,Columns8)
Select
 'Col2_' + Cast(number As VarChar) As Columns2,
 'Col3_' + Cast(number As VarChar) As Columns3,
 'Col4_' + Cast(number As VarChar) As Columns4,
 'Col5_' + Cast(number As VarChar) As Columns5,
 'Col6_' + Cast(number As VarChar) As Columns6,
 'Col7_' + Cast(number As VarChar) As Columns7,
 'Col8_' + Cast(number As VarChar) As Columns8
From master.dbo.spt_values
Where type = 'P' And number < 3
Go

If Exists(Select * from sys.objects where name = 'Trg_Upt_Ssuby')
 Drop Trigger dbo.Trg_Upt_Ssuby
Go
/*  ex1) 8개 이하의 테이블에서 업데이트된 컬럼 개수 확인하는 방법  */
Create Trigger dbo.Trg_Upt_Ssuby On dbo.Ssuby
FOR UPDATE
As
Begin
 --3 번째 컬럼 업데이트 됨
 IF (COLUMNS_UPDATED() & 4) > 0
   Begin
  Print '3 번째 컬럼중 하나가 업데이트 됨'
   End
 --2,4 번째 컬럼 업데이트 됨
 IF (COLUMNS_UPDATED() & 10) > 0
   Begin
  Print '2,4 번째 컬럼중 하나가 업데이트 됨'
   End
 --2,4 번째 컬럼 업데이트 됨
 IF (COLUMNS_UPDATED() & 10) = 10
   Begin
  Print '2,4 번째 컬럼 동시에 업데이트 됨'
   End
 --2,5,6,8 번째 컬럼 업데이트 됨
 IF (COLUMNS_UPDATED() & 178) > 0
   Begin
  Print '2,5,6,8 번째 컬럼중 하나가 업데이트 됨'
   End
End
Go
Set NoCount Off
Go

If Exists(Select * from sys.objects where name = 'Trg_Upt_Ssuby')
 Drop Trigger dbo.Trg_Upt_Ssuby
Go
If Exists(Select * from sys.objects where name = 'Ssuby')
 Drop Table dbo.Ssuby
Go

결과

Update dbo.Ssuby Set Columns2 = 'Col2_0' Where ColumnsIdx = 1
2,4 번째 컬럼중 하나가 업데이트 됨
2,5,6,8 번째 컬럼중 하나가 업데이트 됨
(1개 행 적용됨)

Update dbo.Ssuby Set Columns2 = 'Col2_0',Columns4 = 'Col4_0' Where ColumnsIdx = 1
2,4 번째 컬럼중 하나가 업데이트 됨
2,4 번째 컬럼 동시에 업데이트 됨
2,5,6,8 번째 컬럼중 하나가 업데이트 됨
(1개 행 적용됨)

Update dbo.Ssuby Set Columns3 = 'Col3_0' Where ColumnsIdx = 1
3 번째 컬럼중 하나가 업데이트 됨
(1개 행 적용됨)

Set NoCount On
Go

If Exists(Select * from sys.objects where name = 'Ssuby')
 Drop Table dbo.Ssuby
Go
Create Table dbo.Ssuby
(
 ColumnsIdx Int Identity(1,1) Not Null,
 Columns2 VarChar(10) Null, Columns3 VarChar(10) Null, Columns4 VarChar(10) Null,
 Columns5 VarChar(10) Null, Columns6 VarChar(10) Null, Columns7 VarChar(10) Null,
 Columns8 VarChar(10) Null, Columns9 VarChar(10) Null, Columns10 VarChar(10) Null,
 Columns11 VarChar(10) Null, Columns12 VarChar(10) Null, Columns13 VarChar(10) Null
)
Go
Insert Into dbo.Ssuby(Columns2,Columns3,Columns4,Columns5,Columns6,Columns7,Columns8,Columns9,Columns10,Columns11,Columns12,Columns13)
Select
 'Col2_' + Cast(number As VarChar) As Columns2, 'Col3_' + Cast(number As VarChar) As Columns3,
 'Col4_' + Cast(number As VarChar) As Columns4, 'Col5_' + Cast(number As VarChar) As Columns5,
 'Col6_' + Cast(number As VarChar) As Columns6, 'Col7_' + Cast(number As VarChar) As Columns7,
 'Col8_' + Cast(number As VarChar) As Columns8, 'Col9_' + Cast(number As VarChar) As Columns9,
 'Col10_' + Cast(number As VarChar) As Columns10, 'Col11_' + Cast(number As VarChar) As Columns11,
 'Col12_' + Cast(number As VarChar) As Columns12, 'Col13_' + Cast(number As VarChar) As Columns13
From master.dbo.spt_values
Where type = 'P' And number < 3
Go
If Exists(Select * from sys.objects where name = 'Trg_Upt_Ssuby')
 Drop Trigger dbo.Trg_Upt_Ssuby
Go
/*  ex1) 9개 이상의 테이블에서 업데이트된 컬럼 개수 확인하는 방법  */
Create Trigger dbo.Trg_Upt_Ssuby On dbo.Ssuby
FOR UPDATE
As
Begin
 --3 번째 컬럼 업데이트 됨
    IF (SUBSTRING(COLUMNS_UPDATED(),1,1) & 4 = 4)
   Begin
  Print '3 번째 컬럼 업데이트 됨'
   End
 --10 번째 컬럼 업데이트 됨
    IF (SUBSTRING(COLUMNS_UPDATED(),2,1) & 2 = 2)
   Begin
  Print '10 번째 컬럼 업데이트 됨'
   End
 --2,4,10 번째 컬럼 업데이트 됨
    IF ( (SUBSTRING(COLUMNS_UPDATED(),1,1) & 10 > 0) Or (SUBSTRING(COLUMNS_UPDATED(),2,1) & 2 > 0) )
   Begin
  Print '2,4,10 번째 컬럼중 하나가 업데이트 됨'
   End
 --3,5,9 번째 컬럼 업데이트 됨
    IF ( (SUBSTRING(COLUMNS_UPDATED(),1,1) & 20 = 20) AND (SUBSTRING(COLUMNS_UPDATED(),2,1) & 1 = 1) )
   Begin
  Print '3,5,9 번째 컬럼이 동시에 업데이트 됨'
   End
End
Go

Set NoCount Off
Go

If Exists(Select * from sys.objects where name = 'Trg_Upt_Ssuby')
 Drop Trigger dbo.Trg_Upt_Ssuby
Go
If Exists(Select * from sys.objects where name = 'Ssuby')
 Drop Table dbo.Ssuby
Go

결과

Update dbo.Ssuby Set Columns3 = 'Col3_0' Where ColumnsIdx = 1
3 번째 컬럼 업데이트 됨
(1개 행 적용됨)

Update dbo.Ssuby Set Columns10 = 'Col10_0' Where ColumnsIdx = 1
10 번째 컬럼 업데이트 됨
2,4,10 번째 컬럼중 하나가 업데이트 됨
(1개 행 적용됨)

Update dbo.Ssuby Set Columns5 = 'Col5_0' Where ColumnsIdx = 1
(1개 행 적용됨)

Update dbo.Ssuby Set Columns3 = 'Col3_0',Columns5 = 'Col5_0',Columns9 = 'Col9_0' Where ColumnsIdx = 1
3 번째 컬럼 업데이트 됨
3,5,9 번째 컬럼이 동시에 업데이트 됨
(1개 행 적용됨)