How to create a trigger that update the date field and time field upon modify of the row?

Question

I have created a table testtable inside the database testbase that have the following structure:

product_no (int, not null)
product_name (varchar(30), not null)
price (money, null)
expire_date (date, null)
expire_time (time(7), null)

However, how do I write a trigger such that it will update that particular row that has been modified (or updated with new information) and record the modification date into the expire_date field and the modification time into the expire_time field? (or if this is possible?)

Answer 1

It would be fairly easy but I would actually recommend changing the logic of the command that is inserting/updating the data so that it adds the additional information at this point.

However, should you wish to proceed with a trigger you could do something like this:

create trigger my_trigger
on my_table
after insert, update
as
begin
declare @dt datetime = getdate();
update a
set expire_date = CONVERT(DATE, @dt, 101),
    expire_time = CONVERT(TIME, @dt)
from my_table as a
join inserted as b 
on a.product_no = b.product_no; 
end
go  

This uses the INSERTED table to find out the product_no of the rows that have been changed/created. You can find out more about triggers at the link below:

http://msdn.microsoft.com/en-us/library/ms189799.aspx

I hope this helps you.