November 5, 2008 at 8:00 am
I wud like to record time when a record is inserted by giving default value from table definition. I dont want the developers do it it every time fromtheier queires.
What i need..
Record datetime when record is inserted under createdate field and modifieddate field will be NULL
and when record is modified time shud record in modifiedfield without effecting the createdate field.
November 5, 2008 at 8:29 am
the best approach would be to use a trigger.
On insert, populate createdate = getdate(), modify date = NULL or really better = getdate()
On update, update the modify date = getdate()
- here you could even make sure that the developers do not change the create date as part of the update trigger.
The more you are prepared, the less you need it.
November 5, 2008 at 8:34 am
A trigger is good for the update statement, but I propose you use a simple default value for the new value. Here's how:
USE tempdb
GO
CREATE TABLE [dbo].[Test]
(
[ID] [int] NOT NULL,
[CreationDate] [datetime] NULL
CONSTRAINT [DF_Test_CreationDate] DEFAULT ( getdate() ),
[UpdateDate] [datetime] NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED ( [ID] ASC )
)
GO
CREATE TRIGGER trTest_Update ON Test
AFTER UPDATE
AS
BEGIN
UPDATE test
SET UpdateDate = GETDATE() ;
END
GO
I added the trigger to update the "UpdateDate", and the default value for the getdate().
Hope this helps,
J-F
Cheers,
J-F
November 5, 2008 at 9:46 pm
I agree... triggers needed only on updates. Default of GETDATE() on a column takes care of inserts.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 6, 2008 at 7:43 am
Agree, default on the table is the most efficient for the initial createdate. However, if you are concerned that the development code will put in an incorrect value, then a trigger is better. The default only comes into play if a null is passed in.
The more you are prepared, the less you need it.
November 6, 2008 at 7:46 am
Jean-François Bergeron (11/5/2008)
A trigger is good for the update statement, but I propose you use a simple default value for the new value. Here's how:
USE tempdb
GO
CREATE TABLE [dbo].[Test]
(
[ID] [int] NOT NULL,
[CreationDate] [datetime] NULL
CONSTRAINT [DF_Test_CreationDate] DEFAULT ( getdate() ),
[UpdateDate] [datetime] NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED ( [ID] ASC )
)
GO
CREATE TRIGGER trTest_Update ON Test
AFTER UPDATE
AS
BEGIN
UPDATE test
SET UpdateDate = GETDATE() ;
END
GO
I added the trigger to update the "UpdateDate", and the default value for the getdate().
Hope this helps,
J-F
Your trigger will update UpdateDate for every row in the table, not just the updated rows.
November 6, 2008 at 7:50 am
My bad, you're right, wrote it too quickly, thanks for the correction.
Simply add a where on the key,
Thanks,
J-F
Cheers,
J-F
February 5, 2009 at 3:49 pm
Jean-François Bergeron (11/6/2008)
Simply add a where on the key
Maybe a silly question from this beginner, but is the following the right way then?
CREATE TRIGGER trTest_Update ON Test
AFTER UPDATE
AS
BEGIN
DECLARE @ID int
SET @ID = (SELECT ID FROM inserted)
UPDATE Test SET UpdateDate = GetDate() WHERE ID = @ID
END
Or is there another, more simple, better or faster solution?
February 6, 2009 at 5:49 pm
NGLN (2/5/2009)
Jean-François Bergeron (11/6/2008)
Simply add a where on the keyMaybe a silly question from this beginner, but is the following the right way then?
CREATE TRIGGER trTest_Update ON TestAFTER UPDATE
AS
BEGIN
DECLARE @ID int
SET @ID = (SELECT ID FROM inserted)
UPDATE Test SET UpdateDate = GetDate() WHERE ID = @ID
END
Or is there another, more simple, better or faster solution?
That would be a full up RBAR trigger and will only handle single row updates. It will blow up if you have more than one row updated... "Death by SQL" and a career limiting bit of code... 😉
The way to write such a trigger, within the context you posted above, is as follows...
CREATE TRIGGER dbo.trTest_Update ON dbo.Test
AFTER UPDATE
AS
BEGIN
UPDATE dbo.Test
SET UpdateDate = GETDATE()
FROM dbo.Test tgt
INNER JOIN Inserted i ON tgt.ID = i.ID
END
--Jeff Moden
Change is inevitable... Change for the better is not.
February 7, 2009 at 12:55 am
hi,
you can set the default property of the column to getdate() for new records , and for updates you may use some trigger to handle that,
February 7, 2009 at 7:54 am
Thanks Jeff !
February 7, 2009 at 9:17 am
You're welcome... just remember... if recursive triggers are turned on, you have to take special steps when a trigger updates it's own table or you end up with a really nasty loop until it fails.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 8, 2009 at 2:23 pm
[font="Verdana"]I'm going to differ somewhat from the reponses already presented here.
The combination of default for inserts and triggers for updates will work well. However...
From a database design and even an architectural point of view, you do not want to be encouraging direct inserts into tables.
It would be better to create what I call a "gateway procedure" -- i.e. a stored procedure that is called to do the insert or update. Then you can encapsulate the necessary logic for changing the insert/update datestamp fields within the gateway procedure.
One serious advantage of this is that if you need to move to a full audit log (i.e. not just "when was the first insert/who made the last update") then you can make the changes so that the audit log records are created by your gateway procedure, and you can enclose them in a transaction so that any underlying changes to the table data guarantee an entry in the audit log.
It's also a good way of getting around the issue of logging who made the change. Unless your developers are particularly smart about how they establish the database connections and use proxies correctly, the chances are that the connection made to the database may not actually be under the name of the person making the changes. So if you use system_user to audit who made the changes, then it becomes valueless.
On the other hand, if the changes are all made via gateway procedures, then the application can pass the name of the person making the changes. Or even better, the "User ID" (or equivalent). So you can accurately audit the person making the changes.
Use of triggers in this scenario leads to lazy code, and you will only cause issues when later you run into scenarios where you need gateway stored procedures. So do yourself a favour and use them from the outset.
[/font]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply