Wednesday, November 28

Converting MySQL "on update current_timestamp" to Oracle

Another short simple SQL for all out there in the process of converting old MySQL schemas to Oracle.

MySQL has got a built in feature to automatically update a column to the current timestamp whenever the row is updated, just by using the default-clause. The "on update current_timestamp" feature can be quite handy if you have lazy developers who can't be bothered writing full insert statements. :)

The MySQL create table statement would be something like this:
create table p (
id int,
a varchar(10),
d timestamp DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP,
constraint p_pk primary key (id)
);

Not difficult to do in Oracle either, but we need a trigger to assist.
Example:
SQL> alter session set nls_Date_format='HH24:MI:SS';

Session altered.

SQL> create table p (id number, a varchar2(10), d date default sysdate,
constraint p_pk primary key (id));

Table created.

SQL> insert into p(id,a) values(1,'test');

1 row created.

SQL> host cat p_test.sql
CREATE OR REPLACE TRIGGER p_d_trig
BEFORE UPDATE ON p
FOR EACH ROW
BEGIN
select sysdate into :new.d from dual;
END p_d_trig;
/

SQL> @p_test

Trigger created.

SQL> select * from p;

ID A D
---------- ---------- --------
1 test 21:15:05

SQL> update p set a='foo' where id=1;

1 row updated.

SQL> select * from p;

ID A D
---------- ---------- --------
1 foo 21:16:44

SQL>

7 comments:

Anonymous said...

However, in MySQL you can override the timestamp with...

update p set d = '2008-01-01' where id = 1;

but your trigger does not allow it.

Roxy said...

Costa rica tours, shopping online, costa rica fishing, jaco fishing charters, costa rica whale watching, costa rica dolphin watching, costa rica party boat, panama fishing, fishing costa rica los suenos, los suenos fishing charters, costa rica fishing los suenos, los suenos costa rica fishing, costa rica los suenos fishing, fishing costa rica los suenos, los suenos fishing charters, costa rica fishing los suenos, los suenos costa rica fishing charters, costa rica los suenos fishing, Los suenos fishing, Fishing costa rica los suenos, Costa Rica Rafting,
http://www.kingtours.com/.

Satish said...

This is a great feature in MySQL. Are there alternative solutions other than using Triggers in Oracle.

I have seen many people take Tom's article on "Trouble with Triggers" too literally and refrain from using Triggers.

highheels said...

Christian Louboutin
As a way to design and style remarkably one of a kind and effective Christian Louboutin Petal Sandals; they consider benefit of your very last equipment and technologies in order that you’ll be fully capable of get the brightest and designer sandal designs from them in way. Seems wise, the Christian Louboutin Petal Sandals are extremely inimitable and flexible shoes in an attempt to seize your attentions directly. On the subject of the colours, the monumental fascination is that they use superlative colors for making fancy your Christian Louboutin Petal Sandals.

sianaran said...

ผลบอล
แทงบอล
เว็บเดิมพัน
พนันบอล
คาสิโน
คาสิโนออนไลน์
sbobet
mix8888
3m
m8bet
holiday
ibcbet
gclub
livescore
7m
เว็บเดิมพัน
เว็บเดิมพัน
เว็บเดิมพัน
เว็บเดิมพัน
เว็บเดิมพัน
เว็บเดิมพัน
เว็บเดิมพัน
เว็บเดิมพัน
เว็บเดิมพัน
เว็บเดิมพัน
เว็บเดิมพัน
เว็บเดิมพัน
เว็บเดิมพัน
เว็บเดิมพัน

chengcheng zhu said...

got a built in feature to automatically update a column to the current timestamp whenever the row is updated, just by using the default-clause. The "on update current_timestampWorld of Warcraft Gold Billig
World of Warcraft Gold Kaufen

Brandon Hudson said...

Nice post with awesome points! Can’t wait for the next one.

HP - Refurbished - 15.6 Pavilion Notebook - 6 GB Memory - 640 GB Hard Drive - Midnight Black

HP - Refurbished - 14" Pavilion Notebook - 4 GB Memory - 500 GB Hard Drive - Winter Blue