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: lokesh kumar ()
Date: May 02, 2012 06:02AM

Hi Irek,

I have written the following test trigger to hash the column(first_name) inside a table.

The description of the table is

'id', 'int(11)', 'YES', '', NULL, ''
'first_name', 'varchar(200)', 'YES', '', NULL, ''
'last_name', 'varchar(15)', 'YES', '', NULL, ''
'start_date', 'date', 'YES', '', NULL, ''
'end_date', 'date', 'YES', '', NULL, ''
'salary', 'float(8,2)', 'YES', '', NULL, ''
'city', 'varchar(10)', 'YES', '', NULL, ''
'description', 'varchar(15)', 'YES', '', NULL, ''


The rows of the table are

1, lokesh, Martin, 1996-07-25, 2006-07-25, 600.00, Toronto, Programmer
2, chris, Mathews, 1976-03-21, 1986-02-21, 600.00, Vancouver, Tester
3, loknath, VARMA, 1976-03-21, 1986-02-21, 600.00, Vancouver, Tester
4, kumar, MOHAN, 1976-03-21, 1986-02-21, 600.00, Vancouver, Tester

The test trigger is

DELIMITER $$
CREATE TRIGGER colors_db.HASHPASSWORD_PARENT
AFTER INSERT ON colors_db.employee
FOR EACH ROW
BEGIN
DECLARE ENCR_DATA VARCHAR(100);
DECLARE REC VARCHAR(100);
/* CREATE A HASH OF PASSWORD USING MD5 */
SET REC =(SELECT first_name FROM colors_db.employee WHERE id=NEW.id);
SET ENCR_DATA = MD5(REC);
/* UPDATE THE ENCRYPTED PASSWORD HASH ON PASSWORD COLUMN */
UPDATE colors_db.employee SET first_name = ENCR_DATA WHERE id = NEW.ID;
END;

The trigger is executing fine but the columns data inside the table is not affecting.

I have questions with the following statement in the trigger
SET REC =(SELECT first_name FROM colors_db.employee WHERE id=NEW.id);
whether this is correct or not.

Please correct the trigger if i make any mistake.
Please try to run the program at ur end.

How to pass the selected rows into md5().
Please suggest me the appropriate solution.

Regards,
Lokesh

Options: ReplyQuote


Subject Views Written By Posted
How to convert oracle scripts to mysql 2210 lokesh kumar 04/27/2012 03:21AM
Re: How to convert oracle scripts to mysql 1096 irek kordirko 04/27/2012 03:22PM
Re: How to convert oracle scripts to mysql 1041 lokesh kumar 05/02/2012 06:02AM
Re: How to convert oracle scripts to mysql 851 Peter Brawley 05/02/2012 08:27PM
Re: How to convert oracle scripts to mysql 842 lokesh kumar 05/03/2012 04:31AM
Re: How to convert oracle scripts to mysql 813 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.