두 테이블 값 비교하여 변경된 컬럼을 알아내는 쿼리 질문드립니다.

he2ng 2014.08.20 16:43 Views : 9795

안녕하세요.

질문 하나 드립니다.

테이블의 변경 기록을 남기기 위하여 트리거를 이용하였습니다.

트리거를 사용 하여 테이블의 업데이트 시에 변경 되어지는 컬럼과 변경전 값, 변경 후 값을 로그로 남기고 싶어서요.

그래서 deleted 테이블과 inserted 테이블 값을 비교하여

(deleted table)

SEQ CODE COMP_BRANCH_CODE COMP_RESELLER_CODE CHARGE_CODE MANAGE_KIND SERVICE_KIND
1 P00039 B00003 NULL H001 M A

(inserted table)

SEQ CODE COMP_BRANCH_CODE COMP_RESELLER_CODE CHARGE_CODE MANAGE_KIND SERVICE_KIND
1 P00039 B00011 NULL H001 M A

달라진 컬럼(COMP_BRANCH_CODE)과 컬럼 값(B00003 > B00011)을 뽑아 LOG 테이블에 ETC 컬럼에 넣고 싶은데

(log table)

ETC

COMP_BRANCH_CODE:B00003 > B00011

어떻해야 할지 모르겠어서 글 올립니다.

