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

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;

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial