I have this trigger which has to work on CREATE_TABLE and should check if there is column updated_at in table. If not, create one and set another trigger.

This is how it looks like:

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

And every time I create table I get an error:

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

[S0004][2705] Line 1: Column names in each table must be unique. Column name 'updated_at' in table 'test' is specified more than once.`

I guess, there is problem in this conditional, but I'm not sure:

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

I literally can't find the error. What's wrong? How can I fix that?

CodePudding user response：

I will preface this by saying that I think using a trigger for this is a bad idea. There are a number of other solutions to this, not least just making sure you do this correctly for each table in the first place.

Be that as it may:

• You should specify the schema name everywhere.
• Don't pull the most recent table from sys.tables, instead use EVENTDATA to get the info which relates to the particular CREATE TABLE command that was executed.
• Object names can be up to nvarchar(128), you can use the alias sysname
• Use QUOTENAME to quote object names correctly.
• The DML trigger needs to join on the primary key, which may not be id and may be more than one column.

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
)
    ');
';

EXEC sp_executesql @sql;

GO

CodePudding user response：

...

create or alter trigger updatedatfiled on database
for create_table
as
begin

declare @sql nvarchar(max);
declare @schemaname sysname = EVENTDATA().value('(EVENT_INSTANCE/SchemaName/text())[1]', 'sysname');
declare @tablename sysname = EVENTDATA().value('(EVENT_INSTANCE/ObjectName/text())[1]', 'sysname');

if col_length(object_id(quotename(@schemaname) '.' quotename(@tablename)), 'updated_at') is null
begin
   select @sql = N'alter table '  quotename(@schemaname) '.' quotename(@tablename)   ' add updated_at datetime;';
   exec(@sql)
end

select @sql = N'
create trigger '  quotename(@schemaname)  '.'  quotename('updatedat_' left(@tablename, 118))   N' on '  quotename(@schemaname) '.' quotename(@tablename)   N'
for update
as
begin
  set nocount on;
  if not exists(select * from inserted)
  begin
    return;
  end

  update t
  set updated_at = getdate()
  from '  quotename(@schemaname) '.' quotename(@tablename)   N' as t
  where exists (
                select t.*
                intersect
                select * from inserted
                );
end';

exec(@sql);

end