IF EXISTS (select * from deleted a inner join inserted b on (a.code = b.code and a.comp_branch_code <> b.comp_branch_code) BEGIN

insert into Log_table (ETC)

select 'comp_branch_code: ' + a.comp_branch_code +'>' + b.comp_branch_code

from deleted a inner join insertd b on (a.code = b.code)

이렇게 하긴 했는데, 컬럼마다 다 비교하여 존재 여부 확인토록 하는 게 맞는 가 싶어서 고민입니다.

고수님들의 조언 구합니다...ㅜㅠ 제발용

I want to

cmd_document_vote_user Update Log List

Comment 5

무념

2014.08.20 18:03

로그는 로그일 뿐입니다. 추가나 업데이트 시 이를 조회하거나 이용하려고
한다면 잘못된 설계란 판단이 듭니다.(제생각)

또한, 트리거는 실패할 수도 있습니다. all or noting이 맞다는 판단하에...

저라면 업데이트/추가하는 프로시저에 변경 시
로그를 남기고 참고만 하심이 좋을 것 같습니다.

히스토리를 가공하는건 좋지 않다는 생각을 해요.

1. Insert/Update/Delete 프로세스에 로그를 삽입하는 쿼리 추가
2. 굳이, 변경 된 사항만 알고 싶다면...
Update 시에 컬럼 값을 조회하여 변경된 정보만을 히스토리에 추가.

두 테이블의 업데이트 시마다 비교하여 트리거에 히스토리를 남기는건 안하셨으면 좋겠어요.
개인 공부면 모르겠지만 이 코드를 인수받는 입장이 되면 ㅜㅜ 눈물날 것 같아요.

cmd_comment_vote_user Upvote0 Downvote0
he2ng

2014.08.20 20:02

고견 감사합니다!!
트리거를 사용하면 성능을 떨어뜨린다는 이야기를 듣고 사용치 않으려고 했는데, 업데이트/추가 하는 프로세스가 웹 상에 있어서요..
어쩔 수 없이 트리거를 사용했습니다..ㅠㅠㅠ

획득하려는 하는 정보가 table 변경에 대한 로그성 정보이나 변경을 추적하여 다른 시스템으로 전달해야하는 부분이라서 가공했는데 이부분 역시 고민이 많네용...

주신 의견 참고하여 로그성 정보로 남겨두되 그 정보를 가공할 수 있도록 변경해봐야겠네용 ^_^
감사합니당!!!!!!!!!!!!

cmd_comment_vote_user Upvote0 Downvote0
처리짱

2014.08.20 19:06

테이블 전체 컬럼의 변경사항을 보고 싶다면
똑같은 테이블을 이름 틀리게 만들어서 업데이트 전에 한줄씩 인서트를 해주면
비교가 될거 같은데요...

cmd_comment_vote_user Upvote0 Downvote0
he2ng

2014.08.20 20:05

아 ~
업데이트 전 한줄 씩 보는 것은 이미 트리거에서 Inserted와 Deleted 테이블로 확인을 하였는데,
컬럼 비교가 참 어렵네용...ㅠㅠ
트리거 사용부터 다시 고민해보도록 해야겠어요...^^
고견 감사합니다 ^_^

cmd_comment_vote_user Upvote0 Downvote0
원론

2014.08.21 17:20

-- 사실 데이터의 동일 비교는 UNION으로도 확인이 가능하다.

-- 하기QUERY는 전체 데이터 중 1개라도 컬럼값이 다르기 때문에(COMP_BRANCH_CODE컬럼) UNION시 2개의 row추출
SELECT 1 AS SEQ, 'P00039' AS CODE, 'B00003' AS COMP_BRANCH_CODE, NULL AS COMP_RESELLER_CODE, 'H001' AS CHARGE_CODE, 'M' AS MANAGE_KIND, 'A' AS SERVICE_KIND
UNION
SELECT 1 AS SEQ, 'P00039' AS CODE, 'B00011' AS COMP_BRANCH_CODE, NULL AS COMP_RESELLER_CODE, 'H001' AS CHARGE_CODE, 'M' AS MANAGE_KIND, 'A' AS SERVICE_KIND
GO

-- 하기QUERY는 모든 데이터가 동일하기 때문에 UNION시 1개의 row추출
SELECT 1 AS SEQ, 'P00039' AS CODE, 'B00003' AS COMP_BRANCH_CODE, NULL AS COMP_RESELLER_CODE, 'H001' AS CHARGE_CODE, 'M' AS MANAGE_KIND, 'A' AS SERVICE_KIND
UNION
SELECT 1 AS SEQ, 'P00039' AS CODE, 'B00003' AS COMP_BRANCH_CODE, NULL AS COMP_RESELLER_CODE, 'H001' AS CHARGE_CODE, 'M' AS MANAGE_KIND, 'A' AS SERVICE_KIND
GO

-- 하지만, 어느부분이 틀렸는지는 일일히 컬럼값을 확인하여야 한다. ( 현재 테이블 구성인 경우 7개의 컬럼 )
-- 어느컬럼이 틀렸는지 확인해보자..

--##########################################################################################################--

-- inserted / deleted 테이블 생성
IF OBJECT_ID('inserted') IS NOT NULL DROP TABLE inserted
GO

CREATE TABLE inserted ( SEQ INT, CODE NCHAR(6), COMP_BRANCH_CODE NCHAR(6), COMP_RESELLER_CODE NCHAR(6), CHARGE_CODE NCHAR(4), MANAGE_KIND NCHAR(1), SERVICE_KIND NCHAR(1) )
GO

IF OBJECT_ID('deleted') IS NOT NULL DROP TABLE deleted
GO

SELECT * INTO deleted FROM inserted WHERE 1 = 2
GO

-- inserted테이블 데이터 입력
INSERT INTO inserted SELECT 1 AS SEQ, 'P00039' AS CODE , 'B00011' AS COMP_BRANCH_CODE, NULL AS COMP_RESELLER_CODE, 'H001' AS CHARGE_CODE, 'M' AS MANAGE_KIND, 'A' AS SERVICE_KIND
GO

-- deleted테이블 데이터 입력
INSERT INTO deleted SELECT 1 AS SEQ, 'P00039' AS CODE , 'B00003' AS COMP_BRANCH_CODE, NULL AS COMP_RESELLER_CODE, 'H001' AS CHARGE_CODE, 'M' AS MANAGE_KIND, 'A' AS SERVICE_KIND
GO

-- check 테이블 생성
IF OBJECT_ID('t_inserted_check') IS NOT NULL DROP TABLE t_inserted_check
GO
CREATE TABLE t_inserted_check ( SEQ INT, joinkey TINYINT, value NVARCHAR(MAX) ) -- t_inserted_check TABLE
GO
CREATE NONCLUSTERED INDEX nix_seq_t_inserted_check ON t_inserted_check ( SEQ, joinkey ) -- INDEX 생성
GO

IF OBJECT_ID('t_deleted_check') IS NOT NULL DROP TABLE t_deleted_check
GO
CREATE TABLE t_deleted_check ( SEQ INT, joinkey TINYINT, value NVARCHAR(MAX) ) -- t_deleted_check TABLE
GO
CREATE NONCLUSTERED INDEX nix_seq_t_deleted_check ON t_deleted_check ( SEQ, joinkey ) -- INDEX 생성
GO

-- inserted의 데이터 t_inserted_check 테이블로 밀어넣기
DECLARE @tblname NVARCHAR(100)
, @seq NVARCHAR(15)
, @exec NVARCHAR(MAX)
SET @tblname = 'inserted' -- 테이블명
SET @seq = '1' -- SEQ

SELECT DISTINCT @exec = 'INSERT INTO t_inserted_check '
+ 'SELECT ' + @seq + ', number, CASE number' + STUFF(( SELECT ' WHEN ' + CONVERT(VARCHAR,column_id) + ' THEN CONVERT(VARCHAR,a.' + name + ')' AS [text()]
FROM sys.columns
WHERE object_id = a.object_id FOR XML PATH('') ),1,0,'') + ' END '
+ 'FROM ' + object_name(object_id)
+ ' a CROSS JOIN master.dbo.spt_values WHERE type = ''p'' AND number BETWEEN 1 AND '
+ CONVERT(VARCHAR,COUNT(CONVERT(VARCHAR,column_id)) OVER ( PARTITION BY ( SELECT 0 ) ))
+ ' AND a.SEQ = ' + @seq -- SEQ
FROM sys.columns a
WHERE object_name(object_id) = @tblname -- 테이블명
EXECUTE sp_executesql @exec
GO

-- deleted의 데이터 t_deleted_check 테이블로 밀어넣기
DECLARE @tblname NVARCHAR(100)
, @seq NVARCHAR(15)
, @exec NVARCHAR(MAX)
SET @tblname = 'deleted' -- 테이블명
SET @seq = '1' -- SEQ

SELECT DISTINCT @exec = 'INSERT INTO t_deleted_check '
+ 'SELECT ' + @seq + ', number, CASE number' + STUFF(( SELECT ' WHEN ' + CONVERT(VARCHAR,column_id) + ' THEN CONVERT(VARCHAR,a.' + name + ')' AS [text()]
FROM sys.columns
WHERE object_id = a.object_id FOR XML PATH('') ),1,0,'') + ' END '
+ 'FROM ' + object_name(object_id)
+ ' a CROSS JOIN master.dbo.spt_values WHERE type = ''p'' AND number BETWEEN 1 AND '
+ CONVERT(VARCHAR,COUNT(CONVERT(VARCHAR,column_id)) OVER ( PARTITION BY ( SELECT 0 ) ))
+ ' AND a.SEQ = ' + @seq -- SEQ
FROM sys.columns a
WHERE object_name(object_id) = @tblname -- 테이블명
EXECUTE sp_executesql @exec
GO

/*
-- 어떤값이 다른지 확인하기 위해 임의의 데이터를 업데이트 한다.
-- 확인하려면 주석풀고..
UPDATE t_inserted_check
SET value = 'AAAA'
WHERE SEQ = 1
AND joinkey IN ( 5, 7 )
GO
*/

-- 다른값 비교하기
SELECT c.name, a.value, b.value
FROM t_deleted_check a
INNER JOIN t_inserted_check b
ON a.SEQ = b.SEQ
AND a.joinkey = b.joinkey
AND a.SEQ = 1 -- seq
INNER JOIN
(
SELECT ROW_NUMBER() OVER ( ORDER BY column_id ) AS joinkey, name
FROM sys.columns
WHERE object_name(object_id) = 'inserted' -- 테이블명
) c
ON a.joinkey = c.joinkey
AND a.value <> b.value -- 해당부분 주석시 전체 확인 가능
GO

IF OBJECT_ID('[log]') IS NOT NULL
DROP TABLE [log]
GO

CREATE TABLE [log] ( ETC NVARCHAR(MAX) )
GO

INSERT INTO [log]
SELECT c.name + ':' + a.value + ' > ' + b.value
FROM t_deleted_check a
INNER JOIN t_inserted_check b
ON a.SEQ = b.SEQ
AND a.joinkey = b.joinkey
AND a.SEQ = 1 -- seq
INNER JOIN
(
SELECT ROW_NUMBER() OVER ( ORDER BY column_id ) AS joinkey, name
FROM sys.columns
WHERE object_name(object_id) = 'inserted' -- 테이블명
) c
ON a.joinkey = c.joinkey
AND a.value <> b.value -- 해당부분 주석시 전체 확인 가능
GO

SELECT * FROM [log]
GO

cmd_comment_vote_user Upvote0 Downvote0

No.	Subject	Author	Date	Views
Notice	SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트)	코난(김대우)	2023.08.18	33594
Notice	Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트)	코난(김대우)	2021.01.01	16948
8143	웹환경과 관리툴환경에서의 쿼리문 실행 [3]	taz2315	2014.08.27	2816
8142	SQL Server Architecture 및 내부 동작원리에 대하여 [1]	HoyaSoft	2014.08.26	3800
8141	접근제어 때문에 질문드립니다. [1]	왕만두와우동	2014.08.26	2445
8140	DB 다른컴퓨터에 두고 붙이기 가능한가요? [4]	파파곰	2014.08.26	2800
8139	Left Outer Join 질문입니다. [2]	희망나라	2014.08.26	2827
8138	SQL2008 R2 대용량 SQL 유지관리 (Maintenance Plan) [2]	송동운	2014.08.26	5008
8137	모든 테이블 내용 지우기 [2]	백마탄나	2014.08.22	3410
8136	not in 쿼리 질문드립니다! [4]	파파곰	2014.08.22	2758
8135	데이터베이스 연결 오류 [2]	dewrain	2014.08.22	7343
8134	UNION문으로 합친 테이블을 구분하고 싶습니다ㅠㅠ제발살려주세여 [1]	으아코딩어렵	2014.08.22	3391
8133	bigint끼리 더했는데 결과가 NULL입니다. [2]	연어초밥	2014.08.21	2396
8132	데이터 베이스 질문 있습니다. [1]	청소하자	2014.08.21	2108
8131	SQL Log 처리 - procedure VS trigger! [6]	전설의코더	2014.08.21	3134
»	두 테이블 값 비교하여 변경된 컬럼을 알아내는 쿼리 질문드립니다. [5]	he2ng	2014.08.20	9795
8129	쿼리문 질문 [1]	hhhh	2014.08.19	2400
8128	날짜 형변환 convert 질문좀부탁드려요 [1]	아싸헛스윙	2014.08.19	6901
8127	2000 , 2005 32bit 를 2008 64비트 복원이 가능한가요? [7]	김영환_281520	2014.08.19	5040
8126	쿼리 오류나는데 좀 봐주세요...ㅠ [2]	dewrain	2014.08.19	2219
8125	lined server 로 연결된 서버의 저장프로시저 호출 후 select 가 가능할까요? [8]	taz2315	2014.08.19	7656
8124	어떤 SELECT문일까요?ㅜㅜ [2]	마힐링	2014.08.18	2621

Write Tag

First Page 137 138 139 140 141 142 143 144 145 146 Last Page

두 테이블 값 비교하여 변경된 컬럼을 알아내는 쿼리 질문드립니다.

Comment 5

무념

he2ng

처리짱

he2ng

원론

커뮤니티

데이터베이스 개발

클라우드 개발

머신러닝/AI개발

파이썬&오픈소스 개발