help on Insert trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

help on Insert trigger

Author

Topic

chiman

21 Posts

Posted - 2007-10-05 : 18:28:04

Hi,
I need to update a createdDt field,
with currentdate only when there is a insert in the table.

Please help me with how to do this.

Thanks

chiman

21 Posts

Posted - 2007-10-05 : 18:44:50

Its urgent. Can anybody help

quote:
Originally posted by renu.khot@gmail.com

Hi,
I need to update a createdDt field,
with currentdate only when there is a insert in the table.

Please help me with how to do this.

Thanks

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2007-10-05 : 18:51:18

Have you tried yet? Do you have any code that gives you an error or doesn't work as expected? It's easy to find out...I'm pretty sure searching for "create trigger" in books online will give you the answer...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-05 : 18:52:46

if it's just a createdDate you need then just put a default value on the column

if you need a modified date also then look up after insert trigger
in there you do:

update t1
set modifiedDate = getdate()
from yourTable t1 join instered i on t1.id = i.id

where id is a your id column for the table

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Kristen
Test

22859 Posts

Posted - 2007-10-06 : 04:27:36

Beware that doing a Modify Date in a trigger has two side effects:

1) All records are, in effect, written to the database twice (this is an AFTER trigger, after-all!)
2) All inserts will be get a modify date of "now". This is obviously what you want, but we found that when copying data from database to database (e.g. Production to Dev, or NewYork to Londong) it was undesireable to have the Modify date changed, we wanted the date when the record was REALLY modified.

So we ditched the "Update Modify Date in Trigger" in favour of "Set Modify Date in MyRecordSave Stored Procedure"

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources