INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Can't Create Trigger using values from other tables

Can't Create Trigger using values from other tables

Can't Create Trigger using values from other tables

(OP)
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 --> MySQL

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

Fitz
Did you know, there are 10 types of people in this world:
* Those who understand binary
and
* Those who Don't!!

RE: Can't Create Trigger using values from other tables

"and I am struggling to create a new trigger using phpMyAdmin."

Are your end users going to be using phpMyAdmin, a MySQL command prompt or some other tool that allows them to generate queries and they have appropriate permissions to the database?

If not, I don't quite understand why you are. Surely you should be writing/building a GUI that handles user input and then inserts the data into the tables as appropriate.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.

Never mind this jesus character, stars had to die for me to live.

RE: Can't Create Trigger using values from other tables

Quote (ChrisHirst)


Are your end users going to be using phpMyAdmin, a MySQL command prompt or some other tool that allows them to generate queries and they have appropriate permissions to the database?

A trigger is a conditional set of instructions stored in the Database, it does not rely on the method of input to the DB. When a condition is met in the database, i.e a table is changed, an insert statement is going to be run, a field is updated etc... the trigger fires and does things to the data. Creating it on phpmyadmin or directly through mysql is irrelevant to the end user. The end user does not need to use the same tool that was used to create the trigger for it to fire.

The Trigger will fire when the condition is met in the database.

Creating triggers is part of creating a gui that handles user input, its just kept on the database side.






----------------------------------
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

RE: Can't Create Trigger using values from other tables

Quote (vacunita)

A trigger is a conditional set of instructions stored in the Database, it does not rely on the method of input to the DB


Yes I already know this, I am attempting to find out exactly what the OP is trying to do. Reading the post it would appear that the OP is actually trying to create, the fact that this same question is in the PHP forum, a fact you already are aware of, suggests that the REAL solution lies elsewhere. Therefore, I would like to know more.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.

Never mind this jesus character, stars had to die for me to live.

RE: Can't Create Trigger using values from other tables

But then I fail to understand the relevance of what they are using to create the trigger. A trigger is independent of the tool used to create it.

I'm not versed in trigger creation, so can't answer directly, but perhaps a better question would be: What is the exact text of the 1064 error?

At least that way we know exactly what MYSQL is complaining about.

Questioning the reasons behind the creation of the trigger or how its being created itself does not really help the OP.

Best to address the actual error they are receiving first.

----------------------------------
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

RE: Can't Create Trigger using values from other tables

(OP)
Hey all,

@Chris, yes, I am planning to have a user input, probably in PHP - also need to learn this.

For the moment, I am just setting up the DB.

In short, assume a 1-many relationship between users and profiles and profiles and family.

A user creates a new record in profiles (via a frontend insert), I want the trigger to pickup data from the related user record and insert it as the first record in the family table for that profile.

Am I making any sense?

I've managed to get the trigger on a new user to create a new profile record so I believe I understand the insert process correctly. I think my issue is return data into the MYNAME variable? but I could be wrong. . .

Thanks for your help,

Fitz
Did you know, there are 10 types of people in this world:
* Those who understand binary
and
* Those who Don't!!

RE: Can't Create Trigger using values from other tables

Regarding the OP, according to https://stackoverflow.com/questions/5003869/error-... (I'm not too familiar with MySQL triggers, so I looked it up), a trigger is not the same thing as a stored procedure, so there's no need for BEGIN... END. So... the syntax would be something like:

CODE --> SQL

CREATE TRIGGER tblprofile_After_Insert
AFTER INSERT ON tblprofile
FOR EACH ROW
INSERT INTO tblfamily (FAM_PRO_ID, FAM_NAME, FAM_DOB,      FAM_DATE_UPDATED, FAM_USER_UPDATED)
SELECT
NEW.PRO_ID, (SELECT USR_FRIENDLY_NAME
FROM tblusers
WHERE USR_ID = ( SELECT PRO_USR_ID
FROM tblprofile
WHERE PRO_ID = NEW.PRO_ID)) AS MYNAME,
'2017-01-01' AS DOB, NOW() AS Date_Updated, 1 AS USER_Updated
;

(not tested.)

...also, in your code, you had "FROM tblprofiles" (plural), when you listed the table name as tblprofile in your question. smile I also recommend just testing the "SELECT" portion of the insert statement in your MySQL client, if it throws errors.

Sorry about the multiple edits; I found a few issues with my code and had to fix.

Katie

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close