创建触发器时出现问题.每个表中的列名必须是唯一的.MsSQL

Question

我有这个触发器，它必须在CREATE_TABLE上工作，并且应该检查表中是否有列updated_at。如果没有，请创建一个触发器并设置另一个触发器。

这就是它的样子：

CREATE TRIGGER UpdatedAtFiled ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
DECLARE @createdTableName VARCHAR(50), @column VARCHAR(50), @triggerName VARCHAR(50), @execTrigger VARCHAR(300), @sqlcmd VARCHAR(100), @ColumnWasCreated INT, @Itr INT;
SET @Itr = 0;
SET @ColumnWasCreated = 0;
SELECT @createdTableName = name FROM sys.Tables ORDER BY create_date
WHILE (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @createdTableName) > @Itr
BEGIN
SELECT @column = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @createdTableName ORDER BY COLUMN_NAME OFFSET @Itr ROWS FETCH NEXT 1 ROWS ONLY;
SET @triggerName = 'TRIGGER_' + CONVERT(VARCHAR(20), @createdTableName);
SET @execTrigger = 'CREATE TRIGGER ' + @triggerName + ' ON ' + @createdTableName +
' FOR UPDATE as UPDATE T SET updated_at = GETDATE() ' + ' FROM ' +
@createdTableName + ' AS T JOIN inserted AS i ON T.id = i.id;';
IF (@column != 'updated_at' AND @ColumnWasCreated <= 0)
SET @sqlcmd = 'ALTER TABLE ' + @createdTableName + ' ADD updated_at DATETIME NOT NULL DEFAULT GETDATE()';
EXEC(@sqlcmd)
EXEC(@execTrigger)
SET @ColumnWasCreated = 1;
SET @Itr = @Itr + 1;
END
END

每次创建表时，我都会收到一个错误：

CREATE TABLE test(id INT PRIMARY KEY IDENTITY (1, 1), name VARCHAR(20))

[S004][2705]第1行：每个表中的列名必须唯一。表"test"中的列名"updated_at"被指定了多次。`

我想，这个条件中有问题，但我不确定：

IF (@column != 'updated_at' AND @ColumnWasCreated <= 0)

我真的找不到错误。怎么了？我该怎么解决？

Answer 1

我会在前面说，我认为使用触发器是个坏主意还有许多其他解决方案，尤其是首先要确保对每个表都正确执行此操作。

尽管如此：

• 您应该在所有位置指定模式名称
• 不要从sys.tables中提取最新的表，而是使用EVENTDATA来获取与执行的特定CREATE TABLE命令相关的信息
• 对象名称最多可以是nvarchar(128)，您可以使用别名sysname
• 使用QUOTENAME正确引用对象名称
• DML触发器需要在主键上联接，主键可能不是id，并且可能是多列

CREATE TRIGGER UpdatedAtFiled ON DATABASE
FOR CREATE_TABLE
AS
DECLARE @tableName sysname = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','sysname');
DECLARE @schemaName sysname = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','sysname');
DECLARE @sql nvarchar(max);
IF NOT EXISTS (SELECT 1
FROM sys.columns c
JOIN sys.tables t ON t.object_id = c.object_id
JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE s.name = @schemaName
AND t.name = @tableName
AND c.name = 'updated_at')
BEGIN
SET @sql = '
ALTER TABLE ' + QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName) + '
ADD updated_at DATETIME NOT NULL DEFAULT GETDATE();
';
EXEC sp_executesql @sql;
END
SET @sql = '
CREATE TRIGGER ' + QUOTENAME('TRIGGER_' + @tableName) + ' ON ' + QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName) + '
FOR UPDATE AS
IF @@ROWCOUNT = 0
RETURN;
UPDATE T
SET updated_at = GETDATE()
FROM ' + QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName) + ' AS T
JOIN inserted AS i ON ('
+ (
SELECT STRING_AGG('T.' + QUOTENAME(c.name) + ' = i.' + QUOTENAME(c.name),  ' AND ')
FROM sys.tables t
JOIN sys.schemas s ON s.schema_id = t.schema_id
JOIN sys.indexes i ON i.object_id = t.object_id
JOIN sys.index_columns ic ON ic.object_id = i.object_id
AND ic.index_id = i.index_id
JOIN sys.columns c ON t.object_id = c.object_id
AND c.column_id = ic.column_id
WHERE i.is_primary_key = 1
AND ic.is_included_column = 0
)
+ ');
';
EXEC sp_executesql @sql;
GO