Create Trigger If Column Exists In SQL Server
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?
Answer
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'
Related Questions
- → I can't do a foreign key, constraint error
- → How to implement DbDongle::convertTimestamps as workaround of invalid timestamps with MySql strict
- → MySQL error "Foreign key constraint is incorrectly formed"
- → Eloquent Multitable query
- → "Laravel 5.1" add user and project with userId
- → Database backup with custom code in laravel 5 and get the data upto 10 rows from per table in database
- → Laravel 5.1 QueryException when trying to delete a project
- → Using Array in '->where()' for Laravel Query Building
- → Chaining "Count of Columns" of a Method to Single Query Builder
- → Laravel Eloquent Joining Strange query
- → convert time using mysql laravel 5
- → How to update a column after an expiration date in MySQL?
- → Foreign key constraint fails on existing key