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,
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,
YZlat
is this field in the table to which you insert data?
ASKER
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,
Thanks,
CREATE TRIGGER trg_RecordDate
ON Table1
FOR INSERT
AS
UPDATE Table1 SET DateAddedToDB=GETDATE() WHERE ...
ON Table1
FOR INSERT
AS
UPDATE Table1 SET DateAddedToDB=GETDATE() WHERE ...
ASKER
what would be the where condition for getting the new record?
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?
You do not need a WHERE clause, but rather make use of the INSERTED logical table.
ASKER
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.
ASKER
acperkins what is the INSERTED logical table? not sure what that is
thx
thx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
If the date field is in each row you can simply insert the date with the rest of the data
If you still want to use a TRIGGER than do it this way:
CREATE TRIGGER trg_YourTriggerNameGoesHer
AFTER INSERT
AS
BEGIN
UPDATE t
SET DateAddedToDB = GETDATE()
FROM YourTableNameGoesHere t
INNER JOIN INSERTED i ON t.<yourprimarykeygoeshere>
END
CREATE TRIGGER trg_YourTriggerNameGoesHer
AFTER INSERT
AS
BEGIN
UPDATE t
SET DateAddedToDB = GETDATE()
FROM YourTableNameGoesHere t
INNER JOIN INSERTED i ON t.<yourprimarykeygoeshere>
END
ASKER
Actually that worked...
Thanks... that was simple...
Thanks... that was simple...
>>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.
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.
>> 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.
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.