Link to home
Start Free TrialLog in
Avatar of sunbio
sunbio

asked on

Can someone explain what this piece of T-SQL means?

Here is a Trigger Procedure that I found online a while back and use it to have a history of each data change that takes place in the system. It works, and I love it. But I'm trying to understand it since I might want to make some changes to it (like not have it save the Timestamp field).

Can someone explain to me this piece of the code:
-- Get primary key columns for full outer join
SELECT @PKCols = COALESCE(@PKCols + ' and', ' on')
               + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME

From what I understand Coalesce will return the first parameter which is not Null. But Here it seems that it will allways be the first one since it contains the ' and' which is not Null, or if you include the @PKCols and if that is Null it will ignore it and its ' and' and go to the next parameter then it will allways do this since @PKCols is declared but not assigned until this point. So I'm sure there is something or more (perhaps pretty basic) that I don't know.

Would love if someone can explain this to me.


CREATE TRIGGER tr_tblCustomer ON dbo.tblCustomer 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 = 'tblCustomer'
 
-- 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
 
-- Get primary key select for insert
SELECT @PKSelect = COALESCE(@PKSelect+'+','') 
       + '''<' + COLUMN_NAME 
       + '=''+convert(varchar(100),
coalesce(i.' + COLUMN_NAME +',d.' + 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
 
IF @PKCols IS NULL
BEGIN
       RAISERROR('no PK on table %s', 16, -1, @TableName)
       RETURN
END
 
SELECT         @field = 0, 
       @maxfield = MAX(ORDINAL_POSITION) 
       FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
WHILE @field < @maxfield
BEGIN
       SELECT @field = MIN(ORDINAL_POSITION) 
               FROM INFORMATION_SCHEMA.COLUMNS 
               WHERE TABLE_NAME = @TableName 
               AND ORDINAL_POSITION > @field
       SELECT @bit = (@field - 1 )% 8 + 1
       SELECT @bit = POWER(2,@bit - 1)
       SELECT @char = ((@field - 1) / 8) + 1
       IF SUBSTRING(COLUMNS_UPDATED(),@char, 1) & @bit > 0 OR @Type IN ('I','D')
       BEGIN
               SELECT @fieldname = COLUMN_NAME 
                       FROM INFORMATION_SCHEMA.COLUMNS 
                       WHERE TABLE_NAME = @TableName 
                       AND ORDINAL_POSITION = @field
               SELECT @sql = '
insert Audit (	Type, 
               TableName, 
               PK, 
               FieldName, 
               OldValue, 
               NewValue, 
               UpdateDate, 
               UserName)
select ''' + @Type + ''',''' 
       + @TableName + ''',' + @PKSelect
       + ',''' + @fieldname + ''''
       + ',convert(varchar(1000),d.' + @fieldname + ')'
       + ',convert(varchar(1000),i.' + @fieldname + ')'
       + ',''' + @UpdateDate + ''''
       + ',''' + @UserName + ''''
       + ' from #ins i full outer join #del d'
       + @PKCols
       + ' where i.' + @fieldname + ' <> d.' + @fieldname 
       + ' or (i.' + @fieldname + ' is null and  d.' + @fieldname + ' is not null)' 
       + ' or (i.' + @fieldname + ' is not null and  d.' + @fieldname + ' is null)' 
               EXEC (@sql)
print @sql
       END
END

Open in new window

Avatar of James Murrell
James Murrell
Flag of United Kingdom of Great Britain and Northern Ireland image

take a look at the authors site

http://www.nigelrivett.net/AuditTrailTrigger.html
If @PKCols is not null, then @PKCols will be set to:

@PKCols + ' and' + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME

If @PKCols is null then @PKCols will be set to:

' on' + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
The second part of the coalesce will only execute once.
Since you have "select @pkcols = ....", then on each pass, the new evaluated value will be appended to the variable.

So the first record, as you said, @pkcols will not have anything assigned to it, evaluate to null and add the "on ...." portion.
On the next record, @pkcols has the "on...." portion from the step above assigned to it, and will not be null.  And so on for each subsequent record.
Avatar of sunbio
sunbio

ASKER

Snarf0001:
Perhaps what I'm not understanding is "each pass.."
How can there be more than one pass since there is no loop or suchlike taking place in this procedure?
ASKER CERTIFIED SOLUTION
Avatar of Snarf0001
Snarf0001
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sunbio

ASKER

Snarf0001,
You have totaly clarified this for me.  Thanks so much.