 Deleting a record [message #591186] |
Thu, 25 July 2013 07:14  |
 |
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 
|
|
|
|
|
|
|
|
| Re: Deleting a record [message #591236 is a reply to message #591220] |
Thu, 25 July 2013 14:57  |
John Watson
Messages: 8960
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 #591288 is a reply to message #591244] |
Fri, 26 July 2013 03:08 |
|
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 #591291 is a reply to message #591290] |
Fri, 26 July 2013 03:17 |
John Watson
Messages: 8960
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 #591304 is a reply to message #591298] |
Fri, 26 July 2013 04:17 |
John Watson
Messages: 8960
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.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
