1

I am loading .csv files into Mariadb 10.6 using LOAD DATA LOCAL INFILE.

There is a DATE column called posted_on in the inbound data, and I need store the month name in another field monther, ideally as part of the loading process.

If posted_on = '2023-10-13' then monther = 'October'

I assumed a trigger would be appropriate and have tried variations (added NEW., before/after) on the following:

CREATE TRIGGER `t_add_monther` after INSERT ON `qn_txs` 
     FOR EACH ROW 
 UPDATE qn_txs SET NEW.qn_txs.`monther` = MONTHNAME(`posted_on`);

But can't get past the dreaded ERROR 1442 (HY000) at line 1: Can't update table 'qn_txs' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Any thoughts? Is a different approach needed?

2 Answers 2

1

Right, you can't UPDATE/DELETE/INSERT in a trigger for the same table. This could cause an infinite loop of triggers.

But you don't need to use UPDATE if you are changing values in the row being inserted. If you SET NEW.ColumnName in a trigger, that changes the value for that column in the row that spawned the trigger.

CREATE TRIGGER `t_add_monther` BEFORE INSERT ON `qn_txs` 
     FOR EACH ROW 
 SET NEW.`monther` = MONTHNAME(NEW.`posted_on`);

Demo: https://dbfiddle.uk/qC9iCUbg

9
  • Makes sense. Unfortunately, the CREATE is throwing an error. Looks good to me though. ``` syntax to use near '.monther = MONTHNAME(posted_on)' at line 3 ``` Oct 18, 2023 at 0:37
  • Sorry, I didn't notice that you had done NEW.tableName.columnName. You don't need the table name in that. It is implicit that NEW.columName is naturally based on the table the trigger is defined for. I have edited my answer above to fix that. Oct 18, 2023 at 2:04
  • 1
    That did it! I never would have figured that out. PS, your book is in my shopping cart. Oct 18, 2023 at 14:25
  • 1
    Thanks for checking out my book! Make sure you get the revised 2022 edition. Unfortunately, I didn't realize that when I renamed it "SQL Antipatterns Volume 1" that Amazon treats that as a whole new book instead of a second edition of the first book. Here's a direct link to the publisher's page: pragprog.com/titles/bksap1/sql-antipatterns-volume-1 Oct 18, 2023 at 14:39
  • 1
    Edited into docs thanks @BillKarwin
    – danblack
    Oct 21, 2023 at 0:19
0

Don't bother with a TRIGGER, use a temporary variable inside the LOAD DATA:

LOAD DATA
...
(a, b, c, @date, e, f, ...) 
SET date = @date,
    monther = MONTH(@date)
;

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.