SQL 서버 객체 변경이력 자동 저장.
원문 : https://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/
지원대상 이벤트는 아래와 같습니다.
CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,CREATE_VIEW,ALTER_VIEW, DROP_VIEW,CREATE_FUNCTION,ALTER_FUNCTION,DROP_FUNCTION,CREATE_DEFAULT, DROP_DEFAULT, CREATE_TYPE, DROP_TYPE
-- Database 별로 실행해야 함.
use wacore_main;
go
create TABLE DDLEvents
(
EventDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
EventType NVARCHAR(64),
EventDDL NVARCHAR(MAX),
EventXML XML,
DatabaseName NVARCHAR(255),
SchemaName NVARCHAR(255),
ObjectName NVARCHAR(255),
HostName VARCHAR(64),
IPAddress VARCHAR(32),
ProgramName NVARCHAR(255),
LoginName NVARCHAR(255)
);
go
--일단 SP들만 등록. 나머지 객체들은 필요에 따라.
insert DDLEvents
(
EventType,
EventDDL,
DatabaseName,
SchemaName,
ObjectName,
LoginName
)
SELECT
'CREATE_PROCEDURE',
OBJECT_DEFINITION([object_id]),
DB_NAME(),
OBJECT_SCHEMA_NAME([object_id]),
OBJECT_NAME([object_id]),
'my name'
FROM
sys.procedures;
go
create TRIGGER DDLTrigger
ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,CREATE_VIEW,ALTER_VIEW, DROP_VIEW,CREATE_FUNCTION,ALTER_FUNCTION,DROP_FUNCTION,CREATE_DEFAULT, DROP_DEFAULT, CREATE_TYPE, DROP_TYPE
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@EventData XML = EVENTDATA();
DECLARE
@ip VARCHAR(32) =
(
SELECT client_net_address
FROM sys.dm_exec_connections
WHERE session_id = @@SPID
);
INSERT DDLEvents
(
EventType,
EventDDL,
EventXML,
DatabaseName,
SchemaName,
ObjectName,
HostName,
IPAddress,
ProgramName,
LoginName
)
SELECT
@EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
@EventData,
DB_NAME(),
@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(255)'),
@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)'),
HOST_NAME(),
@ip,
PROGRAM_NAME(),
SUSER_SNAME();
END
GO
댓글
댓글 쓰기