반응형
SQL Server에서는 Trigger 내부에서만 사용할 수 있는 특수한 함수들을 소개하려고 합니다. 이러한 기능을 사용하면 접하기 쉽지 않은 고급 정보를 얻을 수 있습니다.
지원되는 Trigger Function 함수는?
- UPDATE() Function에는 열의 변경을 알 수 있습니다.
- COLUMNS_UPDATED() Function은 여러 열을 확인할 때 주로 사용합니다.
- NESTLEVEL() Function에는 중첩 trigger 수준을 확인할 때 사용합니다.
- EVENTDATA() Function은 실행 정보를 알 수 있습니다. Login Trigger에서 많이 사용합니다.
Trigger Function 특징 및 사용법.
The UPDATE() Function.
- UPDATE() 사용하면은 UPDATE 또는 INSERT 문에 의해 어떤 열이 변경되었는지 알 수 있습니다.
- 즉, UPDATE() Function은 수정된 열을 찾는 데 많은 도움이 됩니다.
- UPDATE() Function에는 Trigger와 관련된 테이블이나 뷰의 열 이름인 하나의 매개변수만 허용합니다.
- UPDATE() Function을 사용하면 IF나 WHILE과 같은 조건문에 사용할 수 있도록 (TRUE = 1; FALSE = 0)로 지원합니다.
UPDATE() Function 소스 코드입니다.
-- UPDATE() Function
DROP TABLE IF EXISTS AurumGuide_UPDATE_Function;
CREATE TABLE AurumGuide_UPDATE_Function
(
AurumId INT PRIMARY KEY,
AurumNm VARCHAR(255) NULL,
AurumAge INT NULL,
AurumAddress VARCHAR(500) NULL
)
GO
DROP TABLE IF EXISTS AurumGuide_UPDATE_Function_Log;
CREATE TABLE AurumGuide_UPDATE_Function_Log
(
AurumGuide_Log varchar(100)
);
GO
INSERT INTO dbo.AurumGuide_UPDATE_Function(AurumId,AurumNm)
VALUES (274, N'Stephen')
,(275, N'Michael')
,(276, N'Linda');
GO
CREATE OR ALTER TRIGGER TR_AurumGuide_UPDATE_Function
ON dbo.AurumGuide_UPDATE_Function
AFTER UPDATE,INSERT
AS
IF UPDATE(AurumNm)
BEGIN
INSERT INTO AurumGuide_UPDATE_Function_Log
(AurumGuide_Log) VALUES ('AurumNm was updated');
END
IF UPDATE(AurumAge)
BEGIN
INSERT INTO AurumGuide_UPDATE_Function_Log
(AurumGuide_Log) VALUES ('AurumAge was updated');
END
IF UPDATE(AurumAddress)
BEGIN
INSERT INTO AurumGuide_UPDATE_Function_Log
(AurumGuide_Log) VALUES ('AurumAddress was updated');
END
GO
UPDATE dbo.AurumGuide_UPDATE_Function
SET AurumAge = 100
WHERE AurumId = 274
SELECT *
-- DELETE
FROM AurumGuide_UPDATE_Function;
SELECT *
-- DELETE
FROM AurumGuide_UPDATE_Function_Log;
The COLUMNS_UPDATED() Function.
- UPDATE() 함수는 어떤 열이 변경되었는지 파악하는 데 유용하지만 여러 열을 확인해야 할 때는 뭔가 부족합니다.
- 이를 보완하기 위해 COLUMNS_UPDATED() Function을 추가로 지원하고 있습니다.
- COLUMNS_UPDATED 함수는 매개변수는 없고, bitmask를 사용하여 여러 열을 테스트할 수 있는 VARBINARY 스트림을 반환합니다.
COLUMNS_UPDATED() Function 소스 코드입니다.
-- COLUMNS_UPDATED() Function
GO
DROP TABLE IF EXISTS AurumGuide_COLUMNS_UPDATED_Function;
CREATE TABLE AurumGuide_COLUMNS_UPDATED_Function
(
AurumId INT PRIMARY KEY,
AurumNm VARCHAR(255) NULL,
AurumAge INT NULL,
AurumAddress VARCHAR(500) NULL
)
GO
CREATE OR ALTER TRIGGER TR_AurumGuide_COLUMNS_UPDATED_Function
ON dbo.AurumGuide_COLUMNS_UPDATED_Function
AFTER UPDATE,INSERT
AS
IF COLUMNS_UPDATED() & CAST(0x02 AS int) = 0x02
BEGIN
PRINT 'AurumNm was changed'
END
IF COLUMNS_UPDATED() & CAST(0x04 AS int) = 0x04
BEGIN
PRINT 'AurumAge was changed'
END
IF COLUMNS_UPDATED() & CAST(0x08 AS int) = 0x08
BEGIN
PRINT 'AurumAddress was changed'
END
GO
INSERT INTO dbo.AurumGuide_COLUMNS_UPDATED_Function(AurumId,AurumNm)
VALUES (274, N'Stephen')
,(275, N'Michael')
,(276, N'Linda');
GO
UPDATE dbo.AurumGuide_COLUMNS_UPDATED_Function
SET AurumAge = 100
WHERE AurumId = 274
;
The TRIGGER_NESTLEVEL() Function.
- TRIGGER_NESTLEVEL() 함수는 DML 및 DDL Trigger 내에서 중첩 정보를 반환합니다.
- Trigger의 객체 ID를 지정하면 이 함수는 허용된 세 가지 매개변수가 해당 Trigger의 중첩 수준을 반환합니다.
TRIGGER_NESTLEVEL() Function 소스 코드입니다.
-- NESTLEVEL() Function
DROP TABLE IF EXISTS AurumGuide_Nested_Trigger_1;
-- Create Sample Table
CREATE TABLE AurumGuide_Nested_Trigger_1 (
AurumId INT NOT NULL,
AurumNm VARCHAR(255) NOT NULL,
AurumAge INT NULL,
AurumAddress VARCHAR(500) NULL
);
DROP TABLE IF EXISTS AurumGuide_Nested_Trigger_2;
CREATE TABLE AurumGuide_Nested_Trigger_2 (
AurumId INT NOT NULL,
AurumNm VARCHAR(255) NOT NULL,
AurumAge INT NULL,
AurumAddress VARCHAR(500) NULL
);
DROP TABLE IF EXISTS AurumGuide_Nested_Trigger_3;
CREATE TABLE AurumGuide_Nested_Trigger_3 (
AurumId INT NOT NULL,
AurumNm VARCHAR(255) NOT NULL,
AurumAge INT NULL,
AurumAddress VARCHAR(500) NULL
);
--Trigger
GO
CREATE OR ALTER TRIGGER TR_AurumGuide_Nested_Trigger_1
ON AurumGuide_Nested_Trigger_1
FOR INSERT
AS
BEGIN
INSERT INTO AurumGuide_Nested_Trigger_2
SELECT * FROM inserted;
END;
GO
CREATE OR ALTER TRIGGER TR_AurumGuide_Nested_Trigger_2
ON AurumGuide_Nested_Trigger_2
FOR INSERT
AS
BEGIN
INSERT INTO AurumGuide_Nested_Trigger_3
SELECT * FROM inserted;
PRINT 'Nesting level for trigger TR_AurumGuide_Nested_Trigger_2: ' + CAST(TRIGGER_NESTLEVEL(OBJECT_ID('TR_AurumGuide_Nested_Trigger_2', 'TR')) AS VARCHAR(2));
PRINT 'Number of AFTER Triggers on stack: ' + CAST(TRIGGER_NESTLEVEL(0,'AFTER','DML') AS VARCHAR(2));
PRINT 'Trigger Nest Level: ' + CAST(TRIGGER_NESTLEVEL() AS VARCHAR(2));
END;
go
-- Create Sample Data
INSERT INTO dbo.AurumGuide_Nested_Trigger_1(AurumId,AurumNm)
VALUES (272, N'Ken')
-- Check Data
SELECT * FROM AurumGuide_Nested_Trigger_1;
SELECT * FROM AurumGuide_Nested_Trigger_2;
SELECT * FROM AurumGuide_Nested_Trigger_3;
The EVENTDATA() Function for Triggers.
- EVENTDATA() 함수는 DDL 또는 로그온 Trigger를 실행한 이벤트에 대한 정보를 제공합니다.
- 물론 Trigger 콘텍스트 내에서 실행될 때만 작동하고 그렇지 않으면 NULL을 반환합니다.
- XML 형태로 지원되며 파일로 저장해서 정보를 확인할 수도 있지만 Table에서 사용할 수 있습니다.
- 추가로 https://aurumguide.tistory.com/60 참조하세요.
EVENTDATA() Function 소스 코드입니다.
-- EVENTDATA() Function
-- 테이블 변경 이력.
DROP TABLE IF EXISTS AurumGuide_EVENTDATA_Trigger_Log;
CREATE TABLE dbo.AurumGuide_EVENTDATA_Trigger_Log(
LogID int IDENTITY(1,1) PRIMARY KEY,
ChangeId varchar(100),
DateChanged datetime,
EventInfo xml NULL
);
GO
-- 트리거 생성
CREATE OR ALTER TRIGGER TR_AurumGuide_EVENTDATA_Trigger_Log
ON DATABASE
FOR
CREATE_TABLE,
ALTER_TABLE,
DROP_TABLE
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.AurumGuide_EVENTDATA_Trigger_Log
(ChangeId, DateChanged, EventInfo)
VALUES
(USER,GETDATE(),EVENTDATA());
END;
GO
-- 테이블 변경
USE sampleDB;
DROP TABLE IF EXISTS AurumGuide_EVENTDATA_Trigger;
-- Create Sample Table
CREATE TABLE AurumGuide_EVENTDATA_Trigger(
AurumId varchar(100),
loginTime datetime
);
go
-- 변경이력 확인
SELECT *
FROM AurumGuide_EVENTDATA_Trigger_Log
반응형
