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?)

  • Have you looked at how a trigger is created?
    – user507
    Sep 10, 2012 at 5:20
  • yes, I have looked at how trigger is created (Ref: msdn.microsoft.com/en-us/library/ms189799(v=sql.105).aspx) but one thing is bothering me is that how does one insert the UPDATE statement for the row that is being trigger by getting the condition correct as in WHERE id = Changed_Row_ID
    – Jack
    Sep 10, 2012 at 5:42

1 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
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; 

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:


I hope this helps you.

  • I never thought that MS SQL actually will form a table called inserted. So, does that mean that it would be safe not to have your own database table to have name such as inserted and deleted?
    – Jack
    Sep 10, 2012 at 9:12
  • 1
    Yes I would recommend not having your own tables with these names just to avoid confusion ;-) Sep 10, 2012 at 13:51
  • on the create trigger manual page you can find a link to the following page describing the use of the inserted/deleted tables (msdn.microsoft.com/en-us/library/ms191300.aspx)
    – miracle173
    Sep 10, 2012 at 17:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.