CODEWITHSUNDEEP
phpmysql
maan81
maan81

Reputation: 3609

Trigger in a database that should change values in a table in a different database?

How to create a trigger that is activated by a change in a database which changes values in a table in a different database ?

One database contains the table which has the login information (usernames, passwords) . When a user logs in, values in a table in another database is to be updated.

How could this be achieved ?

[edit]

Sorry, yes, the two different databases are on the same database server.

[Updated]

I have mainDB.users table :

username | password | NumberLogins
---------|----------|--------------
         |          |

and a otherDB.history table :

username | points
---------|---------
         |

How do we increment the corresponding points after a user has logged in ?

USE `mainDB`;

DROP TRIGGER IF EXISTS `updateUserInfo`;

DELIMITER ||

CREATE TRIGGER `updateUserInfo` 
    BEFORE UPDATE ON `users` 
    FOR EACH ROW

    BEGIN
        IF NEW.`NumberLogins`=5 THEN

            UPDATE `otherDB`.`history`
            SET `points` = `points` + 1,
            WHERE 
              `username`=NEW.`username`;

        END IF;

    END ||

I keep getting this error.

 #1442 - Can't update table 'history' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Upvotes: 1

Views: 144

Answers (1)

Itay Moav -Malimovka
Itay Moav -Malimovka

Reputation: 53597

I assume both DBs are on the same server. 

USE name_of_db_trigger_to_be_in;
delimiter ||
create trigger.......

   UPDATE other_db.other_table
   SET other_guys = 'hohoho';


end||

Upvotes: 1

Related Questions