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>

20 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/.

Unknown 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.

Anonymous 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.

nano&ann said...

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

Unknown 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

Unknown 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

Unknown said...

Thank you for sharing valuable information. Nice post. I enjoyed reading this post.
Signature:
i like play games friv4 online and play games 2 girls Download baixar facebook movel

Unknown said...

Very nice post, impressive. its quite different from other posts. Thanks for sharing.

anti skid tapes supplier in india

Unknown said...

Very good! I found the perfect theme used!
happy wheels
super mario bros
pacman
agario

Unknown said...

I was working and suddenly I visits your site frequently and recommended it to me to read also. Age Of War 2
Big Farm | Slitherio
Tank Trouble | Happy Wheels
Goodgeme Empire | Slither.io

Unknown said...

To be able to participate in free online games. You can click here to play always.
gun mayhem | can your pet
learn to fly | happy wheels
tank trouble 3

Pankaj Singh said...

I really enjoy this blog, this is informative and valuable for me. Gets an awesome website designing services by professionals at OGEN Infosystem.
SEO Service in Delhi

web arora said...

Very inspiring
For website desining and best website developer
Check - https://webocity.in
Website designing company
Website designing company in delhi
Website designing company in india
Website developer company
Website developer company in delhi
Website developer company in india
website developing company in delhi

Latest School News Nigeria Portal said...

Hi Thank you for sharing this wonderful article. Do well to visit Latest School News to apply for Jamb Registration Form. You can always check your Jamb Result and Neco Result Checker here.
Do well to check your Admission Status using Jamb Caps.

Sinelogix said...

Thanks, for providing good Information to community…

Php Web Development Company Bangalore | Website Developer Company | Internet Marketing Company in Bangalore | Website Company In India

Online Front said...

Really glad to say that it's an awesome post to read and learn something new, I always come to know about something interesting and unique. Good job and waiting for another one also. We also offers some services are:-
Digital marketing Company
SMM Services
PPC Ads Services
PPC Services in Delhi
Website Design & Development Packages
Web Development Packages
Web Development Package

admin said...

Online oyunlar mı arıyorsun? Tıkla: online oyunlar

admin said...

İndirmeden oynanan oyunlar mı arıyorsun? Tıkla: indirmeden oynanan oyunlar

Rahul Sharma said...

This is Great Article. You are post informatics blog so keep posting.
SEO Company in Noida
Lead Generation Company in Delhi NCR
Top 10 CBSC Schools in Meerut
Delhi Bazar Satta
Best Digital Marketing Company in Meerut
disawar satta number
Latest and Breaking News in Hindi