List:General Discussion« Previous MessageNext Message »
From:Olaf Stein Date:September 4 2007 8:04pm
Subject:Re: timestamp for update and insert
View as plain text  
If you decide to use the trigger here is the syntax

http://dev.mysql.com/doc/refman/5.0/en/triggers.html

And that table structure looks ok to me


As far as the backup goes just dump the mysql database, which you should be
doing anyway to backup users etc

Olaf



On 9/4/07 3:59 PM, "Hiep Nguyen" <hiep@stripped> wrote:

> 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