Auditing tables can be a complicated procedure if you require a new table for every table being audited. Fortunately the new XML datatype can help to automate this procedure of auditing multiple tables into a single table. The following trigger template can be used to audit a table into a single table. First let’s create the table needed to hold the audited values: [cc lang=”sql”] CREATE TABLE [Log].[AuditTableHistory]( [HistoryID] [int] IDENTITY(1,1) NOT NULL, [TableSchema] [varchar](10) NULL, [TableName] [varchar](100) NULL, [AuditValue] [xml] NULL, [DateCreated] [datetime] NULL, CONSTRAINT [PK_AUDITTABLEHISTORY] PRIMARY KEY CLUSTERED ( [HistoryID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY] ) ON [PRIMARY] — Now let’s create the trigger CREATE TRIGGER [Audit].[Trg_Schema_Table] ON [Schema].[Table] AFTER UPDATE,DELETE AS BEGIN — SET NOCOUNT ON added to prevent extra result sets from — interfering with SELECT statements. SET NOCOUNT ON; DECLARE @AuditValue xml SET @AuditValue = ( SELECT TOP 1 * FROM DELETED FOR XML AUTO ) INSERT INTO Log.AuditTableHistory ( AuditValue, TableSchema, TableName, DateCreated ) SELECT AuditValue = @AuditValue, TableSchema = OBJECT_SCHEMA_NAME(parent_obj), TableName = OBJECT_NAME(parent_obj), DateCreated = GETDATE() FROM sysobjects so WHERE so.id = @@PROCID END GO [/cc] We can apply this trigger to any table we want just by changing the “ON” statement. It will work for any table. The only difficulty comes in when we change the schema for a particular table. When this happens, we need to know the date/time the table schema changed so […]
Continue reading ...
When I first heard of the XML Datatype, I had mixed feelings. The reason is because from a traditional approach, all data structures can be represented using the classical entity relationship model. For me, a problem arises when trying to represent “documents”. When I say documents, I don’t just mean things like “Word Docs”. What I mean by that is data structures that mean nothing when the data is broken up into segments, however when represented in a whole document, that’s where the only relevance comes in. An example of this is a contract. It is possible to take disparate pieces of a contract and break it into separate rows and columns according to the contract terms. However, when you break a contract up into individual pieces, it means nothing when it’s separate. It’s context is only meaningful when the pieces of the contract are together. This is an example of when to use the XML Datatype. I recently wrote a rules engine, where I created all the rules using XML. When I showed some people, their immediate reaction was, “why can’t we break these individual parts of the rule into a table”. My answer was, “because separated, these individual rule items mean nothing, there is no reason to separate them. They only mean something when they are put together because that defines the rule”. In other words, the entire rule is atomic, there is no reason whatsoever to break it apart. When it is separate it has no meaning. […]
Continue reading ...
This neat and simple little trick will help to transform each row in a table or query into an XML row. The simplest way is to use the FOR XML clause. However if you want to return the XML as a separate column in the table, you need to perform a self-join on the table itself. [cc lang=”sql”] SELECT TOP 100 EmployeeID, EmployeeXML = ( SELECT EmpLastName = LastName ,e_xml.* FROM dbo.Employee e_xml WHERE e_xml.EmployeeID = emp.EmployeeID FOR XML PATH (”) ) FROM dbo.Employee emp [/cc] The EmpLastName assignment shows how you can assign your own custom XML tags based on each column.
Continue reading ...
XQuery in SQL Server was introduced in version 2005, along with so many major advancements. The ability to store and query XML has changed the game of SQL Server and has made it what it more of what it should be, a better workflow / storage engine. There are some things to consider prior to introducing XML into the database. The most plausible question you can ask yourself when using XML instead of a table is, “do these XML elements have any use as individual items, or do they only have meaning within the context of this XML document”. Weighing that question, along with the ease of XML portability will hopefully give you the answer you need. Now to use XQuery. SQL Server only supports a subset of XQuery so you don’t want to get too fancy with your needs. There are certain functions (like substring) that are not supported, however most of the missing functionality can be addressed using SQL Server functions. If you do need these added functionalities, then you also may want to consider whether the XML manipulation using SQL is the right approach. While XML is quite useful, and while SQL Server can (architecturally) be used as a workflow engine, when it comes to certain items like display functions, SQL Server is not the right choice. This following example is from a rules engine I am creating. The XML itself represents a rule, and the XQuery below parses the rule into a table. The beauty of […]
Continue reading ...
