Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Can't Create Trigger using values from other tables

Status
Not open for further replies.

TheFitz

Programmer
Dec 18, 2003
140
GB
Hi all,

I'm trying my hand at some MySQL. Although I am a seasoned programmer, I have never touched MySQL and I am struggling to create a new trigger using phpMyAdmin.

In short, there are 3 tables, "tblusers" "tblprofile" and "tblfamily". Assuming a user is logged in and they want to create a new profile. Each profile will contain family members. The first family member should be the current user logged in. I am trying to create an after insert trigger on the profile table that takes the "FRIENDLY NAME" from the user table and inserts a new row into the family table, yet the code is throwing the 1064 error.

My code is below:

Code:
BEGIN

DECLARE MYNAME VARCHAR(30);

SET MYNAME = SELECT USR_FRIENDLY_NAME 
             FROM tblusers 
             WHERE USR_ID = ( SELECT PRO_USR_ID 
                              FROM tblprofiles 
                              WHERE PRO_ID = NEW.PRO_ID);

INSERT INTO tblfamily (FAM_PRO_ID, FAM_NAME, FAM_DOB,      FAM_DATE_UPDATED, FAM_USER_UPDATED)
               VALUES (NEW.PRO_ID, MYNAME,   '2017-01-01', NOW(),            1);

END

I'm sure this will be easily fixed, but Google doesn't seem to be my friend today.

Thanks for any help you can give

TheFitz

Fitz
Did you know, there are 10 types of people in this world:
* Those who understand binary
and
* Those who Don't!!
 
Since this is really about MYSQL and has pretty much nothing to do with PHP, I would suggest posting in the forum436 for better and more accurate help.

----------------------------------
Phil AKA Vacunita
----------------------------------
OS-ception: Running Linux on a Virtual Machine in Windows which itself is running in a Virtual Machine on Mac OSx.

Web & Tech
 
OK, will do.

Thanks Vacunita

Fitz
Did you know, there are 10 types of people in this world:
* Those who understand binary
and
* Those who Don't!!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top