We’re wanting to capture audit history on our table(s) in our database. A good way to do this is via triggers.<\/p>\n
Let’s create ourselves an Audit table first, something like this<\/p>\n
\r\nCREATE TABLE [dbo].[Audit](\r\n [Id] [int] IDENTITY(1,1) NOT NULL,\r\n [Operation] [nvarchar](3) NOT NULL,\r\n [DateTime] [datetime] NOT NULL,\r\n [Before] [nvarchar](256) NULL,\r\n [After] [nvarchar](256) NULL,\r\n) ON [PRIMARY]\r\n<\/pre>\nThe Operation<\/em> will be used to store a value from “UPD”, “DEL” and “INS”. In other words the audit record is an Update, Delete or Insert. The DateTime<\/em> is the date and time the operation took place, Before<\/em> is the data before it’s changed, After<\/em> the data it changed to and a Description<\/em> field for a bit more information. <\/p>\n
I’ve not included this field, but most likely we’ll also want to have a Who<\/em> field for who made the change.<\/p>\n
In my little example I have a table named Scale<\/em> which is used to store musical scales. Just for completeness let’s take a look at the Scale table<\/p>\n
\r\nCREATE TABLE [dbo].[Scale](\r\n [Id] [int] IDENTITY(1,1) NOT NULL,\r\n [Name] [nvarchar](256) NOT NULL,\r\n [Intervals] [nvarchar](256) NOT NULL,\r\n [Description] [nvarchar](256) NULL\r\n) ON [PRIMARY]\r\n<\/pre>\nNow I’m just going to add triggers to this one table, however you might have such triggers on multiple tables. We will want to have a trigger for deletion, insertion and updates.<\/p>\n
For the delete trigger we might use something like this<\/p>\n
\r\nALTER TRIGGER [dbo].[ScaleDelete]\r\nON [dbo].[Scale]\r\nFOR DELETE\r\nAS\r\n INSERT INTO dbo.Audit(Operation, DateTime, Before)\r\n SELECT 'DEL', GETDATE(), (select d.Name, d.Description, d.Intervals from deleted d FOR JSON PATH)\r\n FROM deleted\r\n<\/pre>\nFor a deletion we insert the operation, date\/time and a description into the audit table using the deleted<\/em> data.<\/p>\n
For insertions we might have something like this<\/p>\n
\r\nALTER TRIGGER [dbo].[ScaleInsert] \r\nON [dbo].[Scale]\r\nFOR INSERT\r\nAS\r\n INSERT INTO dbo.Audit(Operation, DateTime, After)\r\n SELECT 'INS', GETDATE(), (select i.Name, i.Description, i.Intervals from inserted i FOR JSON PATH)\r\n FROM inserted;\r\n<\/pre>\nIn this case we take the data from the insert<\/em> data and passing parts of this data to the audit table (similar to the deletion).<\/p>\n
For the update trigger we might write something similar to the above, but any time the UPDATE SQL command is called the audit log will be added to, even if there were not changes. It would, therefore, be nice if we only updated the audit table when real changes are detected.<\/p>\n
In the case of an update we get both inserted<\/em> and deleted<\/em> data. So we can check whether the inserted<\/em> differs from deleted<\/em> using the EXCEPT SQL command. Then, only if a difference has been found, will we update the audit table. For fun, we’re also going to turn the deleted and inserted data into JSON and store the whole row to in the Before<\/em> and After<\/em> fields of the audit table.<\/p>\n
\r\nCREATE TRIGGER [dbo].[ScaleUpdate]\r\nON [dbo].[Scale]\r\nAFTER UPDATE\r\nAS\r\nIF EXISTS (\r\n SELECT Name, Description, Intervals FROM inserted\r\n EXCEPT\r\n SELECT Name, Description, Intervals FROM deleted\r\n)\r\nBEGIN\r\n INSERT INTO dbo.Audit(Operation, DateTime, Before, After)\r\n SELECT 'UPD', GETDATE(), \r\n (select d.Name, d.Description, d.Intervals from deleted d FOR JSON PATH), \r\n (select i.Name, i.Description, i.Intervals from inserted i FOR JSON PATH)\r\n FROM inserted i\r\n join deleted d on (i.Id = d.Id)\r\nEND\r\n<\/pre>\nIf you’d prefer to create XML output you can replace JSON PATH<\/em> to XML RAW<\/em>.<\/p>\n
Now to test these we can use<\/p>\n
\r\nDELETE from Scale where Name = 'Chromatic'\r\n\r\nINSERT INTO Scale (Name, Intervals)\r\nVALUES ('Chromatic', '1,1,1,1,1,1,1,1,1,1,1,1')\r\n\r\nUPDATE Scale\r\nSET Intervals = '1,1,1,1,1,1,1,1,1,1,1,2'\r\nWHERE Name = 'Chromatic'\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"We’re wanting to capture audit history on our table(s) in our database. A good way to do this is via triggers. Let’s create ourselves an Audit table first, something like this CREATE TABLE [dbo].[Audit]( [Id] [int] IDENTITY(1,1) NOT NULL, [Operation] [nvarchar](3) NOT NULL, [DateTime] [datetime] NOT NULL, [Before] [nvarchar](256) NULL, [After] [nvarchar](256) NULL, ) ON […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[724],"tags":[],"class_list":["post-10799","post","type-post","status-publish","format-standard","hentry","category-sql-server-2"],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts\/10799","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/comments?post=10799"}],"version-history":[{"count":5,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts\/10799\/revisions"}],"predecessor-version":[{"id":10806,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts\/10799\/revisions\/10806"}],"wp:attachment":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/media?parent=10799"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/categories?post=10799"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/tags?post=10799"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}