| create database trigger [message #354064] |
Thu, 16 October 2008 05:26  |
emadnabil
Messages: 179
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  |
ThomasG
Messages: 3212
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 |
emadnabil
Messages: 179
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 #354141 is a reply to message #354064] |
Thu, 16 October 2008 09:37 |
emadnabil
Messages: 179
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 |
cookiemonster
Messages: 13963
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 |
ThomasG
Messages: 3212
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
