Home » Developer & Programmer » Forms » create database trigger (oracle 10g , windows xp)
create database trigger [message #354064] Thu, 16 October 2008 05:26 Go to next message
emadnabil
Messages: 163
Registered: August 2007
Senior Member
Hii all

I have a table that i want to make on it a database trigger
from this table there is two columns called status, amount
if the 'status' column contain a value of "APPLIED"
then the 'amount' column cannot be updated

how can i do this
Re: create database trigger [message #354065 is a reply to message #354064] Thu, 16 October 2008 05:43 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
By checking for the :OLD value of the status column, and if it's "APPLIED" either set the :NEW value of amount to :OLD or raise an error in the trigger, depending on how you actually want it.

More info and examples about creating triggers in the documentation.
Re: create database trigger [message #354090 is a reply to message #354064] Thu, 16 October 2008 07:04 Go to previous messageGo to next message
emadnabil
Messages: 163
Registered: August 2007
Senior Member
thanks for reply

i made a code trigger like that
CREATE OR REPLACE TRIGGER LOCK_CASH_UPDATE  BEFORE INSERT OR UPDATE
 OF AMOUNT,STATUS ON AR_CASH_RECEIPTS_ALL
FOR EACH ROW
DECLARE
BEGIN

     IF (:OLD.STATUS =  'APP' ) THEN
     :NEW.AMOUNT := :OLD.AMOUNT;


     END IF;
END;


this code lock the update of 'amount' column


but i have several problem
1. the lock is not shown only when i get out of the screen and enter again (i want to lock inside the screen)

2. every other action except the un update of the 'amount' column is done and this is wrong (i want something like raise form_trigger_failure i mean something to prevent commit)


Re: create database trigger [message #354136 is a reply to message #354090] Thu, 16 October 2008 09:32 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You should have said in the fist place that you wanted a FORMS trigger, not a DATABASE trigger.
Re: create database trigger [message #354141 is a reply to message #354064] Thu, 16 October 2008 09:37 Go to previous messageGo to next message
emadnabil
Messages: 163
Registered: August 2007
Senior Member
I don't want to make a form trigger but a database trigger
but those problem facing me

1. the lock is not shown only when i get out of the screen and enter again (i want to lock inside the screen)

2. every other action except the un update of the 'amount' column is done and this is wrong (i want something like raise form_trigger_failure i mean something to prevent commit)

Re: create database trigger [message #354146 is a reply to message #354064] Thu, 16 October 2008 10:31 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Then don't you just want a trigger that raises an error?
Something like this:
IF :new.amount != :old.amount and :old.status = 'APP' THEN
  raise_application_error......


Have a look at the oracle function raise_application_error.

You might also want to consider what should happen if someone updates the value of status from APP to something else.
Re: create database trigger [message #354169 is a reply to message #354146] Thu, 16 October 2008 15:56 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

I don't want to make a form trigger but a database trigger



Quote:

...the lock is not shown only when i get out of the screen and enter again...
...i want something like raise form_trigger_failure...



Those two statements are contradictory. A database trigger can do *nothing* with forms and/or screens.

Previous Topic: Image not saving
Next Topic: FRM-30015: Ambiguous item name using LOV
Goto Forum:
  


Current Time: Fri Dec 02 22:53:20 CST 2016

Total time taken to generate the page: 0.12165 seconds