Create trigger to log activities
- Record actions happening on CREATE/ALTER/DELETE stored proc
Create table to save actions happening[sql]
CREATE TABLE dbo.ProcedureChanges
(
EventDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
EventType VARCHAR(100),
EventDDL VARCHAR(MAX),
DatabaseName VARCHAR(255),
SchemaName VARCHAR(255),
ObjectName VARCHAR(255),
HostName VARCHAR(255),
IPAddress VARCHAR(32),
ProgramName VARCHAR(255),
LoginName VARCHAR(255)
);
[/sql]Save initial content of stored procedures, functions
[sql]
INSERT ProcedureChanges
(
EventType,
EventDDL,
DatabaseName,
SchemaName,
ObjectName
)
SELECT
N’Initial control’,
ROUTINE_DEFINITION,
ROUTINE_CATALOG,
SPECIFIC_SCHEMA,
ROUTINE_NAME
FROM
INFORMATION_SCHEMA.ROUTINES
[/sql]
or
[sql]
INSERT Utility.dbo.ProcedureChanges
(
EventType,
EventDDL,
DatabaseName,
SchemaName,
ObjectName
)
SELECT
N’Initial control’,
OBJECT_DEFINITION([object_id]),
DB_NAME(),
OBJECT_SCHEMA_NAME([object_id]),
OBJECT_NAME([object_id])
FROM
sys.procedures;
[/sql]Create trigger to record actions relating to Stored Procedure
[sql]
CREATE TRIGGER CaptureStoredProcedureChanges
ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
BEGIN
SET NOCOUNT ON;DECLARE @EventData XML = EVENTDATA(), @ip VARCHAR(32);
SELECT @ip = client_net_address
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;INSERT ProcedureChanges
(
EventType,
EventDDL,
SchemaName,
ObjectName,
DatabaseName,
HostName,
IPAddress,
ProgramName,
LoginName
)
SELECT
@EventData.value(‘(/EVENT_INSTANCE/EventType)[1]’, ‘VARCHAR(100)’),
@EventData.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]’, ‘VARCHAR(MAX)’),
@EventData.value(‘(/EVENT_INSTANCE/SchemaName)[1]’, ‘VARCHAR(255)’),
@EventData.value(‘(/EVENT_INSTANCE/ObjectName)[1]’, ‘VARCHAR(255)’),
DB_NAME(), HOST_NAME(), @ip, PROGRAM_NAME(), SUSER_SNAME();
END
[/sql]You can create trigger to record actions on function as well
[sql]
CREATE TRIGGER CaptureFunctionChanges
ON DATABASE
FOR CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION
AS
BEGIN
SET NOCOUNT ON;DECLARE @EventData XML = EVENTDATA(), @ip VARCHAR(32);
SELECT @ip = client_net_address
FROM sys.dm_exec_connections
WHERE session_id = @@SPIDINSERT ProcedureChanges
(
EventType,
EventDDL,
SchemaName,
ObjectName,
DatabaseName,
HostName,
IPAddress,
ProgramName,
LoginName
)
SELECT
@EventData.value(‘(/EVENT_INSTANCE/EventType)[1]’, ‘VARCHAR(100)’),
@EventData.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]’, ‘VARCHAR(MAX)’),
@EventData.value(‘(/EVENT_INSTANCE/SchemaName)[1]’, ‘VARCHAR(255)’),
@EventData.value(‘(/EVENT_INSTANCE/ObjectName)[1]’, ‘VARCHAR(255)’),
DB_NAME(), HOST_NAME(), @ip, PROGRAM_NAME(), SUSER_SNAME();
END[/sql] - DDL Trigger list
http://www.sqlteam.com/article/using-ddl-triggers-in-sql-server-2005-to-capture-schema-changes - How to list up DB triggers
[sql]select * from sys.triggers order by create_date desc[/sql]
Leave a Reply