SQL Stuff

Basic Way To Create ModifedDate Column In SQL

Here’s a basic way to create the [ModifedDate] column for some of your tables. Often times whenever you create a table that will undergo various updates and modifications; you’ll find yourself wanting to add the classic columns for [CreateDate] and [ModifedDate].

In this example; I have a basic table I’m calling [Standard_Rules_00] which is basically a place where I can store some of my custom build, or modification scripts.

use [compliance];
set nocount on

create table [STANDARD_RULES_00]
(
	[lineid]	int identity(1,1)
,	[createdate]	datetime null
,	[statements]	varchar(max)
,	[modifieddate]	datetime null
,	[description]	varchar(max)
)
go

create trigger trig_post_insert
on [dbo].[STANDARD_RULES_00]
after insert
as
begin 
    update [dbo].[STANDARD_RULES_00]
    set [createdate] = getdate()
    from dbo.[STANDARD_RULES_00] sr
    where exists (select 1 from inserted i where i.[lineid] = sr.[lineid]);
end
go

create trigger trig_post_update
on [dbo].[STANDARD_RULES_00]
after update
as
begin 
    update [dbo].[standard_rules_00]
    set [modifieddate] = getdate()
    from dbo.[STANDARD_RULES_00] sr
    where exists (select 1 from inserted i where i.[lineid] = sr.[lineid]);
end
go
Advertisements

Categories: SQL Stuff

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