0

Is there any utility / function either in mysql / oracle that prevent I issue some delete/update command wrongly, a message must prompt at command_line* (if possible in ubuntu) asking whether "you sure to run this command" , if yes then delete/update command will execute?

I have googled this, but not a specific solution is provided for this. I tried SOURCE also, but it is not exactly what I want.

Do any one have any idea over this?

If I'm about to fire update command on > 100 000 records, a prompt should come asking "do you really want to run?", like in Unix if we use rm filename, it asks if you are you sure you want to delete?

4
  • 1
    DML: It's called commit in oracle ... Don't know if you can do this with a trigger :) I reckon you can.
    – Munchi
    Dec 6, 2013 at 9:31
  • Things like that are usually implemented in the SQL client you are using.
    – user1822
    Dec 6, 2013 at 9:37
  • 1
    In Oracle you can ROLLBACK. Anyway, sounds like a silly requirement. If you're unsure whether you want to run the update/delete, don't press enter in the first place!
    – Philᵀᴹ
    Dec 6, 2013 at 12:16
  • Muchi Phil - please read my edit part..yes rollback is option..but its something different which i dnt want here..what happened today is i fired update t1 set name='some value' :..i forgot to add where and this disturbed whole data and auto commit was on.. Dec 6, 2013 at 19:21

2 Answers 2

4

if i issue some delete/update command wrongly

There is no such thing as a "wrong" delete or update command unless it is "wrong" in the sense of being syntactically invalid, referencing nonexistent objects, or attempting something for which the user does not have permission... and those aren't going to execute anyway.

So you must be talking about something like this:

DELETE FROM t1; -- not wrong, deletes all of the rows by excluding none
UPDATE users SET email = 'test@localhost'; -- not wrong, updates all rows with same value

There is sure-fire no way for the system to correctly guess "hey, that seems wrong" and ask you to rethink your decision. The DBMS's job is to do what you ask in the most optimal way, not second-guess whether you know what you're doing.

MySQL does have a mode that tries to protect you, by stopping you from executing updates or deletes that aren't using an index to find rows, with the --i-am-a-dummy flag that enables sql_safe_updates on a per-connection basis -- or you can enable that setting at a global level on the server.

$ mysql --i-am-a-dummy
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4642931
Server version: 5.5.30 MySQL Community Server (GPL)

mysql> use sakila;
Database changed

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

I would argue that this fixes the wrong problem, which is that you have privileges granted to people that they are not qualified to hold.

You could, of course, disable autocommit, requiring you to type COMMIT; to actually commit your DML changes to the database, but that's only true and fully helpful if you're exclusively using a transactional storage engine like InnoDB, and not MyISAM, so I don't know how much that solves.

Those blessed with extra lazyiness will figure out that they can type ; COMMIT; at the end of every statement (true in the mysql cli, not universally).

The person who is sufficiently conscientious to think before committing is probably sufficiently conscientious to not type queries that don't make sense.

8
  • 1
    I haven't seen i-am-a-dummy in years. +1 !!! Dec 6, 2013 at 17:12
  • @miachel please check my edit part...sorry i was unable to communicate properly Dec 6, 2013 at 19:15
  • @user21546 yes, that is what I assumed you were asking, and that is the question I answered. There is not an "are you sure?" option, but there is a "this is probably not what you want" option provided in the mysql command line client. mysql --i-am-a-dummy or the slightly more polite mysql --safe-updates will start the client in a mode that prevents queries like the one you ran today. You can also set this as default behavior. Dec 6, 2013 at 19:26
  • 2
    "I would argue that this fixes the wrong problem, which is that you have privileges granted to people that they are not qualified to hold." -- That alone should be the answer to the question. On the Oracle size of things - you're paying license fees in the tens of thousands at least, so why skimp on staff?
    – Philᵀᴹ
    Dec 7, 2013 at 0:01
  • 1
    Heh, +1 just for the --i-am-a-dummy! Perhaps it could be added that it doesn't protect you from DDL that autocommit, like DROP TABLE ..; or ALTER TABLE DROP COLUMN ...; Jan 8, 2018 at 11:26
-3

You can use

The following is the syntax to create a trigger in MySQL:

CREATE TRIGGER { BEFORE | AFTER } { INSERT | UPDATE | DELETE } ON <table name>
FOR EACH ROW <triggered action>

4
  • 2
    Triggers do not allow for any prompting mechanisms for bulk operations. Triggers are row-by-row stored procedures and do not fit the paradigm described in the question. Dec 6, 2013 at 19:33
  • @RolandoMySQLDBA thanks and excuse me I'm not very good in English Dec 6, 2013 at 19:34
  • so , it is not possible to do this !!! Dec 10, 2013 at 12:24
  • @AnkitKapoor, no, this not an answer to your question. Jan 6, 2018 at 13:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.