In an UPDATE trigger - get the Primary Key without the column's name

Question

I need to update the DateModified Column without knowing the name of the Primary Key Column.

Basically, I've got a plain-jane UPDATE trigger like this:

CREATE TRIGGER updated_SCHEMA_TABLE
 ON [SCHEMA].[TABLE]
   AFTER UPDATE AS 
     BEGIN 
       SET NOCOUNT ON; 
       UPDATE [SCHEMA].[TABLE] 
       SET DateModified = getdate() 
       WHERE [PRIMARYKEY]
       IN (SELECT [PRIMARYKEY]
       FROM Inserted)
     END

but won't know the primary key's column name because the trigger will be generated programmatically (see this question as to why).

Is this possible?

Answer 1

well, i just took a quick gander through some of the system views, and i'm not seeing anything that tells you what the primary keys are for each table. you just might have to do it by hand.

Answer 2

OK, perhaps I was a bit unfair leaving this part as an "exercise" in the previous question.

This would work for tables with a single column PK. It might be easiest to start with these and then go back and manually adjust those with a composite PK.

select 'create trigger updated_'+s.name + '_' + t.name + ' on  ' + quotename(s.name) + '.' + quotename(t.name) 
       + ' after update as'
       + ' begin '
       + ' set nocount on; '
       + ' update t'
       + '     set [DateModified] = getdate()'
       + '     from inserted i'
       + '         inner join ' + quotename(s.name) + '.' + quotename(t.name) + ' t'
       + '             on i.' + quotename(c2.name) + ' = t.' + quotename(c2.name)
       + ' end'
    from sys.columns c
        inner join sys.tables t
            on c.object_id = t.object_id
        inner join sys.schemas s
            on t.schema_id = s.schema_id
        inner join sys.indexes i
            on t.object_id = i.object_id
        inner join sys.index_columns ic
            on i.object_id = ic.object_id
                and i.index_id = ic.index_id
        inner join sys.columns c2
            on ic.object_id = c2.object_id
                and ic.index_id = c2.column_id
    where c.name = 'DateModified'
        and t.type = 'U'
        and i.is_primary_key = 1

Answer 3

If you were able to build all of your primary keys from identity columns:

SELECT table_name, column_name
FROM INFORMATION_SCHEMA.COLUMNS
where COLUMNPROPERTY (OBJECT_ID(Table_Name),Column_Name,'IsIdentity') = 1
 and table_schema = [SCHEMA] and table_name = [TABLE]

otherwise, you will have to look through the indexes using all of the sys tables (good work Joe).