What is the easiest way of capturing changes on tables? I was asked this question and what immediately came to my mind was DDL Triggers. Though this is not a new feature of SQL Server, it is still unknown to many and various ways are implemented for capturing changes.
DDL Trigger can be implemented for capturing almost all DDL changes. Here is the code for capturing changes made to tables;
USE AdventureWorks2014; GO -- Creating a table for holding changes CREATE TABLE TableChanges ( ChangedDate datetime, EventType nvarchar(100), TableName nvarchar(100), LoginName nvarchar(100), SQLCommand nvarchar(2000)); GO -- Creating the DDL trigger for tables CREATE TRIGGER CaptureTableChanges ON DATABASE WITH EXECUTE AS 'dbo' FOR ALTER_TABLE, DROP_TABLE AS DECLARE @data XML SET @data = EVENTDATA(); INSERT TableChanges (ChangedDate, EventType, TableName, LoginName, SQLCommand) VALUES (getdate(), @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(100)'), @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(100)'), @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ; GO -- Creating a login and user for testing CREATE LOGIN Jack WITH PASSWORD = 'Pa$$w0rd', CHECK_EXPIRATION = OFF, CHECK_POLICY=OFF; GO CREATE USER Jack FOR LOGIN Jack; GO ALTER ROLE db_ddladmin ADD MEMBER Jack; -- Use a different window for this -- Connect using Jacks credentials and execute ALTER TABLE Production.Product ALTER COLUMN Color nvarchar(20); GO -- Check captured changes SELECT * FROM TableChanges ; GO
No comments:
Post a Comment