How can I cancel insertion inside the trigger?

Question

Inside the History table, The COL1 shouldn't be like COL2, If was equal then cancel the insertion.

How can I do it?

Read about `SIGNAL` in the manual: https://dev.mysql.com/doc/refman/8.0/en/signal.html — Bill Karwin, May 30 '22 at 17:43
Are you sure you really want to do this in a trigger? Why not create a constraint for that? — Jonas Metzler, May 30 '22 at 17:47
@JonasMetzler Okay, The history table was an example only, The real table is called Followers with two columns, Follower_Id and Following_Id, I don't want to allow the user to follow himself, I make conditions on the client side but I want to increase the safety — Taha Sami, May 30 '22 at 17:51
*I don't want allow the user follow himself* Table-level CHECK constraint solves this easily. PS. Common XY-problem.. — Akina, May 30 '22 at 17:52
Ok, but this is no answer. Why do you want to create a trigger instead of a check constraint? Is there a special reason for that? — Jonas Metzler, May 30 '22 at 17:53
@JonasMetzler Because [Barmar said in his answer](https://stackoverflow.com/a/72408295/18753735) that is not possible in MySQL version below 8.0.16, And suggested to use Triggers — Taha Sami, May 30 '22 at 17:55
Ok, thanks. You didn't tell us before that you're using an older MySQL version ;) — Jonas Metzler, May 30 '22 at 17:57
@JonasMetzler I tried many ways like unique key index but does not work as I expected, I hope I find a way to do that without triggers or with, Not matter, Thanks — Taha Sami, May 30 '22 at 18:01
@BillKarwin Signal works with MySQL 8.0 and above but I have 5.7 — Taha Sami, May 30 '22 at 18:30
*Is it possible to do it without triggers?* On MySQL 5.7 - no, version 8.0.16 needed. — Akina, May 30 '22 at 18:37
@Akina I'm using Bluehost and I don't know if I can update the MySQL version, Anyway do you know how can I cancel the insert according to the condition above? — Taha Sami, May 30 '22 at 18:49
SIGNAL has been supported for years. It does not require MySQL 8.0. I only linked to the 8.0 manual because it's the current version. — Bill Karwin, May 30 '22 at 18:57
https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html but if this is for a user-defined error, just use `'45000'`. — Bill Karwin, May 30 '22 at 21:26

score 2 · Accepted Answer · answered May 30 '22 at 18:55

2

For MySQL 5.7 use

CREATE TRIGGER prevent_self_referencing
BEFORE INSERT
ON tablename
FOR EACH ROW
BEGIN
    IF NEW.column1 = NEW.column2 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Self-referencing not allowed.';
    END IF;
END

And the same trigger for BEFORE UPDATE.

In shown window insert only 3 code lines, the whole IF statement.

answered May 30 '22 at 18:55

Akina

39,301
5
14
25

It's working, I think to BEGIN and END is not required here because that better to add it when we have multiple commands. Could you tell me more about Signal? Is it related to the client side? – Taha Sami May 30 '22 at 19:05
MESSAGE_TEXT is optional? – Taha Sami May 30 '22 at 19:10
@T38416 [SIGNAL Statement](https://dev.mysql.com/doc/refman/8.0/en/signal.html) – Akina May 30 '22 at 19:34

How can I cancel insertion inside the trigger?

1 Answers1