Home » Developer & Programmer » Application Express & MOD_PLSQL » Deleting a record (APEX 4.2.0.00.27)
icon4.gif  Deleting a record [message #591186] Thu, 25 July 2013 07:14 Go to next message
yashiindi
Messages: 8
Registered: July 2013
Junior Member
Hi Guys, i need help here. I have an apex application and a table Training_tb that i interacts with.
I have a composite Primary key consisting of three fields(emp_code, Budget_year and training Desciprtion)
Now my problem is, working from the application i want to edit the field (training description) which is a primary key, this is imposible since you cant edit a Primary key field. i then decided to create a trigger that fires when i want to update that field. What should happens is that the old record should be deleted upon clicking the 'save changes' button and everything will then just look as if i am submitting a new entry.

The triggers look like this but its not working .

CREATE OR REPLACE TRIGGER CDRUSER.TBM_REPLACE_RECORD
BEFORE UPDATE
OF TBM_COURSE_NAME, TBM_BUDGET_YEAR
ON CDRUSER.TBM_TRAINING_BUDGET_TB
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
tmpVar NUMBER;
BEGIN
IF UPDATING
THEN


INSERT INTO TBM_TRAINING_BUDGET_TB (TBM_EMP_CODE,
TBM_DEPT,
TBM_EMP_NAME,
TBM_TRAINING_TYPE,
TBM_COURSE_NAME,
TBM_DURATION,
TBM_INSTITUTION,
TBM_PLACE)
VALUES (:NEW.TBM_EMP_CODE,
:NEW.TBM_DEPT,
:NEW.TBM_EMP_NAME,
:NEW.TBM_TRAINING_TYPE,
:NEW.TBM_COURSE_NAME,
:NEW.TBM_DURATION,
:NEW.TBM_INSTITUTION,
:NEW.TBM_PLACE);

DELETE FROM TBM_TRAINING_BUDGET_TB
WHERE TBM_EMP_CODE = :new.TBM_EMP_CODE
AND TBM_COURSE_NAME = :OLD.TBM_COURSE_NAME;

COMMIT;
END IF;

COMMIT;
END tbm_replace_record;
/

any help will be very apriciated Cool
Re: Deleting a record [message #591191 is a reply to message #591186] Thu, 25 July 2013 07:37 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.
Instead of posting with color that are unreadable and hurt eyes, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Quote:
The triggers look like this but its not working


Which does not help us in any way to know what "its not working" means and when "its not working".

Regards
Michel
Re: Deleting a record [message #591220 is a reply to message #591191] Thu, 25 July 2013 11:05 Go to previous messageGo to next message
yashiindi
Messages: 8
Registered: July 2013
Junior Member
Thanks Michel.

To emphasis, the tirgger is suppose to meet the above situation. when i click the edit button in apex i want the record deleted from database so that when i update it, it wont violate the primary key constraint as it will basically just create a new record instead.

Regards
Re: Deleting a record [message #591236 is a reply to message #591220] Thu, 25 July 2013 14:57 Go to previous messageGo to next message
John Watson
Messages: 4522
Registered: January 2010
Location: Global Village
Senior Member
It would be helpful if you gave the error message. I do not think that it was "It's not working".
However, your code is obviously not correct. For instance, it is logically impossible to COMMIT n a trigger.
Re: Deleting a record [message #591244 is a reply to message #591236] Thu, 25 July 2013 16:05 Go to previous messageGo to next message
Littlefoot
Messages: 19536
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
my problem is, working from the application i want to edit the field (training description) which is a primary key, this is imposible since you cant edit a Primary key field

Two ideas.

Rewrite that page; when you are prompted to uniquely distinguish records by a) primary key or b) rowid, choose "rowid" instead of "primary key" and see what happens.

Alternatively, abandon idea of a composite primary key - alter table and add another column (say, "ID", populated from a sequence). Make combination of EMP_CODE, BUDGET_YEAR and TRAINING_DESCRIPTION unique + all these columns NOT NULL. Then rewrite the page (now it doesn't matter whether you pick "primary key" or "rowid").

Both these options make a trigger unnecessary.
Re: Deleting a record [message #591288 is a reply to message #591244] Fri, 26 July 2013 03:08 Go to previous messageGo to next message
yashiindi
Messages: 8
Registered: July 2013
Junior Member
Thank you guys for your suggestion, i will look into them all.
the attached pictures shows the error message. the field encircled in rectangle shape is the one i want to edit which is part of the primary key.
any suggestion of a probable workaround will indeed do.



Regards,

[Updated on: Fri, 26 July 2013 03:14]

Report message to a moderator

Re: Deleting a record [message #591290 is a reply to message #591288] Fri, 26 July 2013 03:15 Go to previous messageGo to next message
yashiindi
Messages: 8
Registered: July 2013
Junior Member
Guys i am struggling to upload the image. i was usinging the Browse files button but it goes blank the moment i click 'upload file'
Re: Deleting a record [message #591291 is a reply to message #591290] Fri, 26 July 2013 03:17 Go to previous messageGo to next message
John Watson
Messages: 4522
Registered: January 2010
Location: Global Village
Senior Member
For heavens sake, man, why mess about with images? Just say what the error is.
And I've already told you about ine bug in your code. Have you fixed it yet?
And do one or two elementary tests, such as what happens when you update a row using SQL*Plus?

--
And LF has already given you an alternative solution. What happened when you tried it?

[Updated on: Fri, 26 July 2013 03:19]

Report message to a moderator

Re: Deleting a record [message #591295 is a reply to message #591291] Fri, 26 July 2013 03:43 Go to previous messageGo to next message
yashiindi
Messages: 8
Registered: July 2013
Junior Member
Hi John, you dont need to be rude, my above reply read Quote:
Thank you guys for your suggestion, i will look into them all
thats means um busy trying them out.
The error is 'ORA-01403: No data Found'

Thanx.
Re: Deleting a record [message #591298 is a reply to message #591295] Fri, 26 July 2013 03:50 Go to previous messageGo to next message
yashiindi
Messages: 8
Registered: July 2013
Junior Member
This error also occur if i try to edit any other fields other then the one containg primary key.
 ORA-04091: table CDRUSER.TBM_TRAINING_BUDGET_TB is mutating, trigger/function may not see it ORA-06512: at "CDRUSER.TBM_REPLACE_RECORD", line 8 ORA-04088: error during execution of trigger 'CDRUSER.TBM_REPLACE_RECORD'
Re: Deleting a record [message #591304 is a reply to message #591298] Fri, 26 July 2013 04:17 Go to previous messageGo to next message
John Watson
Messages: 4522
Registered: January 2010
Location: Global Village
Senior Member
An ora-4091 is another bug: it is logically impossible (in many cases) for a trigger to address the table on which the trigger is defined. This follows from the rules of consistency. You can sometimes get around it, if the constraints and the nature of the statement(s) are such that Oracle can guarantee that only one row is affected.
Re: Deleting a record [message #591350 is a reply to message #591304] Fri, 26 July 2013 10:55 Go to previous messageGo to next message
yashiindi
Messages: 8
Registered: July 2013
Junior Member
Thanx John, point taken.
more suggestions welcome.
Re: Deleting a record [message #591928 is a reply to message #591350] Thu, 01 August 2013 08:53 Go to previous messageGo to next message
yashiindi
Messages: 8
Registered: July 2013
Junior Member
Thank you everyone that tried to help.

I have found a solution to the above, i dropped the trigger idea and wrote a pl/sql process that applies when the button 'saves changes' is clicked, wherelse the normal apex ARP process only fires when i want to 'delete' the record.

Matt
Re: Deleting a record [message #591942 is a reply to message #591928] Thu, 01 August 2013 10:20 Go to previous message
Littlefoot
Messages: 19536
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm glad you fixed it! Thank you for letting us know.
Previous Topic: I'm Back - brain picking time once again
Next Topic: Business small commercial app using Apex
Goto Forum:
  


Current Time: Mon Sep 01 16:43:23 CDT 2014

Total time taken to generate the page: 0.13285 seconds