{"id":2015,"date":"2012-05-24T15:36:50","date_gmt":"2012-05-24T15:36:50","guid":{"rendered":"http:\/\/craftydba.com\/?p=2015"},"modified":"2013-01-23T22:01:30","modified_gmt":"2013-01-23T22:01:30","slug":"various-triggers-%e2%80%93-part-5","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=2015","title":{"rendered":"Various Triggers \u2013 Part 5"},"content":{"rendered":"

\"\"<\/a>Another usage of Database Triggers is track Data Definition Language (DDL<\/a>) changes that occur in a database. Today, I am going to enhance the AUTOS database script<\/a> to add such auditing. This auditing is a very good practice when multiple people have syadmin rights to make such changes.<\/p>\n

First, I am going to create a schema<\/a> called Audit Database Tracking (ADT) to seperate the data tables from audit tracking tables.<\/p>\n

-- Delete existing schema.\r\nIF EXISTS (SELECT * FROM sys.schemas WHERE name = N'ADT')\r\nDROP SCHEMA [ADT];\r\nGO\r\n\r\n-- Create a ADT schema\r\nCREATE SCHEMA [ADT] AUTHORIZATION dbo;\r\nGO\r\n<\/span><\/pre>\n<\/p>\n
Second, I am going to create a table<\/a> to hold the keep track of the DDL changes that are occuring to the database.<\/p>\n
\/*  \r\n\tCreate schema level auditing - table.\r\n*\/\r\n\r\n-- Remove table if it exists\r\nIF  EXISTS (SELECT * FROM sys.objects WHERE object_id = \r\n    OBJECT_ID(N'[ADT].[LOG_DDL_CHANGES]') AND type in (N'U'))\r\nDROP TABLE [ADT].[LOG_DDL_CHANGES]\r\nGO\r\n\r\n-- Create a DDL Log Changes table\r\nCREATE TABLE [ADT].[LOG_DDL_CHANGES]\r\n(\r\n\t[ChangeId] BIGINT IDENTITY(1,1) NOT NULL,\r\n\t[ChangeDate] [datetime] NOT NULL,\r\n\t[ChangeType] [varchar](20) NOT NULL,\r\n\t[ChangeBy] [nvarchar](256) NOT NULL,\r\n\t[AppName] [nvarchar](128) NOT NULL,\r\n\t[HostName] [nvarchar](128) NOT NULL,\r\n\t[SchemaName] [sysname] NULL,\r\n\t[ObjectName] [sysname] NULL,\r\n\t[Tsql] nvarchar(MAX) NULL,\r\n\r\n        CONSTRAINT [pk_Ldc_ChangeId] PRIMARY KEY CLUSTERED ([ChangeId] ASC)\r\n) \r\nGO\r\n\r\n-- Add defaults for key information\r\nALTER TABLE [ADT].[LOG_DDL_CHANGES] \r\n    ADD CONSTRAINT [df_Ldc_ChangeDate] DEFAULT (getdate()) FOR [ChangeDate];\r\n\r\nALTER TABLE [ADT].[LOG_DDL_CHANGES] \r\n    ADD CONSTRAINT [df_Ldc_ChangeType] DEFAULT ('') FOR [ChangeType];\r\n\r\nALTER TABLE [ADT].[LOG_DDL_CHANGES] \r\n    ADD CONSTRAINT [df_Ldc_ChangeBy] DEFAULT (coalesce(suser_sname(),'?')) FOR [ChangeBy];\r\n\r\nALTER TABLE [ADT].[LOG_DDL_CHANGES] \r\n    ADD CONSTRAINT [df_Ldc_AppName] DEFAULT (coalesce(APP_NAME(),'?')) FOR [AppName];\r\n\r\nALTER TABLE [ADT].[LOG_DDL_CHANGES] \r\n    ADD CONSTRAINT [df_Ldc_HostName] DEFAULT (coalesce(HOST_NAME(),'?')) FOR [HostName];\r\nGO\r\n<\/span><\/pre>\n<\/p>\n
Third, I am going to create a database trigger<\/a> to capture the event data and add entries to the tracking table.<\/p>\n
\/*  \r\n\tCreate schema level auditing - trigger.\r\n*\/\r\n\r\n-- Remove trigger if it exists\r\nIF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[TRG_TRACK_DDL_CHANGES]'))\r\nDROP TRIGGER [TRG_TRACK_DDL_CHANGES]\r\nGO\r\n\r\n-- Add the trigger to the db\r\nCREATE TRIGGER [TRG_TRACK_DDL_CHANGES] ON DATABASE \r\nFOR \r\n  DDL_TRIGGER_EVENTS, DDL_FUNCTION_EVENTS, DDL_PROCEDURE_EVENTS, DDL_TABLE_VIEW_EVENTS, DDL_DATABASE_SECURITY_EVENTS \r\nAS \r\nBEGIN\r\n\r\n    -- Declare local variables\r\n    DECLARE @VAR_DATA XML;\r\n    DECLARE @VAR_SCHEMA sysname;\r\n    DECLARE @VAR_OBJECT sysname; \r\n    DECLARE @VAR_EVENT_TYPE sysname;\r\n\r\n    -- Set local variables\r\n    SET @VAR_DATA = EVENTDATA();\r\n    SET @VAR_EVENT_TYPE = @VAR_DATA.value('(\/EVENT_INSTANCE\/EventType)[1]', 'sysname');\r\n    SET @VAR_SCHEMA = @VAR_DATA.value('(\/EVENT_INSTANCE\/SchemaName)[1]', 'sysname');\r\n    SET @VAR_OBJECT = @VAR_DATA.value('(\/EVENT_INSTANCE\/ObjectName)[1]', 'sysname') \r\n\r\n    -- Do not track replication or statistics\r\n    IF (( @VAR_EVENT_TYPE <> 'CREATE_STATISTICS') and ( @VAR_OBJECT not like 'syncobj_0x%')) \r\n        BEGIN\r\n\t\tINSERT [ADT].[LOG_DDL_CHANGES] ([ObjectName], [ChangeType], [Tsql], [SchemaName]) VALUES \r\n\t\t( \r\n\t\t    CONVERT(sysname, @VAR_OBJECT), \r\n\t\t    @VAR_EVENT_TYPE, \r\n\t\t    @VAR_DATA.value('(\/EVENT_INSTANCE\/TSQLCommand)[1]', 'nvarchar(max)'), \r\n\t\t    CONVERT(sysname, @VAR_SCHEMA)\r\n\t\t );\r\n        END\r\n\r\nEND;\r\n<\/span><\/pre>\n<\/p>\n
The EVENTDATA()<\/a> function, only available for Server and Database DDL triggers, returns XML data is key to extracting information about the event.  The FOR clause of the database trigger can narrow down what events<\/a> you want to track.  Last but not least, a database developer needs to be aware of other events such as replication that might not wanted to be tracked.<\/p>\n
The image below shows what is captured in the audit table after a couple tables, triggers and stored procedures are created.<\/p>\n
\"\"<\/a><\/p>\n
In summary, database triggers (AFTER) can be used to keep track of SCHEMA changes.  Since the number of SCHEMA changes on a mature database are small, this is a great way to find out who did what when there is a production issue.  <\/p>\n
In SQL Server 2008, a built in Database Auditing mechanism was introduced.  We will explore this feature in future articles.<\/p>\n","protected":false},"excerpt":{"rendered":"
Another usage of Database Triggers is track Data Definition Language (DDL) changes that occur in a database. Today, I am going to enhance the AUTOS database script to add such auditing. This auditing is a very good practice when multiple people have syadmin rights to make such changes. First, I am going to create a schema called Audit Database Tracking (ADT) to seperate the data tables from audit tracking tables. — Delete existing schema. IF EXISTS (SELECT * FROM sys.schemas WHERE name = N’ADT’) DROP SCHEMA [ADT]; GO — Create…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[396,398,317,391,428,429,12,15,28,390,29],"class_list":["post-2015","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-after","tag-alter-trigger","tag-create-trigger","tag-ddl","tag-drop-trigger-eventdata","tag-events","tag-free-code","tag-john-f-miner-iii","tag-sql-server","tag-triggers","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/2015","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2015"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/2015\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2015"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2015"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2015"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}