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
Last activity: Oct 19, 2023, 07:36 AM