CODEWITHSUNDEEP
mysqlsqltriggers
Parris Varney
Parris Varney

Reputation: 11478

Can a mysql trigger be used to block an insert?

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?

Upvotes: 1

Views: 2179

Answers (1)

John M
John M

Reputation: 14668

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;

Upvotes: 3

Related Questions