Mysql trigger to update empty field with condition

Question

I have a MySQL table , look like this

t   id   lang   title
1   7   en_UK   my_title
1   7   kh_KH

I want write a trigger that update title to my_title with the same id is 7

Result

t   id   lang   title
1   7   en_UK   my_title
1   7   kh_KH   my_title

From my understanding.

DELIMITER $$

CREATE TRIGGER upd_title BEFORE UPDATE ON `term`
FOR EACH ROW BEGIN
  IF (NEW.title IS NULL OR NEW.title= '') THEN
        SET NEW.title= ??? ;
  END IF;
END$$

DELIMITER ;

[UPDATE1]->not works (trigger not being created)

 DELIMITER $$
DROP TRIGGER IF EXISTS `update_category_after_insert`
CREATE TRIGGER `update_category` AFTER INSERT ON `categories`
 FOR EACH ROW BEGIN

  DECLARE loc_title text;

   IF (NEW.libelle_categorie IS NULL OR NEW.libelle_categorie= '') THEN
      select libelle_categorie into loc_title from categories where NEW.num_noeud= num_noeud and langue = 'en_UK';

      SET NEW.libelle_categorie = loc_title;
   END IF;

END
DELIMITER ;

[UPDATE2]

DELIMITER $$
CREATE TRIGGER ``update_category_after_insert`` BEFORE INSERT ON `categories`
 FOR EACH ROW BEGIN

  DECLARE loc_title text;

   IF (NEW.libelle_categorie IS NULL OR NEW.libelle_categorie= '') THEN
      select libelle_categorie into loc_title from categories where NEW.num_noeud= num_noeud and langue = 'en_UK';

      SET NEW.libelle_categorie = loc_title;
   END IF;

END
DELIMITER ;

Finally , I found the good solution for my case

 UPDATE categories c INNER JOIN categories c2 ON (
     c.num_noeud = c2.num_noeud
) SET c.libelle_categorie = c2.`libelle_categorie`

DaveTheRave · Accepted Answer · 2015-12-17T05:11:09.287

2

Can you clarify?
Are you trying to pull the value from the title column in the 'en_UK' row that exists when you insert an new row with the same id that HAS the title column not entered?

okay

CREATE TRIGGER upd_title BEFORE UPDATE ON `term`
FOR EACH ROW BEGIN

  DECLARE loc_title VARCHAR(20);

   IF (NEW.title IS NULL OR NEW.title= '') THEN
      select title into loc_title from term where NEW.id = id and lang = 'en_UK';

      SET NEW.title= loc_title;
   END IF;

END

This should do the trick.

This was my trigger def:

CREATE DEFINER = CURRENT_USER TRIGGER `therinks`.`glreturndata_BEFORE_UPDATE` BEFORE UPDATE ON `glreturndata` FOR   EACH ROW
BEGIN

    DECLARE myVal  VARCHAR(20);

    if NEW.DESCRIPTION IS NULL  or new.description = '' THEN

        SELECT min(description) into myVal from glreturndata where category = NEW.category and new.idglreturndata <> idglreturndata;
        Set NEW.DESCRIPTION = myval;
    end if;
END

edited Dec 17 '15 at 05:11

answered Dec 17 '15 at 04:09

DaveTheRave

463
2
5

1

you may want another one on the insert before trigger to cover everything. I got the above example to work in my test database – DaveTheRave Dec 17 '15 at 04:35
it doesn't for me here – tree em Dec 17 '15 at 04:59
I did everything through workbench. I altered table.. went to trigger tab.. added a before update trigger Saved it Then went to query and did select * from glreturndata. Then I modified a test row and applied the change.. on the auto reselect.. my previously null description was updated as expected. You may want to make sure your trigger is alive, well, and firing – DaveTheRave Dec 17 '15 at 05:07
if I want after insert then update, is it possible? my case is that: first I create the first row `en_UK ` , then the user create the second row or next row `kh_KH ` with the `null` but I dont to use this null value, and I want to update its values to be the same as `en_UK ` . – tree em Dec 17 '15 at 05:23
then i believe you need a before insert trigger. Try putting the trigger there. The user is inserting a new row with the null.. it is at this point you want to grab the en_UK version of the title and set this new inserted column to it. Try it. It should work – DaveTheRave Dec 17 '15 at 05:36
I have tried, please see my update, but the trigger not created.do you have any suggestion ? – tree em Dec 17 '15 at 05:42
Try using before insert instead of after insert. You cannot use the NEW reference in an After insert trigger.. only in a before insert trigger. – DaveTheRave Dec 17 '15 at 05:49
do you get an error? Also.. what version of mySQL are you using? – DaveTheRave Dec 17 '15 at 05:59
I did not get any error, but I cannot see trigger created.I am using Server version: 5.5.16 MySQL Community Server (GPL) – tree em Dec 17 '15 at 06:02
1

do u have the workbench? I did this test through workbench. I can alter table, and go to the trigger tab.. and create my before insert trigger there. – DaveTheRave Dec 17 '15 at 06:16
anywhere thank you for your help. the trigger is created now, but what I want is after insert , I think I could find another solution. – tree em Dec 17 '15 at 07:04
Thanks so much , finally I found solution (check my update), – tree em Dec 17 '15 at 07:32

score 0 · Answer 2 · edited May 23 '17 at 12:04

It seems that you can't do all this in a trigger. According to the documentation:

Within a stored function or trigger, it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

According to this answer, it seems that you should:

create a stored procedure, that inserts into/Updates the target table, then updates the other row(s), all in a transaction.

With a stored proc you'll manually commit the changes (insert and update). I haven't done this in MySQL, but this post looks like a good example.

Mysql trigger to update empty field with condition

2 Answers2