Trigger to add basic columns in table.

Whenever I create a table I have to add few default filed like, CreatedDate/CreatedBy/UpdatedDate/UpdatedBy/Status etc. For every table I create I’ve to add these fields. I hate such repetitive work so I have created a trigger that automatically adds these filed for me when I create a table.

 

alter trigger tr_DataBase
on DataBase
For Create_table
as
declare @tableName varchar(max)
declare @query varchar(max)
set @tableName = ”
SELECT @tableName=EVENTDATA().value(‘(/EVENT_INSTANCE/ObjectName)[1]’,’nvarchar(max)’)
set @query = ‘alter table ‘+@tableName+’ add CreatedDate datetime;’
set @query = @query + ‘alter table ‘+@tableName+’ add UpdatedDate datetime;’
set @query = @query + ‘alter table ‘+@tableName+’ add Status tinyint default 1 not null;’
exec(@query)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s