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>

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

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.

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

OGEN Infosystem (P) Limited 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

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

admin said...

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