Thanks so much, gmanish! You helped me work around an impasse.
Having established the triggers, I now have a small problem that I think you could steer me straight on (as it seems to me that you know your SQL).
The problem is that, when the trigger is set on the comprofiler table, the initial 'Confirm your Registration' email is not sent by the system. However, when the new user tries to log on, the email is resent just fine.
If I drop the trigger, the initial email is fired off immediately, so there must be something in the trigger interfering with the process.
I did need to modify your original code a bit to account for the differences in our databases... the biggest issue being in Country names, as I use USA and not 'United States' as in VM.
[code:1]
create trigger `Mydb`.`sync_vm` AFTER INSERT on `Mydb`.`jos_comprofiler`
for each row BEGIN
DECLARE MY_FNAME VARCHAR(64) ;
DECLARE MY_LNAME VARCHAR(64) ;
DECLARE MY_EMAIL VARCHAR(64) ;
DECLARE MY_STATE_2_CODE VARCHAR(64) ;
DECLARE MY_STATE VARCHAR(64) ;
DECLARE MY_COUNTRY_3_CODE VARCHAR(64) ;
DECLARE MY_COUNTRY_ID INT(11) ;
IF not exists (SELECT * FROM jos_vm_user_info WHERE user_id = NEW.id AND address_type = 'BT') Then
SELECT substring_index(name,' ',1), substring_index(name,' ',-1), email INTO MY_FNAME, MY_LNAME, MY_EMAIL
from jos_users where id = NEW.id;
SELECT country_3_code, country_id INTO MY_COUNTRY_3_CODE, MY_COUNTRY_ID
FROM jos_vm_country
WHERE country_name = NEW.cb_mailingcountry ;
IF (MY_COUNTRY_3_CODE IS NULL) Then SET MY_COUNTRY_3_CODE = 'USA' ;
END IF ;
SELECT state_2_code INTO MY_STATE_2_CODE
FROM jos_vm_state
WHERE state_name = (SELECT SUBSTRING_INDEX(NEW.cb_mailingstate,' - ',1)) ;
/*AND country_id = MY_COUNTRY_ID;*/
INSERT INTO jos_vm_user_info (user_info_id, user_id, address_type, address_type_name, last_name, first_name, phone_1, phone_2, address_1, address_2, city, state, country, zip, user_email, perms) VALUES (concat('fromcb-', NEW.user_id), NEW.user_id, 'BT', '-default-', MY_LNAME, MY_FNAME, NEW.cb_primaryphone, NEW.cb_secondphone, NEW.cb_mailingstreet, NEW.cb_mailingapt, NEW.cb_mailingcity, MY_STATE_2_CODE, MY_COUNTRY_3_CODE, NEW.cb_mailingzip, MY_EMAIL, 'shopper');
END IF;
END;
[/code:1]
I don't know if you can spot any 'howlers' in my code or you have any ideas as to what might be happening, but I would sincerely appreciate your help.
Thanks - Anton
Post edited by: antonstewart, at: 2008/12/15 15:42