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-17 05:11:09Z

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, 2015 at 5:11

answered Dec 17, 2015 at 4:09

DaveTheRave

4632 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

DaveTheRave Over a year ago

you may want another one on the insert before trigger to cover everything. I got the above example to work in my test database

tree em Over a year ago

it doesn't for me here

DaveTheRave Over a year ago

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

tree em Over a year ago

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 .

DaveTheRave Over a year ago

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.

|

Community · Accepted Answer · 2017-05-23 12:04:04Z

0

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.

edited May 23, 2017 at 12:04

CommunityBot

11 silver badge

answered Dec 17, 2015 at 4:27

Gaurav Lad

1,8081 gold badge17 silver badges31 bronze badges

Collectives™ on Stack Overflow

Mysql trigger to update empty field with condition

2 Answers 2

12 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related