CODEWITHSUNDEEP
oracle-databaseplsqltriggers
UltraCommit
UltraCommit

Reputation: 2286

Oracle PL/SQL: Prevent delete * from table unless primary key specified

In Oracle PL/SQL language, I would like to prevent user from using deleting * from table unless primary key specified.

How could I achieve this?

I've just found a similar question, but in a different language: prevent delete * from table unless primary key specified

Could you please help me to find a solution in Oracle PL/SQL language?

EDIT:

SELECT * FROM V$VERSION;

BANNER,CON_ID
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production,0
PL/SQL Release 12.1.0.2.0 - Production,0
CORE    12.1.0.2.0  Production,0
TNS for IBM/AIX RISC System/6000: Version 12.1.0.2.0 - Production,0
NLSRTL Version 12.1.0.2.0 - Production,0

Upvotes: 0

Views: 833

Answers (2)

APC
APC

Reputation: 146349

As someone observed in the referenced thread, this requirement amounts to I want to slow down every deletion executed in my database. Not to mention that sometimes bulk deletions are necessary.

This is a classic example of a trying to apply a technical fix to what is a political situation, a failure of process. I once knew a developer who initiated a delete of a massive (multi-million row) table because they didn't know how to scope variables in PL/SQL. But it would be wrong to penalize the entire development team because of one person's silly mistake.

The correct solution is to implement one or both of these:

  • make sure your developers (or any user with raw SQL access) understand how to write proper SQL
  • withdraw DELETE privileges from people who can't be trusted with them
  • have decent back-ups so it doesn't matter whether somebody deletes all the records

Upvotes: 2

Roger Cornejo
Roger Cornejo

Reputation: 1547

not sure, but a delete will fail if a constraint is violated, so I'd look to see if you can create a constraint that will be violated (e.g. foreign key constraint where child record exists that must be deleted first).

Upvotes: 1

Related Questions