代码之家 › 专栏 › 技术社区 › David Murdoch

在更新触发器中-获取不带列名的主键

triggers sql-server-2008 sql-server sql

David Murdoch · 技术社区 · 14 年前

我需要在不知道主键列名称的情况下更新DateModified列。

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

但不知道主键的列名,因为触发器将以编程方式生成( see this question as to why ).

3 回复 | 直到 8 年前

Joe Stefanelli 14 年前

这将适用于具有单列PK的表。最简单的方法可能是从这些开始,然后返回并用复合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

Bill 14 年前

如果您能够从标识列生成所有主键:

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]

否则,您将不得不使用所有sys表查看索引(很好)。