Hi Ferindo
>The intended effect is to get a timestamp field that inserts the
current system
>time on inserts and continues to update the field with the current
timestamp
>on updates without the application or use needing to specify it....
are you
>saying that the timestamp attribute alone will do that?
Yep. Try...
create table ts(i int,ts timestamp);
insert into ts values(1,null);
select * from ts;
+------+---------------------+
| i | ts |
+------+---------------------+
| 1 | 2005-12-04 17:21:01 |
+------+---------------------+
update ts set i=2;
select * from ts;
+------+---------------------+
| i | ts |
+------+---------------------+
| 2 | 2005-12-04 17:21:13 |
+------+---------------------+
PB
http://www.artfulsoftware.com
-----
Ferindo Middleton Jr wrote:
> Thanks Peter. I did originally use this table in a Postgresql db.
> Thanks for you advice. Your suggestions below allowed me to create
> this table and I learned a thing t two about proper usage of the
> TIMESTAMP data type. The intended effect is to get a timestamp field
> that inserts the current system time on inserts and continues to
> update the field with the current timestamp on updates without the
> application or use needing to specify it.... are you saying that the
> timestamp attribute alone will do that?
>
> Ferindo
>
> Peter Brawley wrote:
>
>> Ferindo
>>
>> One problem is:
>> employment_status_id INTEGER REFERENCES employment_statuses(id)
>> NOT NULL,
>> NOT NULL should be before REFERENCES.
>>
>> Also, in:
>> last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
>> (i) NOT NULL is superfluous since the default is given by
>> CURRENT_TIMESTAMP.
>> (ii) specifying DEFAULT CURRENT_TIMESTAMP defeats auto-resetting of
>> the timestamp on updates. Is
>> that what you want? To get auto-setting on INSERTs and UPDATEs, just
>> write
>> last_updated TIMESTAMP,
>> Also the manual doesn't mention TIME WITHOUT TIME ZONE. Are you
>> thinking of PostgreSQL?
>>
>> PB
>>
>> -----
>>
>> Ferindo Middleton Jr wrote:
>>
>>> I have been trying to create a table but mysql 5.0.15-nt-max is
>>> having a problem parsing the statement. Anyone know what the problem
>>> is in the syntax of the following table creation statement:
>>>
>>> CREATE TABLE registration_and_attendance (
>>> id SERIAL NOT NULL UNIQUE,
>>> firstname VARCHAR(256) NOT NULL,
>>> middlename TEXT,
>>> lastname VARCHAR(256),
>>> suffix TEXT,
>>> sf182_received BOOLEAN NOT NULL DEFAULT
>>> TRUE,
>>> registrant_email_address TEXT,
>>> cc_email_list TEXT,
>>> bureau_id INTEGER REFERENCES
>>> bureaus(id),
>>> office TEXT,
>>> class_id INTEGER NOT NULL
>>> REFERENCES classes(id),
>>> schedule_id INTEGER REFERENCES
>>> schedules(id),
>>> start_date DATE,
>>> end_date DATE,
>>> enrolled BOOLEAN,
>>> attended BOOLEAN,
>>> completed BOOLEAN,
>>> cancelled BOOLEAN DEFAULT FALSE,
>>> cancelled_comments TEXT,
>>> comments TEXT,
>>> email_confirmation_sent BOOLEAN NOT
>>> NULL, employment_status_id INTEGER REFERENCES
>>> employment_statuses(id) NOT NULL,
>>> last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
>>> last_user_updated TEXT,
>>> waitlisted BOOLEAN DEFAULT FALSE,
>>> overflow_registrant BOOLEAN DEFAULT FALSE,
>>> attach_hotel_listing_directions BOOLEAN,
>>> instructor_legacy TEXT,
>>> time_legacy TIME WITHOUT TIME ZONE,
>>> ssn_legacy TEXT,
>>> position_grade_title TEXT,
>>> office_phone_legacy TEXT,
>>> contractor_legacy BOOLEAN,
>>> no_show_legacy BOOLEAN,
>>> status_legacy TEXT,
>>> funding_id INTEGER REFERENCES
>>> funding_types(id),
>>> PRIMARY KEY (firstname, lastname, class_id, start_date,
>>> end_date)
>>> );
>>>
>>> I get the following error message with the above statement but I
>>> can't figure out what the problem is:
>>>
>>> ERROR 1064 (42000): You have an error in your SQL syntax; check the
>>> manual that
>>> corresponds to your MySQL server version for the right syntax to use
>>> near 'NOT N
>>> ULL,
>>> last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
>>> l' at line 23
>>>
>>>
>>> Thanks, Ferindo
>>>
>>
>>
>
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.11/191 - Release Date: 12/2/2005
