Sample Header Ad - 728x90

Mariadb using IF in trigger to fill null value on insert

0 votes
1 answer
250 views
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 |
Asked by br8k (115 rep)
Oct 18, 2023, 07:49 PM
Last activity: Oct 19, 2023, 07:36 AM