Re: How to convert oracle scripts to mysql
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