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.