Doubt in a piece of code [Transact-SQL database audit].

vValkir

Hi all, Trying to discover an appropiate way to perform the audit of a database, I have discovered a piece of code that seems to be quite appropiate. This is there: http://www.simple-talk.com/sql/database-administration/pop-rivetts-sql-server-faq-no.5-pop-on-the-audit-trail/[^] What matters right now is the following lines of code, which are difficult for me to be understood properly:

-- Get primary key columns for full outer join
SELECT @PKCols = COALESCE(@PKCols + ' and', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk, INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME = @TableName
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = pk.TABLE_NAME
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

This piece of code is placed inside a trigger writen on Transact-SQL (SQLServer) code. I understand more or less what is the programmer trying to get by using these lines, but what I don't understant (probably because I'm quite new on SQLServer) is what are the 'i.' and 'd.' references. By accessing the link you can discover the full code, but I had not been able to discover anythink on the code preceding it. Another part of the code above that is a bit confusing is the following:

@PKCols = COALESCE(@PKCols + ' and', ' on')

COALESCE returns the first non-null expression, but it's difficult for me to understand the meaning of the sentence inside the complete expression shown above. Thanks in advance, any kind of help (links, direct answer...) will be wellcome.

Mycroft Holmes

You need to take the i and d in context of the rest of the query, they probably represent (alias) the inserted and deleted or possibly data records based on the query. The coalesce in this case services the composite (2+ fields) primary key requirement.

Never underestimate the power of human stupidity RAH

vValkir

The code preceding the lines I pasted above are the following:

CREATE TRIGGER tr_trigtest ON trigtest FOR INSERT, UPDATE, DELETE
AS

DECLARE @bit INT ,
@field INT ,
@maxfield INT ,
@char INT ,
@fieldname VARCHAR(128) ,
@TableName VARCHAR(128) ,
@PKCols VARCHAR(1000) ,
@sql VARCHAR(2000),
@UpdateDate VARCHAR(21) ,
@UserName VARCHAR(128) ,
@Type CHAR(1) ,
@PKSelect VARCHAR(1000)

--You will need to change @TableName to match the table to be audited
SELECT @TableName = 'trigtest'

-- date and user
SELECT @UserName = SYSTEM_USER ,
@UpdateDate = CONVERT(VARCHAR(8), GETDATE(), 112)
+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114)

-- Action
IF EXISTS (SELECT * FROM inserted)
IF EXISTS (SELECT * FROM deleted)
SELECT @Type = 'U'
ELSE
SELECT @Type = 'I'
ELSE
SELECT @Type = 'D'

-- get list of columns
SELECT * INTO #ins FROM inserted
SELECT * INTO #del FROM deleted

-- Get primary key columns for full outer join
SELECT @PKCols = COALESCE(@PKCols + ' and', ' on')
+ ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,

          INFORMATION\_SCHEMA.KEY\_COLUMN\_USAGE c
   WHERE   pk.TABLE\_NAME = @TableName
   AND     CONSTRAINT\_TYPE = 'PRIMARY KEY'
   AND     c.TABLE\_NAME = pk.TABLE\_NAME
   AND     c.CONSTRAINT\_NAME = pk.CONSTRAINT\_NAME

So, there are no mentions about 'd.' or 'i.'. Are "d" and "i" alias for these tables by default?

Corporal Agarn

One thing to remember about T-SQL is that by default if you have a null in an expression the expression is null. Thus

COALESCE(@PKCols + ' and', ' on')

would return ' on' if @PKCols is null as NULL + ' and' returns NULL. Also NULL cannot be compared to another NULL as in WHERE NULL = NULL because this returns NULL not true or false. This gets me on occasion. Besides the COALESCE function there is ISNULL, used like

WHERE ISNULL(@PKCols, '') <> ''

Hope this is more helpful than confusing.