Mysql – Creating a MySQL Trigger

MySQLtrigger

I want to create a trigger such that whenever a user is inserted in
TABLE A(userid,type) then his "userid" along with some predefined value, depending in his "type", is inserted in TABLE B(userid,value)

For example in TABLE A if following value is inserted:

userid  |  Type

23      |  admin

then, depending on condition if Type=admin value=1 OR if Type=user value=2, the following insertions are made:

TABLE B
userid  |  Value 

23      |  1

Is this possible?

Best Answer

Try

CREATE TRIGGER tg_ai_tablea
AFTER INSERT ON tablea
FOR EACH ROW
  INSERT INTO tableb (userid, value)
  VALUES (NEW.userid, CASE NEW.type WHEN 'admin' THEN 1 WHEN 'user' THEN 2 END);

Here is SQLFiddle demo

You can also consider instead of storing value just evaluating it on the fly 

SELECT b.userid, 
       CASE a.type WHEN 'admin' THEN 1 WHEN 'user' THEN 2 END value
       -- , other columns from tableb
  FROM tableb b JOIN tablea a
    ON b.userid = a.user.id;
Related Question