Create trigger if column exists in SQL Server-CodePudding

How do I go about looping through all the tables that have the updated_at column? I figured out how to find all the tables that have the column using information_schema.columns like this:

SELECT TABLE_NAME FROM information_schema.columns WHERE COLUMN_NAME = 'updated_at'

But I have no idea how I would go about looping through all the tables to create the trigger to update the updated_at column to the current time with SYSDATETIMEOFFSET() when the row is updated?

EDIT: So I managed to figure out the iterating now using a cursor. but now im kind of confused about updating the date. So if I set an after update trigger to update the updated_at date, wouldn't that trigger an infinite loop?

CodePudding user response：

Because this meeting hasnt started yet and Im bored, here is what youre looking for:

Learning how to use system tables to help you write code dynamically is going to be important.

Also looping to do this is the worst thing ever.

SELECT 
    'CREATE TRIGGER schema.triggername
    ON schema.tablename
    AFTER UPDATE 
    AS

          IF TRIGGER_NESTLEVEL() > 1
             RETURN

        UPDATE schema.tablename
        SET RowUpdated = SYSDATETIMEOFFSET()
        WHERE UniqueIdentifier/PrimaryKey IN 
        (
            SELECT  UniqueIdentifier/PrimaryKey 
            FROM Inserted
        )
    ;
    GO'
    , 'UPDATE ' t.name ' SET ' c.name ' = SYSDATETIMEOFFSET(); '
FROM 
    sys.Tables t 
    INNER JOIN sys.columns c ON t.object_id =c.object_id
WHERE
    c.name = 'RowLoaded'