keekehhung
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)
> 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)
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)