Insert and update date/time trigger

I am newbie in SQL.

I have a SQL database imported from Access database. I want to add in 3 new fields which
will automatically get the date and number(int) when we insert new or update existing data.
I will use ACCESS as interface program and SQL 2000 as database.

Table : MAIN PROFILE  (import from ACCESS)
1) Name
2) ID
3) Entry Date

but I want it to be like following table structure, the field CREATEDATE, UPDATETIME and RECNO
should update the date and integer when we create or update any record.

CREATEDATE = date & time we import Access to SQL/date & time we insert new record to table
UPDATETIME = date & time we import Access to SQL/date & time we update the record in table
RECNO = running number, no duplication

Table : MAIN PROFILE
1) Name
2) ID
3) Entry Date
4) CREATEDATE  <-- new
5) UPDATETIME  <-- new
6) RECNO <-- new



Can following triggers statement work? I tried it for few times and it give me syntax error.
Any amendment need to correct? Please advise. Thank you.


<For field CREATEDATE and UPDATETIME>
Statement:

CREATE TRIGGER [MAIN PROFILE_INSERT] on dbo.[MAIN PROFILE]
FOR INSERT as UPDATE [MAIN PROFILE]
SET [MAIN PROFILE].CREATEDATE = getdate(),
[MAIN PROFILE].UPDATETIME = getdate() from [MAIN PROFILE]
inserted where [MAIN PROFILE].RECNO = inserted.RECNO


<For field UPDATETIME only>
Statement:

CREATE TRIGGER [MAIN PROFILE_UPDATE] on dbo.[MAIN PROFILE]
FOR UPDATE as UPDATE
SET [MAIN PROFILE].UPDATETIME = getdate() from [MAIN PROFILE]
inserted where [MAIN PROFILE].RECNO = inserted.RECNO

keekehhungAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SashPCommented:
You do not need a trigger for the create data and the recno.

Can I suggest an identity column for the recno and a default of getdate() for the createdate field.

ALTER YOURTABLE
ADD
CREATEDATE Datetime DEFAULT = getdate(),
RECNO int identity (1,1)
0
SashPCommented:
keekehhung,

CREATE TRIGGER [MAIN PROFILE_UPDATE] on dbo.[MAIN PROFILE]
FOR UPDATE as
UPDATE [MAIN PROFILE]
SET UPDATETIME = getdate() from
[MAIN PROFILE]
join inserted on [MAIN PROFILE].RECNO = inserted.RECNO
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SashPCommented:
SashP,
> ALTER YOURTABLE
> ADD
> CREATEDATE Datetime DEFAULT = getdate(),
> RECNO int identity (1,1)

Should be

ALTER TABLE YOURTABLE
ADD
CREATEDATE Datetime DEFAULT = getdate(),
RECNO int identity (1,1)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.