TRIGGER - Insert current date on new row

I would like to write a trigger to insert the current date in my ProjectRequestDate column when a new row is added. I have tried to set this as a DEFAULT when I created the table but it does not work unless the query is re-run after the db committ. I have no experience with triggers but I feel this is the sure way to go. Please could someone help me write this? I am using a Visual Basic form and a SQL Server 2005.
teustaceAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
teustaceConnect With a Mentor Author Commented:
Hi Deportas
Just trying out your trigger. I want to make sure it only updates the Current Timestamp if it is NULL, jsut incase it over writes any stored values. Can you help me out with the code?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you please clarify the table structure, ie the primary key field?
0
 
dportasCommented:
I think a trigger is overkill for this. Use a DEFAULT. Maybe you could post some example code to show how the default isn't working for you.

In a trigger you could do something like this:

UPDATE YourTbl
SET ProjectRequestDate = CURRENT_TIMESTAMP
FROM YourTbl
JOIN Inserted
ON YourTbl.KeyCol = Inserted.KeyCol;
0
 
teustaceAuthor Commented:
I agree that the TRIGGER may be a bit too powerful but I don't know what else to do...

In my table I have defined  two default values on two columns. One is GetDate() the other is an integer (6). I am developing a visual basic form based on this table. When I enter a new row, be it from the form or direct into the db table, the default values will not populate the table unless I re-run the ! SQL. Do I need to create a trigger and if so what is the code? The columns are currently set to NOT NULL. When I try entering the data directly into my table (rather than from the form) it gives a red dot to tell me that "the row was successfully committed to the db however, a problem occurred when attempting to retrieve the data back". So it tells me to re-run the query again. THEN it populates with the specifued default values.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.