MySQL If else construct syntax

Question

Im having some trouble with using the if else construct in sql triggers in MySQL. The tables below are just used for testing.

First Table:

  CREATE TABLE `test2`.`t1` (
  `c1` VARCHAR(5) NOT NULL,
  `c2` VARCHAR(45) NULL,
  `c3` VARCHAR(45) NULL,
  PRIMARY KEY (`c1`));

My second table:

    CREATE TABLE `test2`.`t2` (
  `cc1` VARCHAR(5) NOT NULL,
  `cc2` VARCHAR(45) NULL,
  `cc3` VARCHAR(45) NULL,
  PRIMARY KEY (`cc1`));

and the third:

CREATE TABLE `test`.`t3` (
  `ccc1` VARCHAR(5) NOT NULL,
  `ccc2` VARCHAR(45) NULL,
  `ccc3` VARCHAR(45) NULL,
  PRIMARY KEY (`cc1`));

c1 is the primary key for both cc1 and ccc1.

The trigger I am using is:

DROP TRIGGER IF EXISTS `test`.`t1_AFTER_UPDATE`;

DELIMITER $$
USE `test`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `t1_AFTER_UPDATE` AFTER UPDATE ON 
`t1` FOR EACH ROW 
BEGIN
DECLARE start varchar(1);
set start=substring((c2),1,1);

 IF(start='S') THEN
     UPDATE t2
     SET cc2 = NEW.c2  
     WHERE cc1 = NEW.c1;
 else

    UPDATE t3
    SET ccc2 = NEW.c2  
    WHERE ccc1 = NEW.c1;
 END IF;
  END$$
  DELIMITER ;

Basically, if the user updates t1.c2, then t2.cc2 OR t3.ccc2 must be updated. If t1.c2 of the NEW.c2 starts with the letter S, then t2.cc2 must be updated, else t3.ccc2 must be updated.

The query executes without a hitch but wont let me update the value of c2:

Executing:
UPDATE `test2`.`t1` SET `c2` = '24' WHERE (`c1` = 'M123');

Operation failed: There was an error while applying the SQL script to the database.
ERROR 1054: 1054: Unknown column 'c1' in 'field list'
SQL Statement:

Madhur Bhaiya · Accepted Answer · 2018-11-17 10:03:50Z

2

In a Trigger, we refer to columns in the rows being updated/inserted/deleted by NEW (after operation), and OLD (before operation) keywords.

You will need to use these keywords in order to be able to access the values. You can access the c2 value from the current table using NEW keyword. You can also optimize further by doing the operation only, when the c2 value is actually modified.

DROP TRIGGER IF EXISTS `test`.`t1_AFTER_UPDATE`;

DELIMITER $$
USE `test`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `t1_AFTER_UPDATE` AFTER UPDATE ON 
`t1` FOR EACH ROW 
BEGIN
DECLARE start varchar(1) DEFAULT NULL; -- default it to NULL

-- Check if there is any update on c2 column
IF (NEW.c2 <> OLD.c2)

  -- We access the updated value of c2 using NEW keyword
  SET start = SUBSTRING(NEW.c2,1,1);
END IF;

-- Do update operation on other tables only when start is not null
IF (start IS NOT NULL) THEN

  IF (start = 'S') THEN
     UPDATE t2
     SET cc2 = NEW.c2  
     WHERE cc1 = NEW.c1;
  ELSE

     UPDATE t3
     SET ccc2 = NEW.c2  
     WHERE ccc1 = NEW.c1;
  END IF;
END IF;

END$$
DELIMITER ;

Some noteworthy points from Trigger docs:

Within the trigger body, the OLD and NEW keywords enable you to access columns in the rows affected by a trigger. OLD and NEW are MySQL extensions to triggers; they are not case-sensitive.

In an INSERT trigger, only NEW.col_name can be used; there is no old row. In a DELETE trigger, only OLD.col_name can be used; there is no new row. In an UPDATE trigger, you can use OLD.col_name to refer to the columns of a row before it is updated and NEW.col_name to refer to the columns of the row after it is updated.

edited Nov 17, 2018 at 10:03

answered Nov 17, 2018 at 9:43

Madhur Bhaiya

28.9k10 gold badges53 silver badges61 bronze badges

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

2 Comments

Ilyes Over a year ago

And what is NEW actually? I think you should define what is it :)

Madhur Bhaiya Over a year ago

@Sami hope the new explanation suffices :)

Gordon Linoff · Accepted Answer · 2018-11-17 12:23:03Z

0

Your trigger basically looks okay, except for the definition of start. The column reference is missing a reference to new. I am puzzled by the error message, which should be on c2 rather than c1, though.

In any case, I would simply dispense with it:

DELIMITER $$
USE `test`$$

CREATE DEFINER=`root`@`localhost` TRIGGER `t1_AFTER_UPDATE` AFTER UPDATE ON 
`t1` FOR EACH ROW 
BEGIN
    IF (new.c2 LIKE 'S%') THEN
        UPDATE t2
            SET cc2 = NEW.c2  
            WHERE cc1 = NEW.c1;
    ELSE
        UPDATE t3
            SET ccc2 = NEW.c2  
            WHERE ccc1 = NEW.c1;
    END IF;
END$$

DELIMITER ;

You also need to fix the definition of t3. The primary key column does not exist.

answered Nov 17, 2018 at 12:23

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

MySQL If else construct syntax

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related