auto populating fields in sql - 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

SQL Server 2000 Forums

SQL Server Development (2000)

auto populating fields in sql

Author

Topic

superblades
Starting Member

7 Posts

Posted - 2007-01-03 : 05:16:56

Hi,

I wondering if someone can help me as im a complete newbie :)

Ive upsized a database from access to sql 2000 and i had a bit of code written in vb that once i changed a particular field in a table it automatically inserted todays date into another field, i know it still works if i work with it as an access project but im trying to get completely away from access.

e.g.

i have a table called tbl_req and a table called tbl_status

in the tbl_req table i have a field called fld_status which is linked to the tbl_status table and it basically contains a unique ID and a string e.g.

tbl_status

unique id - status
1 - completed
2 - cancelled
3 - ordered

etc...

what i want to happen is when someone selects a different status in the tbl_req table it automatically inserts todays date in another field called fld_statuschgd in the tbl_req table.

e.g.

tbl_req

fldstatus - fldstatuschgd
ordered - 12/12/2006

if i now changed from ordered to completed the below should happen

completed - 03/01/2007

etc...

ive no idea if i need to use triggers or stored procedures etc.

any help would be most appreciated

many thanks

Brendan Tate

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-03 : 05:28:16

There are two ways to do this:
1. If all your data manipulation happens through SPs, you can code your SPs in such a way to handle this scenario
2. Write an Update trigger on the table which will update current date for the updated status.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

superblades
Starting Member

7 Posts

Posted - 2007-01-03 : 05:45:28

sorry to sound stupid but how would i code it if i was doing it as a trigger?

Cheers

Brendan

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-03 : 05:55:23

[code]create trigger t1
on tbl
for insert, update
as
begin
if update(fldstatus)
update t1
set fldstatuschgd = getdate()
from tbl t1 join inserted t2 on t1.[id] = t2.[id]
end[/code]

Edit: Added missing AS keyword.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

superblades
Starting Member

7 Posts

Posted - 2007-01-03 : 06:09:03

thanks for your help

i seem to be getting an error message :- error 156 incorrect syntax near the keyword 'begin'

superblades
Starting Member

7 Posts

Posted - 2007-01-03 : 06:39:47

managed to get it working

CREATE trigger trginsert
on dbo.tblAllReqs
for update
as
if update(fldstatus)
update tblallreqs
set fldstatuschgd = getdate()

thanks for your help harsh_athalye

Regards

Brendan Tate

superblades
Starting Member

7 Posts

Posted - 2007-01-03 : 10:37:19

Erm managed to find a problem :(

if i update 1 of the records it updates all of the other date records with the same as the one i changed.

e.g.

record no - fldstatus - fldstatuschgd
----------------------------------------
1 - complete - 02/01/2007 15:00
2 - ordered - 01/01/2007 12:00

if i now change record 2 to fldstatus complete i get the following:-

record no - fld status - fldstatuschgd
----------------------------------------
1 - complete - 03/01/2007 15:35
2 - complete - 03/01/2007 15:35

as you can see it changed both records and not just the record i changed, how can i make the trigger just trigger on the record and not the whole table?!

Thanks

Brendan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources