Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
Have you looked at how a trigger is created? –  Shawn Melton Sep 10 '12 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 '12 at 5:42

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
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 '12 at 9:12
1  
Yes I would recommend not having your own tables with these names just to avoid confusion ;-) –  Mr.Brownstone Sep 10 '12 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 '12 at 17:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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