Can a mysql trigger be used to block an insert?

Question

I've got a mail queue table, and a email black list table. Inserts to the mail queue table are made in a zillion (literally, I counted) places throughout the code. I've been tasked with blocking emails to people on the black list. Can I make a trigger on the mail queue table that rejects inserts if the address is in the black list table?

Is there possibly a better way of doing this?

possible duplicate of [TRIGGERs that cause INSERTs to fail? Possible?](http://stackoverflow.com/questions/229765/triggers-that-cause-inserts-to-fail-possible) — Marc B, Feb 14 '11 at 18:35
Check the link above in the 'possible dupe' comment. It has exactly what you need - a database "on before insert" trigger that causes the insert to fail. — Marc B, Feb 14 '11 at 18:36
Thanks for the reply, so there's no graceful way to to this? — Parris Varney, Feb 14 '11 at 18:41
@Marc B: If you stick that link in a comment I can accept it. — Parris Varney, Feb 14 '11 at 21:41
Thanks, but I'm not going to take points for someone else's answer. — Marc B, Feb 14 '11 at 21:49
I searched SO for it, didn't come up. No biggie, thanks for getting me the answer. though. — Parris Varney, Feb 15 '11 at 02:13

score 3 · Accepted Answer · answered Feb 16 '11 at 17:26

From the Book 'High Performance MySQL Second Edition':

Sometimes you can even work around the FOR EACH ROW limitation. Roland Bouman found that ROW_COUNT( ) always reports 1 inside a trigger, except for the first row of a BEFORE trigger. You can use this to prevent a trigger’s code from executing for every row affected and run it only once per statement. It’s not the same as a per-statement trigger, but it is a useful technique for emulating a per-statement BEFORE trigger in some cases. This behavior may actually be a bug that will get fixed at some point, so you should use it with care and verify that it still works when you upgrade your server. Here’s a sample of how to use this hack:

CREATE TRIGGER fake_statement_trigger
BEFORE INSERT ON sometable
FOR EACH ROW
BEGIN
DECLARE v_row_count INT DEFAULT ROW_COUNT( );
IF v_row_count <> 1 THEN
-- Your code here
END IF;
END;

Can a mysql trigger be used to block an insert?

1 Answers1