SQL - custom log schema via XML

It has been a long time since my last post: After took my holiday for about one month, it is just so difficult to get myself ready for work :(

But anyway, when I came back immediately I found there were quite a lot works need to be settled: We are changing our whole dialler to a brand new one; we are having some new opportunities from both internal and externals; we are planning to update our data platforms, such as ETL management frameworks, etc, etc.

When I reviewed our team's old works, I found below codes and just want to log here. I could not find the original source of the codes, but if my memory is correct, the credit of the codes should be to one of the SQL books. (professional SQL Server 20xx, probably) So if you know where the source of below codes is, please leave me a message so I can link to codes to original source.

The purpose of below codes is to log DML actions via XML and table triggers. Basically we will turn insert and update actions into xml format through the table trigger, and then log into the DML log table.

Now firstly we have table structure like below

 Create table SysLog.DMLLog  
 (  
 LogKey bigint identity(1,1),  
 TableName varchar(255),  
 TriggerName varchar(255),  
 SpID int,  
 Username varchar(255),  
 DMLAction XML,  
 CreatedOn datetime,  
 Constraint PK_DMLLog primary key(LogKey)  
 )  
 go  

Fields we created are self-explanatory, so I won't spend time to explain it :p.

Now it is time about the trigger, assume we are going to build the trigger on Account table. Something like below:

 Create Trigger TriLogAccountDML   
 On Account  
 After insert, update, delete  
 as  
 BEGIN  
      DECLARE @XmlAction XML, @TriID int, @TblID int  

      SELECT @XmlAction = ISNULL((SELECT * FROM inserted FOR XML AUTO), '<inserted/>') + ISNULL((SELECT * FROM deleted FOR XML AUTO), '<deleted/>') 
 
      SELECT @TriID = @@PROCID  
      SELECT @TblID = parent_id FROM sys.triggers WHERE object_id = @TriID  

      INSERT INTO SysLog.DMLLog(TableName, TriggerName, SpID, Username, DMLAction, CreatedOn)  
      SELECT OBJECT_NAME(@TblID), OBJECT_NAME(@TriID), @@SPID, SYSTEM_USER, @XmlAction, GETDATE();  
 END  

Now we are done. In the end we will have our log table like below:

Quite simple isn't it? :D