Create trigger if column exists in SQL Server

Question

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?

There are lots of examples out there on how to iterate through the tables in a database, what about those didn't you understand and we can try and fill in the blanks. — Thom A, Feb 17 '22 at 15:26
Does this help? - [How to create a trigger for all tables in a SQL Server database](https://stackoverflow.com/q/25865949/1048425) — GarethD, Feb 17 '22 at 15:31
Your content says you want a trigger to do this, but wouldnt you really just want to redefine the data types of the column? You can easily write simple sql for this..... — Doug Coats, Feb 17 '22 at 16:05
@DougCoats I'm not trying to change the column type just update the `updated_at` column to the current time. — Ryker, Feb 17 '22 at 16:28
I mean the solution is still the same - use system table to write code dynamically. Not only is this a common thing thats asked about it shouldve been easy to find not to mention EWAY easier than iterating (through anything) — Doug Coats, Feb 17 '22 at 16:30
@Larnu 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? — Ryker, Feb 17 '22 at 16:32

score 1 · Answer 1 · edited Feb 17 '22 at 21:53

1

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'

edited Feb 17 '22 at 21:53

Charlieface

52,284
6
19
43

answered Feb 17 '22 at 16:34

Doug Coats

6,255
9
27
49

Sorry I guess I wasn't super clear in my question (just updated it to clarify that i meant after update) or maybe I'm misunderstanding the code but would this be triggered when the row is updated? This looks like its is just doing this update instantaneously instead on doing so after the row is updated – Ryker Feb 17 '22 at 16:38
@Ryker The idea is still the same. Use the system table to help you dynamically create code. Make sense? Youll have to adjust the trigger to your needs, I think I feel lik eim doing all the heavy lifting instead of just being helpful – Doug Coats Feb 17 '22 at 16:45
1

Ohh I see what you mean, thank you and sorry I don't really have a lot of experience with databases aside from basic querying and modifying existing procedures – Ryker Feb 17 '22 at 16:49

score 0 · Answer 2 · answered Feb 17 '22 at 22:14

@DougCoat's answer helped me come up with the create trigger and the looping was from this answer

DECLARE @TableName varchar(30)

DECLARE MY_CURSOR CURSOR 
  LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR 
SELECT TABLE_NAME FROM information_schema.columns WHERE COLUMN_NAME = 'updated_at'

OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN 
    EXEC(
    'CREATE TRIGGER tr_update_timestamp_' + @TableName + ' ON [' + @TableName +
    '] AFTER UPDATE 
    AS
        BEGIN
            UPDATE [' + @TableName +
            '] SET updated_at = SYSDATETIMEOFFSET()
            WHERE [' + @TableName + '].id IN 
            (
                SELECT i.id 
                FROM Inserted i
            )
        END'
    )
    FETCH NEXT FROM MY_CURSOR INTO @TableName
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR

Create trigger if column exists in SQL Server

2 Answers2