오픈쿼리 특성 상 테이블변수를 사용하지 못하기에 WHERE 절에서 IN 으로 조회하게 되는데
대상이 많아지면 8000자를 넘는일이 자주 발생한다.
예제
DECLARE @V_TABLE TABLE
(
ORD_NO INT ,
ORD_NAME VARCHAR(30)
)
DECLARE @QUERY NVARCHAR(MAX)
DECLARE @MULTI_ORD_NO NVARCHAR(MAX)
-- 1. 대상 테이블로 받을 때 행 별 콤마 추가
-- ex) 1,2,3
SELECT @MULTI_ORD_NO = STRING_AGG(CAST(ORD_NO AS NVARCHAR(MAX)), ',') FROM (SELECT ORD_NO FROM @V_TABLE) A
SET @QUERY = N'
SELECT 1 FROM A WHERE KEY IN ('+ @MULTI_ORD_NO +')
'
EXECUTE (@QUERY) AT LINKED_SERVER
SELECT * FROM USERS
ORDER BY
CASE
WHEN USER_ST = 3 THEN 0
WHEN USER_ST = 2 THEN 1
WHEN USER_ST = 1 THEN 3
WHEN USER_ST = 4 THEN 4
ELSE 99
END ASC
위와 같이 CASE 를 사용하여 임의로 순서를 지정해주면 된다.
SELECT * FROM USERS
ORDER BY
CASE
WHEN USER_ST = 3 THEN 0
WHEN USER_ST = 2 THEN 1
WHEN USER_ST = 1 THEN 3
WHEN USER_ST = 4 THEN 4
ELSE 99
END ASC,
CASE
WHEN USER_ID = 'PARK' THEN 0
ELSE 1
END ASC
목표는 USERS 테이블의 [USER_ST] (유저 상태)컬럼 값이 변할 때 이력을 저장하는 것 입니다.
[USER_ST]가 변할 때 USERS_HIS 테이블에 데이터를 삽입하도록 트리거를 작성하면 됩니다.
예제는 다음과 같습니다.
트리거 INSERTED 와 DELTED 시에는 그냥 이력을 삽입하면되니 UPDATED만 예제로 작성하겠습니다.
1. 트리거 쿼리문
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: davi
-- Create date: 210323
-- Description: Insert history when [USERS]'s USER_ST updated
-- =============================================
CREATE TRIGGER [dbo].[TRG_USERS_UPDATED]
ON [dbo].[USERS]
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--USER 테이블에서 이력을 저장할 컬럼 변수 생성
DECLARE
@USER_ID varchar(20),
@HIS_IDX int,
@USER_NM varchar(10),
@USER_ST char(1),
@EDT_DT datetime
--I : INSERTED
--U : UPDATED
--D : DELETED
DECLARE @TRG_ACTION char(1)
--INSERTED
SET @TRG_ACTION = 'I'
--U : UPDATED
--D : DELETED
IF EXISTS(SELECT * FROM deleted)
BEGIN
SET @TRG_ACTION = (CASE WHEN EXISTS (SELECT * FROM inserted) THEN 'U' ELSE 'D' END)
END
--UPDATED 만 작성
IF @TRG_ACTION = 'U'
BEGIN
SELECT
@USER_ID = INS.USER_ID, @USER_NM = INS.USER_NM, @USER_ST = INS.USER_ST, @EDT_DT = INS.EDT_DT
FROM DELETED DEL
JOIN INSERTED INS ON DEL.USER_ID = INS.USER_ID --PK로 JOIN
WHERE
DEL.USER_ID = INS.USER_ID AND
DEL.USER_ST != INS.USER_ST --컬럼 값이 다르면 USER_ST가 변경되었을때이다.
END
IF(@USER_ID IS NOT NULL)
BEGIN
--USERS_HIS 테이블에서 해당 USER_ID의 변경 순서 +1
SELECT @HIS_IDX = ISNULL(MAX(HIS_IDX),0)+1 FROM USERS_HIS WITH(NOLOCK) WHERE USER_ID = @USER_ID
--USERS_HIS 삽입
INSERT INTO USERS_HIS
(USER_ID, HIS_IDX, USER_NM, USER_ST, EDT_DT) VALUES
(@USER_ID, @HIS_IDX, @USER_NM, @USER_ST, @EDT_DT)
END
END
GO
해당 쿼리는 업데이트 후 정보를 이력으로 저장 했습니다.
2. 트리거 검증
1) 검증 쿼리
--변경전
SELECT * FROM USERS
SELECT * FROM USERS_HIS
--USER_ST 및 다른 값 업데이트
UPDATE USERS
SET USER_ST = 2, USER_NM = '이지은', EDT_DT = GETDATE()
WHERE USER_ID = 'davi'
-- USER_ST 업데이트 후
SELECT * FROM USERS
SELECT * FROM USERS_HIS
