Avatar of TonyReba
TonyReba
Flag for United States of America asked on

trigger mysql database

I have two tables, one is called

userprofile  the other is called users

Everytime a new record is inserted into userprofile I want to update the table 'users'

this a mockup structure

table -userprfile  
id
uid
name
department

table-users
id
name
email
department  ( want to update this field on every insert from the userprofiletable)

As now  , the table userprofile matches users on uid = id    , there is no contratins, or foreign keys, which I probably would need, not sure,,,

How can I do this.? Thanks in advance......
PHPMySQL ServerSQL

Avatar of undefined
Last Comment
Kevin Cross

8/22/2022 - Mon
Sandeep Kothari

user mysql procedure ... http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

or

simple is to write a update query for users table after the insert query for userprofile so it would be like..

insert into userprofile......
update users set.......


TonyReba

ASKER
thanks kshna , but actually i have a trigger but not working,  


the purpose is to get the department from table userprofile then update table users where the uid from table userprofile = id table users....

can you suggest which code change is needed..?
BEGIN

  DECLARE MY_DEPT VARCHAR(255);
  DECLARE MY_ID   INT(11);


  SET MY_DEPT = '';
  SET MY_ID = '';

  IF EXISTS (SELECT *
             FROM
               jos_dhruprofile
             WHERE
               uid = jos_users.id) THEN
     
  UPDATE jos_users
  SET
    department = MY_DEPARTMENT
  WHERE
    id = MY_ID;
  END

Open in new window

theGhost_k8

try following update manually to test if it works and use it in trigger:
Update jos_users u,  jos_dhruprofile d  set  department = MY_DEPARTMENT where d.uid=u.id and u.id=MY_ID;
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
theGhost_k8

also post your trigger's complete definition using
SHOW CREATE TRIGGER
TonyReba

ASKER
HI find attached the diagram and the trigger,,,

thanks
CREATE 
	DEFINER = 'root'@'xx.xxx.xx.xx'
TRIGGER db_name.`insert department`
	AFTER INSERT
	ON jos_dhruprofile
	FOR EACH ROW
BEGIN
  UPDATE jos_users
SET
  jos_user.department = jos_dhruprofile.department
WHERE
  jos_dhruprofile.uid = jos_user.id;
END

Open in new window

27249769.png
ASKER CERTIFIED SOLUTION
Kevin Cross

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question