Home » SQL & PL/SQL » SQL & PL/SQL » Advance Constraints. (Oracle10g, Win2003)
Advance Constraints. [message #362779] Thu, 04 December 2008 02:13 Go to next message
weekend79
Messages: 188
Registered: April 2005
Location: Islamabad
Senior Member

HI

Oracle10g
How can make a row read only using constraints , not very much interesting in triggers.
For example where primary key col value is xyz the row cant be edit or delete.
Please advise how?

Wishes


Re: Advance Constraints. [message #362782 is a reply to message #362779] Thu, 04 December 2008 02:18 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Well, I know you don't want to hear it, but this is exactly what triggers are made for.

Best regards.

Frank
Re: Advance Constraints. [message #362787 is a reply to message #362779] Thu, 04 December 2008 02:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First, don't grant the privileges to do it.

Regards
Michel
Re: Advance Constraints. [message #362795 is a reply to message #362787] Thu, 04 December 2008 02:57 Go to previous messageGo to next message
weekend79
Messages: 188
Registered: April 2005
Location: Islamabad
Senior Member

Michel

Yes you are right but this is a case of single db user.
Re: Advance Constraints. [message #362801 is a reply to message #362795] Thu, 04 December 2008 03:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Bad design.
You MUST have an account for at least each type of user (but better for each user).
Even if it is a single user, he must not onw the objects.
So you have 2 accounts: owner and user.

Regards
Michel
Re: Advance Constraints. [message #362811 is a reply to message #362801] Thu, 04 December 2008 03:22 Go to previous messageGo to next message
weekend79
Messages: 188
Registered: April 2005
Location: Islamabad
Senior Member

"owner and user" looks good advise, I will use it in future thanks.
But what about my this case, no way to lock a row or mark as read only?
Re: Advance Constraints. [message #362818 is a reply to message #362811] Thu, 04 December 2008 03:37 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could lock all hthe rows in the table with something like
SELECT * FROM table FOR UPDATE
but then if someone tried to update/delete a row their session would hang and need to be killed.

You could make the tablespace that the table is in Read Only

The simplest and best solution is to create a trigger on the tables that just raises an error - why do you not like triggers?

Something like this:
CREATE OR REPLACE TRIGGER 
BEFORE INSERT OR UPDATE OR DELETE
ON table
BEGIN
  raise_application_error(-20001,'NO!');
END;
/


You could create the SQL for these automatically from a select statement if you've got a lot of tables.
Previous Topic: Invoking unix shell script from PL/SQL (merged)
Next Topic: ORA-00972: identifier is too long while using DBMS_SQLTUNE
Goto Forum:
  


Current Time: Sat Dec 03 20:39:29 CST 2016

Total time taken to generate the page: 0.07701 seconds