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.
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 |
categoryalso? Show us thedelivered result and the desired result after theINSERT.t_add_top_categoryBEFORE INSERT ONqn_txsFOR 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 ; ```