List:General Discussion« Previous MessageNext Message »
From:Hiep Nguyen Date:September 4 2007 7:59pm
Subject:Re: timestamp for update and insert
View as plain text  
so, if trigger is used then

create table temp (
id int not null primary key auto_increment,
data varchar(100),
inserted timestamp,
lastupdated timestamp)

is good enough, right?  trigger will use now() function to set inserted & 
lastupdated.

any thought on backup & restore tables & tringgers???

thank you for your helps.
T. Hiep

On Tue, 4 Sep 2007, Michael Dykman wrote:

> There is nothing terribly wrong with the approach documented in
> 'http://sql-info.de/mysql/examples/CREATE-TABLE-examples.html' but, as
> you no doubt have read, it does mean that you have to make sure that
> every insert statement is specifically designed to set the *second*
> timestamp field to now() and then count on the built-in properties to
> see the first one updated on every UPDATE.
>
> The only other caveats are:
>        your application behaviour is now dependent on the ordering of
> columns; ok in the short-term, increasingly annoying over time as
> maintainence phases grow the app in complexity.
>       importing data from your system to another system might prove
> hairy as you figure out how to temporarily avoid this bevahiour to
> keep your data intact.
>
> The trigger method is universal in that this solution will port to any
> half-way reasonable database engine....
>
> but, as in all things IT, do whatever best meets your circumstances.
>
> - michael
>
> On 9/4/07, Hiep Nguyen <hiep@stripped> wrote:
>> is it possible to do without trigger?
>>
>> i google and found this link:
>> http://sql-info.de/mysql/examples/CREATE-TABLE-examples.html
>>
>> but when i tried to combine two examples into one CREATE statement and it
>> didn't work.
>>
>> any idea?
>>
>> is there a way to create this table that accomplishes these two goals?
>>
>> thanks,
>> T. Hiep
>>
>> On Tue, 4 Sep 2007, Michael Dykman wrote:
>>
>>> Triggers are a fine idea, but I would use a trigger for both cases..
>>> no point putting that level of housekeeping on the application when
>>> you can set rules in the database and more or less forget about it.
>>>
>>> - michael
>>>
>>>
>>> On 9/4/07, Olaf Stein <steino@stripped> wrote:
>>>> I would use a trigger (at least for the update)....
>>>>
>>>> The first insert should work with now() and you can leave lastupdateted
>>>> empty
>>>>
>>>>
>>>> Olaf
>>>>
>>>>
>>>> On 9/4/07 3:01 PM, "Hiep Nguyen" <hiep@stripped> wrote:
>>>>
>>>>> Hi list,
>>>>>
>>>>> i tried to create a table with inserted & lastupdated timestamp
> fields:
>>>>>
>>>>> create table temp (
>>>>> id int not null primary ke auto_increment,
>>>>> data varchar(100),
>>>>> inserted timestamp default now(),
>>>>> lastupdated timestamp(8));
>>>>>
>>>>>
>>>>> how do i get mysql to put in the current timestamp for inserted
> &
>>>>> lastupdated fields when i insert a record and only lastupdated when
> i
>>>>> update the record?
>>>>>
>>>>> thanks,
>>>>> T. Hiep
>>>>>
>>>>
>>>>
>>>> --
>>>> MySQL General Mailing List
>>>> For list archives: http://lists.mysql.com/mysql
>>>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>>>>
>>>>
>>>
>>>
>>> --
>>> - michael dykman
>>> - mdykman@stripped
>>>
>>> - All models are wrong.  Some models are useful.
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>>>
>>>
>>
>
>
> -- 
> - michael dykman
> - mdykman@stripped
>
> - All models are wrong.  Some models are useful.
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>
Thread
timestamp for update and insertHiep Nguyen4 Sep
  • Re: timestamp for update and insertOlaf Stein4 Sep
    • Re: timestamp for update and insertMichael Dykman4 Sep
      • Re: timestamp for update and insertOlaf Stein4 Sep
      • Re: timestamp for update and insertHiep Nguyen4 Sep
        • Re: timestamp for update and insertMichael Dykman4 Sep
          • Re: timestamp for update and insertHiep Nguyen4 Sep
            • Re: timestamp for update and insertOlaf Stein4 Sep
  • RE: timestamp for update and insertDaevid Vincent4 Sep