Trigger on record insert

Hello,

I want to write a trigger where as soon as a record has been inserted into the tablei have a field called 'DateAddedToDB' which i want to update with the day the record was added.

Im not familiar with triggers so im not really sure how to tackle it... any suggestions.

Thanks,
AIdoHSGAsked:
Who is Participating?
 
Anthony PerkinsCommented:
Assuming that DateAddedToDB is in the same table then the best way to solve this problem is simply to add a DEFAULT value of GETDATE() to the DateAddedToDB.
0
 
YZlatCommented:
is this field in the table  to which you insert data?
0
 
AIdoHSGAuthor Commented:
yes it is in the same table im inserting the data. Also either i will sometimes have one entry or i might upload a list.

Thanks,
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
YZlatCommented:
CREATE TRIGGER trg_RecordDate

ON Table1

FOR INSERT

AS

UPDATE Table1 SET DateAddedToDB=GETDATE() WHERE ...
0
 
AIdoHSGAuthor Commented:
what would be the where condition for getting the new record?
0
 
YZlatCommented:

One question  though - Why do you need a trigger if the field to be updated is in the same table on which you perform insert???

Why not just do a simple INSERT?
0
 
Anthony PerkinsCommented:
You do not need a WHERE clause, but rather make use of the INSERTED logical table.
0
 
AIdoHSGAuthor Commented:
sometimes i wont be entering data... it is by other users and it is done through a form... so when data is entered i'm not running a sql insert query.
0
 
AIdoHSGAuthor Commented:
acperkins what is the INSERTED logical table? not sure what that is

thx
0
 
YZlatCommented:
can you post the table structure?

If the date field is in each row you can simply insert the date with the rest of the data
0
 
Anthony PerkinsCommented:
If you still want to use a TRIGGER than do it this way:
CREATE TRIGGER trg_YourTriggerNameGoesHere ON YourTableNameGoesHere

AFTER INSERT

AS

BEGIN

UPDATE t
SET      DateAddedToDB = GETDATE()
FROM      YourTableNameGoesHere t
      INNER JOIN INSERTED i ON t.<yourprimarykeygoeshere> = i.<yourprimarykeygoeshere>
END
0
 
AIdoHSGAuthor Commented:
Actually that worked...
Thanks... that was simple...
0
 
Anthony PerkinsCommented:
>>If the date field is in each row you can simply insert the date with the rest of the data<<
But that is the point, the author is saying they have no control over that, hence it has to be using a DEFAULT value (simplest) or a TRIGGER.
0
 
Anthony PerkinsCommented:
>> what is the INSERTED logical table?<<
If in future you ever need to use a TRIGGER make sure to read up on the INSERTED and DELETED logical tables.  You are going to need them.
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.