CODEWITHSUNDEEP
mysqltriggers
gregthegeek
gregthegeek

Reputation: 1433

Mysql before delete trigger, prevent delete if no where clause?

I know there's no "NEW" value in a delete trigger, so not sure if this is possible. I am trying to use a before delete trigger to log some values, which works fine, except I'd also like to prevent the deletion of any records if the user does not specify anything in the where clause. Basically, I don't want them wiping out the whole table. (app devs with code mistakes actually)

Currently I have:

delimiter $$
create trigger tg_order_shipping_bd
before delete on order_shipping
FOR EACH ROW
begin

        insert into order_shipping_log
        (log_type, inv_nbr, old_tracking_nbr)
        values ( 'DEL', OLD.inv_nbr, OLD.tracking_nbr);

end
$$

So is there a clever way to count rows affected, and if more than 1, prevent delete and error? Maybe just rethinking how we handle this is better. Thanks for any advice.

Upvotes: 3

Views: 2221

Answers (1)

Michael - sqlbot
Michael - sqlbot

Reputation: 179462

That's really a mistake a developer should only make once... but, here's the correct fix if you believe it needs to be system-wide. No cleverness required. (Also, triggers in MySQL are FOR EACH ROW so the answer to your question is "not really" although you could hack it with clever use of a session variable or two).

MySQL has a system variable called sql_safe_updates.

mysql> SET GLOBAL sql_safe_updates = 1;
Query OK, 0 rows affected (0.00 sec)

Disconnect and reconnect so your session picks up the new global value.

mysql> delete from t1;
ERROR 1175 (HY000): You are using safe update mode and you tried to update
                    a table without a WHERE that uses a KEY column

When using the command line client, you can temporarily enable this setting just for your session with this flag:

$ mysql --i-am-a-dummy

(No, I'm not making this up).

Upvotes: 3

Related Questions