OraFAQ Forum: SQL & PL/SQL » Row level lock after updation

Home » SQL & PL/SQL » SQL & PL/SQL » Row level lock after updation (windows 7,oracle 10g, form builder 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Row level lock after updation [message #637340]

Fri, 15 May 2015 05:48

hcdba
Messages: 34
Registered: March 2015

Member

Hello Respected sir,

Here is one scenario i have one table realted to payment module and in that table pl/sql developer used flag=y for payment done and flag=n for pending payment.

Now all those users whoes payment flag value=y cant update and cant perform any action on that row whose value =y how can i prevent this restriction on row level?

how can this possible at database level or any script pls suggest or guide me well.

Report message to a moderator

Re: Row level lock after updation [message #637343 is a reply to message #637340]

Fri, 15 May 2015 06:02

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I'm not sure whether you are asking how to prevent anyone from modifying records whose FLAG = 'Y', or would you like to know how to circumvent that restriction?

Report message to a moderator

Re: Row level lock after updation [message #637345 is a reply to message #637343]

Fri, 15 May 2015 06:20

hcdba
Messages: 34
Registered: March 2015

Member

sir prevent means which row has flag=y means payment done by that user so that particular row i want to lock or called want to disabled so no one can change in future for security purpose?

yes i want to know that how to apply this restriction

Report message to a moderator

Re: Row level lock after updation [message #637348 is a reply to message #637345]

Fri, 15 May 2015 06:28

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

You could use a BEFORE UPDATE trigger.

Something like:

CREATE TRIGGER trg_flag 
  BEFORE UPDATE 
  ON table_name 
  FOR EACH ROW 
BEGIN 
   IF (:NEW.flag = 'N')
      THEN raise_application_error( -20001, 'Cannot update flag');
   END IF;
END;

You could also have a look at Row level security

Report message to a moderator

Re: Row level lock after updation [message #637349 is a reply to message #637348]

Fri, 15 May 2015 06:36

hcdba
Messages: 34
Registered: March 2015

Member

Actually pl/sql developer wants no message display and in back end whole row lock so is there any technique at database level or we have to make any trigger or block for that?

Report message to a moderator

Re: Row level lock after updation [message #637352 is a reply to message #637349]

Fri, 15 May 2015 07:00

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

That might be a slightly modified Lalit's trigger:

CREATE TRIGGER trg_flag 
  BEFORE UPDATE 
  ON table_name 
  FOR EACH ROW 
BEGIN 
   IF (:OLD.flag = 'Y')
      THEN raise_application_error( -20001, 'Cannot update');
   END IF;
END;

Though, what do you mean by saying that a developer (who is it? Is it you? Why do you talk about yourself that way? If it is not you, why doesn't that developer register here and ask those questions?) doesn't want to see any message? How is anyone supposed to know that he/she is not allowed to update records whose FLAG = 'Y'?

Report message to a moderator

Re: Row level lock after updation [message #637353 is a reply to message #637349]

Fri, 15 May 2015 07:37

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

hcdba wrote on Fri, 15 May 2015 12:36

Actually pl/sql developer wants no message display and in back end whole row lock

A permanent row lock is wildly impractical and shouldn't be considered. If developers don't want those rows updated then they should modify the code that updates the table to disallow it. Failing that the trigger is the best approach.

Report message to a moderator

Re: Row level lock after updation [message #637354 is a reply to message #637353]

Fri, 15 May 2015 08:16

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Another way is to create a view upon the table which masks the rows with flag=Y.
No UPDATE can be done on the table itself (no GRANT UPDATE on it) but only on the view.
The view will be something like:

select * from mytable where flag='N' with check option;

Report message to a moderator

Re: Row level lock after updation [message #637357 is a reply to message #637349]

Fri, 15 May 2015 09:12

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

@OP, How is the table updated? As you keep saying developers shouldn't update, do you mean the users directly access the database and issue an update statement? They would be doing it through application. So, can't you handle the business logic such that if the row to be updated has the flag Y then don't fire the update. An IF-ELSE block.

Report message to a moderator

Previous Topic:	Select best 2 value out of 3 three values
Next Topic:	Inserting records in Table using cursor

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 18:24:48 CDT 2024