0

Trying to use a trigger to a) fill in a new column top_category with values left of a colon, found in category, then b) If there is no colon, then put entire insert value into top_category. I can't get this second trigger structured to create.

https://dbfiddle.uk/jwLle13e

CREATE TABLE qn_txs (
  id SERIAL PRIMARY KEY,
  category VARCHAR(250),
  top_category VARCHAR(250)
);

-- Stage 1 trigger works
--  CREATE TRIGGER `t_add_top_category1` BEFORE INSERT ON `qn_txs` 
--    FOR EACH ROW 
--  SET NEW.`top_category` = LEFT(NEW.category, INSTR(NEW.category, ":") - 1);

DELIMITER //
CREATE TRIGGER `t_add_top_category2` BEFORE INSERT ON `qn_txs` 
     FOR EACH ROW BEGIN
         SET NEW.`top_category` = LEFT(NEW.category, INSTR(NEW.category, ":") - 1)
         -- make sure top_category is not null if there is a value in category
            IF (NEW.`top_category` = '')
             THEN
              SET NEW.`top_category` = NEW.`category`;
           END IF;
END//

DELIMITER;

INSERT INTO qn_txs (category)
VALUES ('I should also be a top cat'), ('bananas:green'), ('baseball:table'), (''), ('Very long string: with spaces');

SELECT * FROM qn_txs;

Here's a table of the desired output after the insert is complete.

id category top_category
1 I should also be a top cat I should also be a top cat
2 bananas:green bananas
3 baseball:table baseball
4
5 Very long string: with spaces Very long string
2
  • For the first VALUE, you want that phrase in category also? Show us thedelivered result and the desired result after the INSERT.
    – Rick James
    Commented Oct 18, 2023 at 23:42
  • Here's another attempt at the trigger, but it still fails to create and errors are not helpful : ``` DELIMITER // CREATE TRIGGER t_add_top_category BEFORE INSERT ON qn_txs FOR EACH ROW BEGIN IF(INSTR(NEW.category, ":") = 0) THEN SET NEW.top_category = NEW.category; ELSE SET NEW.top_category = LEFT(NEW.category, INSTR(NEW.category, ":") - 1); END IF; END // DELIMITER ; ```
    – br8k
    Commented Oct 19, 2023 at 1:37

1 Answer 1

1
CREATE TRIGGER `t_add_top_category2` 
BEFORE INSERT ON `qn_txs` 
FOR EACH ROW
SET NEW.top_category = SUBSTRING_INDEX(NEW.category, ':', 1);

https://dbfiddle.uk/_ru7qse3

1
  • Brilliant! Thank you!
    – br8k
    Commented Oct 19, 2023 at 13:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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