Home » SQL & PL/SQL » SQL & PL/SQL » Preventing commit in a "Before update" trigger.
Preventing commit in a "Before update" trigger. [message #240519] Fri, 25 May 2007 02:34 Go to next message
truth_bajaj
Messages: 5
Registered: May 2007
Junior Member
Hi,

I am working in a scenario where we use an application that has some states which are changed by users. This is workflow management software. I have complete access to the database where this application is committing data of users and itself.

My motive is to incorporate a conditional state change for this application. That is, I wish to fire a trigger "Before update" which checks the value of a field and based on this value, update is to be completed or prevented.

How can I achieve this?

Thanks,
Sachin
Re: Preventing commit in a "Before update" trigger. [message #240531 is a reply to message #240519] Fri, 25 May 2007 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, this is one the purposes of triggers.

Regards
Michel
Re: Preventing commit in a "Before update" trigger. [message #240586 is a reply to message #240531] Fri, 25 May 2007 04:42 Go to previous messageGo to next message
truth_bajaj
Messages: 5
Registered: May 2007
Junior Member
Michel,

I am very much aware of this but I didn't ask if ti was possible or not. My question is how to do it.

Besides I might face "Mutating table" error if I try to modify the trigger table.
Re: Preventing commit in a "Before update" trigger. [message #240588 is a reply to message #240586] Fri, 25 May 2007 04:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
To be accurate, Michaels answer is quite adequate to answer a question as broad and vague as yours.

When you say that you wish to prevent the update, do you wish an exception to be raised?

Re: Preventing commit in a "Before update" trigger. [message #240589 is a reply to message #240519] Fri, 25 May 2007 04:57 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
create or replace trigger trigname before update on tabname
for each row
begin
   if :NEW.colname = 'XXX' then
      raise_application_error(-20500, 'You cannot use this value');
   end if;
end;
Re: Preventing commit in a "Before update" trigger. [message #240593 is a reply to message #240586] Fri, 25 May 2007 05:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Actually, there is something very wrong with this sentence:
 I didn't ask if ti was possible or not. My question is how to do it.

Surely 'Is it Possible' would be a neccessary prerequisite to knowing how to do it.
Re: Preventing commit in a "Before update" trigger. [message #240600 is a reply to message #240593] Fri, 25 May 2007 05:13 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I have to agree here with the OP.
My first reaction, when I read Michel's reply was 'That is not really what the OP asks'...
Re: Preventing commit in a "Before update" trigger. [message #240603 is a reply to message #240600] Fri, 25 May 2007 05:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I agree, my english knowledged is not sufficient to understand it at first sight but I admit it wanted the code and not just the fact it can be done. Embarassed

Regards
Michel
Re: Preventing commit in a "Before update" trigger. [message #240823 is a reply to message #240603] Sat, 26 May 2007 02:34 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Don't you guys think that it better to user after update trigger to check condition? I say this because before update trigger allows the modification of column whereas after update trigger will not allow it so it is more accurate to check the conditions in after update trigger.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:29259877603106

Re: Preventing commit in a "Before update" trigger. [message #240824 is a reply to message #240823] Sat, 26 May 2007 02:40 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I agree.
When using a before update trigger, there could always be another before update trigger that fires AFTER the check that updates the column (:shiver:)

[Edit: bummer, should have read the link before posting...]

[Updated on: Sat, 26 May 2007 02:41]

Report message to a moderator

Re: Preventing commit in a "Before update" trigger. [message #240838 is a reply to message #240823] Sat, 26 May 2007 07:13 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I agree too for the same reasons.

Regards
Michel
Previous Topic: How to modify this package for Performance tuning (merged)
Next Topic: Trigger
Goto Forum:
  


Current Time: Sun Dec 11 07:54:14 CST 2016

Total time taken to generate the page: 0.07712 seconds