Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Insert and update date/time trigger

Posted on 2004-11-04
3
Medium Priority
?
382 Views
Last Modified: 2012-08-14
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

0
Comment
Question by:keekehhung
  • 3
3 Comments
 
LVL 8

Expert Comment

by:SashP
ID: 12491337
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
 
LVL 8

Accepted Solution

by:
SashP earned 400 total points
ID: 12491350
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
 
LVL 8

Expert Comment

by:SashP
ID: 12491356
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

804 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question