Creating a trigger that insert something if

Question

I need a bit of help with creating a trigger in mysql: I have a column named “country” and another one named “tag”.

Everytime when someone insert in the city “Los Angeles” for example, I want that my trigger to insert in “tag” column the text “is from California”.

Edit:

delimiter //
CREATE TRIGGER update_tag AFTER UPDATE ON users
    FOR EACH ROW
    BEGIN
        IF (city = 'Los Angeles') THEN 
            INSERT INTO users(tag) VALUES (California);
        END IF;
    END;//
delimiter ;

That seems to be executed with no errors, but is not inserting anything in "tag" column, Any ideea why?

PS. I would appreciate from the ones that rated this post with "-" to write me a PM and tell me what I did wrong :). Thank you.

so how do you tag if the user adds say New York City ? How does the code going to know the tag name ? — Abhik Chakraborty, May 21 '14 at 07:33
Do you want us to do the research for you? Did you have a look somewhere? For example... here: http://stackoverflow.com/questions/6296313/mysql-trigger-after-update-only-if-row-has-changed — Frazz, May 21 '14 at 07:35
to me it looks like this is better solved on server side code (for example php), as you need to map city (country?!) to state (tag?). Do you need to solve this in MySQL? — Bojan Kovacevic, May 21 '14 at 07:39
Not really needs to be done in MySql. I want just to learn a few about triggers. — user140102, May 21 '14 at 07:51

score 2 · Accepted Answer · answered May 21 '14 at 08:35

You cannot use an insert statement to update the row you are currently processing. You should use the SET NEW.cxy = "" syntax.

I have prepared a working sqlfiddle for you, which hopefully shows want you wanted to achieve.

CREATE TABLE users (
  id int auto_increment PRIMARY KEY,
  `city` varchar(255),
  `tag` varchar(255)
  )//


CREATE TRIGGER update_tag BEFORE UPDATE ON users
    FOR EACH ROW
    BEGIN
        IF (NEW.city = 'Los Angeles') THEN 
            SET NEW.tag = "California";
        END IF;
    END//

INSERT INTO users VALUES (1, 'test', '')//
UPDATE users SET `city` = 'Los Angeles'//

Please notice that this is also a BEFORE UPDATE trigger, so that your changes are saved as well.

If one issues a SELECT * FROM users one receives a single row with

1     Los Angeles    California

There is also a page in the MySQL manual containing trigger examples. You should read that thoroughly.

I tried now to use it for data sended via a html form. When a user sayes that his name is: John Doe and he is from Los Angeles, I want to add him the tag "California". Is there somnething that needs to be added and I'm missing? — user140102, May 21 '14 at 11:49
No, you have to execute the `create table` and the `create trigger` statement once, for example with PHPMyAdmin. If you then insert something to the table, the trigger is executed. — Ulrich Thomas Gabor, May 21 '14 at 12:31

Creating a trigger that insert something if

1 Answers1