Skip navigation links

MySQL Forums :: Triggers :: How to convert oracle scripts to mysql


Advanced Search

Re: How to convert oracle scripts to mysql
Posted by: irek kordirko ()
Date: April 27, 2012 03:22PM

Hello,

MySql hasn't BEFORE and AFTER STATEMENT triggers (like Oracle),
it supports only FOR EACH ROW triggers.
But looking at code you posted it seems that this shouldn't be a very big problem.

This procedure:
CREATE OR REPLACE TRIGGER HASHPASSWORD_BEFORE_INSERT
BEFORE INSERT ON USER_MASTER 
BEGIN
is called once before each insert statement - it's called only ONCE regardless of how many records the statement inserts
- whether it inserts 1 or 1000 records, it's always called only once.
It looks that this procedure initialize variables with nulls.

Then, this procedure is called after inserting for each row:
CREATE OR REPLACE TRIGGER HASHPASSWORD_AFTER_INSERT
AFTER INSERT ON USER_MASTER FOR EACH ROW
BEGIN
It seems that this procedure simply stores user_id and a password in variables.

Then, after the statement finishes inserting all rows, this procedure is called:
CREATE OR REPLACE TRIGGER HASHPASSWORD_PARENT
AFTER INSERT ON USER_MASTER
BEGIN
This trigger retrieves last userid and password from variables, encrypts password and stores data in the USER_MASTER table.


In MySql, just create a trigger like this:
CREATE OR REPLACE TRIGGER HASHPASSWORD_PARENT
AFTER INSERT ON USER_MASTER
FOR EACH ROW
BEGIN
  DECLARE ENCR_DATA VARCHAR(100); 
  /* CREATE A HASH OF PASSWORD USING MD5 */
  SET ENCR_DATA = DBMS_OBFUSCATION_TOOLKIT.MD5(new.PASSWORD);

  /* UPDATE THE ENCRYPTED PASSWORD HASH ON PASSWORD COLUMN */
  UPDATE USER_MASTER SET PASSWORD = ENCR_DATA WHERE USER_ID = NEW.USER_ID;
END;


The big problem is ... I have no clue how to implement in MySql a call to oracle DBMS_OBFUSCATION_TOOLKIT.MD5(STATE_PKG.NPASSWORD) function.
Documentation says: http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_obtool.htm#i1003449
that it computes MD5 hash value.

Options: ReplyQuote


Subject Views Written By Posted
How to convert oracle scripts to mysql 2134 lokesh kumar 04/27/2012 03:21AM
Re: How to convert oracle scripts to mysql 1065 irek kordirko 04/27/2012 03:22PM
Re: How to convert oracle scripts to mysql 1014 lokesh kumar 05/02/2012 06:02AM
Re: How to convert oracle scripts to mysql 828 Peter Brawley 05/02/2012 08:27PM
Re: How to convert oracle scripts to mysql 809 lokesh kumar 05/03/2012 04:31AM
Re: How to convert oracle scripts to mysql 785 Manoj Bardhan 05/03/2012 10:13PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.