Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

How do I log date and time?

3 posts in Trigger Last posting was on 2011-10-18 16:48:12.0Z
Advantage Posted on 2011-10-18 00:00:23.0Z
From: "Advantage" <no-reply>
Newsgroups: Advantage.Trigger
Subject: How do I log date and time?
Date: Mon, 17 Oct 2011 17:00:23 -0700
Lines: 29
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: 208.31.88.154
Message-ID: <4e9cc176$1@solutions.advantagedatabase.com>
X-Trace: 17 Oct 2011 16:59:50 -0700, 208.31.88.154
Path: solutions.advantagedatabase.com
Xref: solutions.advantagedatabase.com Advantage.Trigger:503
Article PK: 1136564

I have table temperature the following fields:
temp1 -- double
temp2 -- double
LogDate - date
LogTime - time
When I insert a record, I want to have the current date and time written
into LogDate and LogTime.

Using Architect in version 8.10.0.18, I created an INSTEAD OF Insert
trigger:

UPDATE __new
SET __new.LogDate = CURDATE(),
__new.LogTime = CURTIME();
INSERT INTO temperature SELECT * FROM __new;

(This is roughly from an example in the help file)

If I execute this INSERT from Architect:
INSERT INTO temperature (temp1, temp2) VALUES (55, 44)

a new record appears in the table, but the LogTime field in ALL of the rows
of temperature are changed to the time of the last insert. There is
obviously some concept here I am missing!

Thanks,
Chuck


Thomas Steinmaurer Posted on 2011-10-18 07:48:15.0Z
Date: Tue, 18 Oct 2011 09:48:15 +0200
From: Thomas Steinmaurer <t.steinmaurer_dontbugmewithspam@upscene.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.23) Gecko/20110920 Thunderbird/3.1.15
MIME-Version: 1.0
Newsgroups: Advantage.Trigger
Subject: Re: How do I log date and time?
References: <4e9cc176$1@solutions.advantagedatabase.com>
In-Reply-To: <4e9cc176$1@solutions.advantagedatabase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 193.186.164.88
Message-ID: <4e9d2f1d$1@solutions.advantagedatabase.com>
X-Trace: 18 Oct 2011 00:47:41 -0700, 193.186.164.88
Lines: 41
Path: solutions.advantagedatabase.com
Xref: solutions.advantagedatabase.com Advantage.Trigger:504
Article PK: 1136566


> I have table temperature the following fields:
> temp1 -- double
> temp2 -- double
> LogDate - date
> LogTime - time
> When I insert a record, I want to have the current date and time written
> into LogDate and LogTime.
>
> Using Architect in version 8.10.0.18, I created an INSTEAD OF Insert
> trigger:
>
> UPDATE __new
> SET __new.LogDate = CURDATE(),
> __new.LogTime = CURTIME();
> INSERT INTO temperature SELECT * FROM __new;
>
> (This is roughly from an example in the help file)
>
> If I execute this INSERT from Architect:
> INSERT INTO temperature (temp1, temp2) VALUES (55, 44)
>
> a new record appears in the table, but the LogTime field in ALL of the rows
> of temperature are changed to the time of the last insert. There is
> obviously some concept here I am missing!

You don't need a trigger for that. Simply use CURRENT_DATE and
CURRENT_TIME as field DEFAULT values when creating the table.


--
With regards,

Thomas Steinmaurer
Upscene Productions
http://www.upscene.com
http://blog.upscene.com/thomas/

Download LogManager Series, FB TraceManager today!
Continuous Database Monitoring Solutions supporting
Firebird, InterBase, Advantage Database, MS SQL Server
and NexusDB!


Advantage Posted on 2011-10-18 16:48:12.0Z
From: "Advantage" <no-reply>
Newsgroups: Advantage.Trigger
References: <4e9cc176$1@solutions.advantagedatabase.com> <4e9d2f1d$1@solutions.advantagedatabase.com>
Subject: Re: How do I log date and time?
Date: Tue, 18 Oct 2011 09:48:12 -0700
Lines: 52
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
NNTP-Posting-Host: 208.31.88.154
Message-ID: <4e9dadb1$1@solutions.advantagedatabase.com>
X-Trace: 18 Oct 2011 09:47:45 -0700, 208.31.88.154
Path: solutions.advantagedatabase.com
Xref: solutions.advantagedatabase.com Advantage.Trigger:505
Article PK: 1136565

Thanks for the reply.
I think because the syntax is from the expression engine, the only defaults
that worked for me are DATE() and TIME().

Chuck

"Thomas Steinmaurer" <t.steinmaurer_dontbugmewithspam@upscene.com> wrote in
message news:4e9d2f1d$1@solutions.advantagedatabase.com...
>> I have table temperature the following fields:
>> temp1 -- double
>> temp2 -- double
>> LogDate - date
>> LogTime - time
>> When I insert a record, I want to have the current date and time written
>> into LogDate and LogTime.
>>
>> Using Architect in version 8.10.0.18, I created an INSTEAD OF Insert
>> trigger:
>>
>> UPDATE __new
>> SET __new.LogDate = CURDATE(),
>> __new.LogTime = CURTIME();
>> INSERT INTO temperature SELECT * FROM __new;
>>
>> (This is roughly from an example in the help file)
>>
>> If I execute this INSERT from Architect:
>> INSERT INTO temperature (temp1, temp2) VALUES (55, 44)
>>
>> a new record appears in the table, but the LogTime field in ALL of the
>> rows
>> of temperature are changed to the time of the last insert. There is
>> obviously some concept here I am missing!
>
> You don't need a trigger for that. Simply use CURRENT_DATE and
> CURRENT_TIME as field DEFAULT values when creating the table.
>
>
> --
> With regards,
>
> Thomas Steinmaurer
> Upscene Productions
> http://www.upscene.com
> http://blog.upscene.com/thomas/
>
> Download LogManager Series, FB TraceManager today!
> Continuous Database Monitoring Solutions supporting
> Firebird, InterBase, Advantage Database, MS SQL Server
> and NexusDB!