SQL Server 2008: create trigger across all tables in db

Question

Using SQL Server 2008, I've created a database where every table has a datetime column called "CreatedDt". What I'd like to do is create a trigger for each table so that when a value is inserted, the CreatedDt column is populated with the current date and time.

If you'll pardon my pseudocode, what I'm after is the T-SQL equivalent of:

foreach (Table in MyDatabase)
{
   create trigger CreatedDtTrigger
   {
        on insert createddt = datetime.now;
   }
}

If anyone would care to help out, I'd greatly appreciate it. Thanks!

Instead of creating some many triggers, could you use DEFAULT GETDATE() on all CreateDt columns? — EricZ
– EricZ, Commented Apr 26, 2013 at 15:59
Didn't know you could do that, thanks. After some Googling, it looks like I'd do something like: ADD CONSTRAINT DF_MyTable_CreatedDt DEFAULT GETDATE() FOR CreatedDt But I would still need to do that for every table. Is there a way to programmatically loop through them? — LMCSdev
– LMCSdev, Commented Apr 26, 2013 at 16:15

mroach · Accepted Answer · 2013-04-26 16:24:47Z

2

As @EricZ says, the best thing to do is bind a default for the column. Here's how you'd add it to every table using a cursor and dynamic SQL:

Sure, You can do it with a cursor:

declare @table sysname, @cmd nvarchar(max)
declare c cursor for
  select name from sys.tables where is_ms_shipped = 0 order by name
open c; fetch next from c into @table
while @@fetch_status = 0
begin
  set @cmd = 'ALTER TABLE ' + @table + ' ADD CONSTRAINT DF_' + @table + '_CreateDt DEFAULT GETDATE() FOR CreateDt'
  exec sp_executesql @cmd
  fetch next from c into @table
end
close c; deallocate c

answered Apr 26, 2013 at 16:24

mroach

2,4781 gold badge24 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

LMCSdev Over a year ago

Many thanks, I think this is exactly what I needed. I will read up on cursors so I can learn exactly what this is doing. Much appreciated!

ErikE Over a year ago

I think it's best practice to use INFORMATION_SCHEMA.TABLES whenever possible, since this is guaranteed to not change between versions but the system tables are not--as demonstrated between SQL 2000 and SQL 2005.

Prahalad Gaggar · Accepted Answer · 2013-04-26 16:29:42Z

2

No need to go for Cursors. Just copy the result of below Query and Execute.

select distinct 'ALTER TABLE '+ t.name + 
' ADD CONSTRAINT DF_'+t.name+'_crdt DEFAULT getdate() FOR '+ c.name
from sys.tables t
inner join sys.columns c on t.object_id=c.object_id
where c.name like '%your column name%'

answered Apr 26, 2013 at 16:29

Prahalad Gaggar

11.6k17 gold badges58 silver badges73 bronze badges

2 Comments

ErikE Over a year ago

I think it's best practice to use INFORMATION_SCHEMA.TABLES whenever possible, since this is guaranteed to not change between versions but the system tables are not--as demonstrated between SQL 2000 and SQL 2005.

Prahalad Gaggar Over a year ago

select distinct 'ALTER TABLE '+ t.TABLE_NAME +  ' ADD CONSTRAINT DF_'+t.TABLE_NAME+'_crdt DEFAULT getdate() FOR '+ c.COLUMN_NAME from INFORMATION_SCHEMA.TABLES t inner join INFORMATION_SCHEMA.COLUMNS c on t.TABLE_NAME=c.TABLE_NAME where c.COLUMN_NAME like '%createdd%'

With Information_Schema. Thanks @ErikE

ErikE · Accepted Answer · 2013-04-26 23:13:32Z

Here's another method:

DECLARE @SQL nvarchar(max);
SELECT @SQL = Coalesce(@SQL + '
', '')
    + 'ALTER TABLE ' + QuoteName(T.TABLE_SCHEMA) + '.' + QuoteName(T.TABLE_NAME)
    + ' ADD CONSTRAINT ' + QuoteName('DF_'
    + CASE WHEN T.TABLE_SCHEMA <> 'dbo' THEN T.Table_Schema + '_' ELSE '' END
    + C.COLUMN_NAME) + ' DEFAULT (GetDate()) FOR ' + QuoteName(C.COLUMN_NAME)
    + ';'
FROM
   INFORMATION_SCHEMA.TABLES T
   INNER JOIN INFORMATION_SCHEMA.COLUMNS C
      ON T.TABLE_SCHEMA = C.TABLE_SCHEMA
      AND T.TABLE_NAME = C.TABLE_NAME
WHERE
   C.COLUMN_NAME = 'CreatedDt'
;
EXEC (@SQL);

This yields, and runs, a series of statements similar to the following:

ALTER TABLE [schema].[TableName] -- (line break added)
   ADD CONSTRAINT [DF_schema_TableName] DEFAULT (GetDate()) FOR [ColumnName];

Some notes:

This uses the INFORMATION_SCHEMA views. It is best practice to use these where possible instead of the system tables because they are guaranteed to not change between versions of SQL Server (and moreover are supported on many DBMSes, so all things being equal it's best to use standards-compliant/portable code).
In a database with a case-sensitive default collation, one MUST use upper case for the INFORMATION_SCHEMA view names and column names.
When creating script it's important to pay attention to schema names and proper escaping (using QuoteName). Not doing so will break in someone's system some day.
I think it is best practice to put the DEFAULT expression inside parentheses. While no error is received without it in this case, with it, if the function GetDate() is parameterized and/or ever changed to a more complex expression, nothing will break.

If you decide that column defaults are not going to work for you, then the triggers you imagined are still possible. But it will take some serious work to manage whether the trigger already exists and alter or create it appropriately, JOIN to the inserted meta-table inside the trigger, and do it based on the full list of primary key columns for the table (if they exist, and if they don't, then you're out of luck). It is quite possible, but extremely difficult--you could end up with nested, nested, nested dynamic SQL. I have such automated object-creating script that contains 13 quote marks in a row...

Collectives™ on Stack Overflow

SQL Server 2008: create trigger across all tables in db

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related