automatic update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

automatic update

Author

Topic

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-04-22 : 07:16:29

hi,

i have:


create table tbl_order
(
 id int
,price float
,quantity int
,product nvarchar(50)
,date_of_order smalldatetime
,date_of_change smalldatetime
)

insert into tbl_order (id, price, quantity, product, date_of_order) values (1, 23.21, 2, 'A+B', getdate())
insert into tbl_order (id, price, quantity, product, date_of_order) values (2, 22.21, 2, 'A+B+C', getdate())

and i want to make automatic update:
-if new order is made, i want to update field date_of_change with getdate().

i want to put it in a job with some trigger or something that will recognize instant new insert.

thank you in advance,

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-22 : 07:36:45

Use a trigger. Something like this...

create trigger tbl_order_trigger on tbl_order after insert, update as
update a set date_of_change = getdate() from tbl_order a inner join inserted b on a.id = b.id

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-22 : 07:59:21

If you just want this to happen for insert action alone i think a default constraint on the column date_of_change is sufficient

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-04-22 : 08:52:06

ryanrandall thank you. i forgot that i have to depend this trigger on another table.

so let say, when a new record is saved in tbl_records (with column id_order) then insert new record in tbl_order.

how much mroe complicated can get now?

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-22 : 09:41:07

Insert a new record into tbl_order? Where will the values for each of the columns come from?

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources