MySQL Forums
Forum List  »  Triggers

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
Re: How to convert oracle scripts to mysql
1518
May 02, 2012 06:02AM


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.