Datestamp? 
Author Message
 Datestamp?

Hi,

is there a way to declare a table column to be
a datestamp which would be automatically updated
on INSERT/UPDATE? If not, how do you do it?

I am thinking of using triggers:

    create trigger replace_caa_rev
    on CONTACTS
    for insert
    as

       update CONTACTS set UPD = getdate()

       /* some other code in this trigger */

    create trigger update_upd
    on CONTACTS
    for insert,update
    as

    update CONTACTS set UPD = getdate()

Regards
Alex



Fri, 25 Apr 2003 03:00:00 GMT
 Datestamp?

Well, for insert it is more effeciant to bind a default to the column of
"getdate()".

For the update trigger, you are close, but I doubt you want to update
the value of every UPD
column in the table.  You probably want something closer to:

   create trigger update_upd
    on CONTACTS
    for update
    as
   update CONTACTS  set c.UPD = getdate
   from contacts c, inserted i
   where c.<primarykey> = i.<primarykey>
go

Note that you can have no more than three triggers on a table, one each
for insert, update, and delete.

-bret

Quote:

> Hi,

> is there a way to declare a table column to be
> a datestamp which would be automatically updated
> on INSERT/UPDATE? If not, how do you do it?

> I am thinking of using triggers:

>     create trigger replace_caa_rev
>     on CONTACTS
>     for insert
>     as

>        update CONTACTS set UPD = getdate()

>        /* some other code in this trigger */

>     create trigger update_upd
>     on CONTACTS
>     for insert,update
>     as

>     update CONTACTS set UPD = getdate()

> Regards
> Alex



Sat, 26 Apr 2003 03:00:00 GMT
 Datestamp?
Hi,

Quote:

> Well, for insert it is more effeciant to bind a default to the column of
> "getdate()".

thanks for this idea. Does someone know what am I doing wrong here:

1> create table CONTACTS
2> (
3>     CAA   char(12)    not null constraint check_caa check (CAA like 'CAA[ Z] %'),
4>     REV   char(4)     not null constraint check_rev check (REV like 'R[0-9]%'),
5>     BLK   varchar(20) null,
6>     MHO   char(11)    null,
7>     NAME1 varchar(20) null,
8>     MAIL1 varchar(60) null,
9>     NAME2 varchar(20) null,
10>     MAIL2 varchar(60) null,
11>     UPD   datetime    not null default(getdate()),
12>     FND   bit         not null
13> )
14>
15> go
Msg 156, Level 15, State 1:
Line 11:
Incorrect syntax near the keyword 'default'.

Regards
Alex



Sun, 27 Apr 2003 03:00:00 GMT
 Datestamp?


Quote:
> Hi,


> > Well, for insert it is more effeciant to bind a default to the
column of
> > "getdate()".

> thanks for this idea. Does someone know what am I doing wrong here:

> 1> create table CONTACTS
> 2> (
> 3>     CAA   char(12)    not null constraint check_caa check (CAA
like 'CAA[ Z] %'),
> 4>     REV   char(4)     not null constraint check_rev check (REV
like 'R[0-9]%'),
> 5>     BLK   varchar(20) null,
> 6>     MHO   char(11)    null,
> 7>     NAME1 varchar(20) null,
> 8>     MAIL1 varchar(60) null,
> 9>     NAME2 varchar(20) null,
> 10>     MAIL2 varchar(60) null,
> 11>     UPD   datetime    not null default(getdate()),
> 12>     FND   bit         not null
> 13> )
> 14>
> 15> go
> Msg 156, Level 15, State 1:
> Line 11:
> Incorrect syntax near the keyword 'default'.

> Regards
> Alex

the syntax is ==>   UPD  datetime    default getdate() not null

Sent via Deja.com http://www.deja.com/
Before you buy.



Sun, 27 Apr 2003 03:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Help on simple datestamp trigger

2. convert datestamp to char(yyyy/mm/dd)

3. Automatically Inserting Datestamp into Record Field

4. Datestamp in DTS Destination FileName

5. Future Datestamp on Import

6. DateStamp and NT Account name

7. Auto Datestamp when Record is Added?

8. Datestamp an output file

9. default datestamp of 12am of today's date for default value

10. how to capture datestamp of a file system file

11. Datestamp

12. search on datestamp?


 
Powered by phpBB® Forum Software