Using sub query in mysql before insert trrigger

Question

I have a table with a two component key where the first component is a given id from a related table and the second will be sequential number in order to make the primary key unique. A simple example of the table:

CREATE TABLE a_tbl (
    id INT,
    seq_num INT,
    primary key (id, seq_num)
);

Now, the InnoDB engine in MySQL only allows auto increment on the first element of a multi element primary key. Thus, I thought that I could easily handle the assignment of the second element through a BEFORE INSERT trigger, but it turned out not to work.

I have tried in various ways, but cannot get MySQL to accept the syntax and there is no explanation as to what is wrong with the SQL code. Hopefully, someone can can shed some light on where the problem is and how to get the code to work. Here is the SQL trigger code:

CREATE TRIGGER a_tbl_BEFORE_INSERT
BEFORE INSERT ON a_tbl
FOR EACH ROW BEGIN
    SET NEW.seq_num = 1 + (SELECT MAX(COALESCE(seq_num, 0))
                        FROM a_tbl
                        WHERE id = NEW.id
                        );
END

score 0 · Accepted Answer · edited May 23 '17 at 10:28

0

use delimiter:

delimiter |
CREATE TRIGGER a_tbl_BEFORE_INSERT
BEFORE INSERT ON a_tbl
FOR EACH ROW BEGIN
    SET NEW.seq_num = 1 + coalesce((SELECT MAX(seq_num) FROM a_tbl WHERE id = NEW.id), 0);
END
|
delimiter

edited May 23 '17 at 10:28

Community

1
1

answered Feb 21 '16 at 18:05

Gouda Elalfy

6,888
1
26
38

Thanks, that worked :-). This is something to remember. I have worked with many other databases, but this is the first time I have had need for triggers in MySQL. I see that it is mentioned in the documentation, but very far down. I have tried to upvote the answer, but I apparently do not have enough mojo yet to do that - - how very unfair. But I have accepted the solution. – Orion Feb 21 '16 at 18:16
Just a side note: Although the above code can compile, it does in fact not work, since it evaluates to null when there are no existing record with the given id -- despite the coalesce function. The solution is to move the coalesce function outside the SQL select statement: SET NEW.seq_num = 1 + coalesce((SELECT MAX(seq_num) FROM a_tbl WHERE id = NEW.id), 0); – Orion Feb 22 '16 at 07:07
@Orion I update the answer, and I validate your syntax error not trigger logic – Gouda Elalfy Feb 22 '16 at 07:10

Using sub query in mysql before insert trrigger

1 Answers1