0

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?

0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.