Home » SQL & PL/SQL » SQL & PL/SQL » Invalid record error with triggers (Oracle 10g)
Invalid record error with triggers [message #423564] Fri, 25 September 2009 05:56 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
When I make a change to a record in the item master it will write a record, but if I try to make another change it will not write a record to the table of the change and will not allow the change to the Item Master (roll back). Can you tell me what is wrong with my code, I'm using a date/time stamp key field with the other key fields of short item, update date, update time.

What I don't understand is when I make the first change it writes to the custom table fine....but when I try to make another change on the same item I get an error on the application that says "Invalid Record".....

Triger code
CREATE OR REPLACE TRIGGER TESTDTA.INSERT_F4101
BEFORE
INSERT ON TESTDTA.F4101 FOR EACH ROW
Begin
Insert into TESTDTA.F5841099( IL$DTS,
ILITM,ILLITM,ILAITM,ILDSC1,ILDSC2,ILSRTX,ILALN,ILTDAY)
Values(TO_CHAR(SYSDATE,'mm/dd/yyyy HH24:MI:SS'),
:new.IMITM,:new.IMLITM,:new.IMAITM,:new.IMDSC1,:new.IMDSC2,:new.IMSRTX,:new.IMALN,:new.IMTDAY);
End;
/


create or REPLACE TRIGGER TESTDTA.UPDATE_F4101
BEFORE
Update or delete ON TESTDTA.F4101 FOR EACH ROW
Begin
Insert into TESTDTA.F5841099( IL$DTS,ILITM,ILLITM,ILAITM,ILDSC1,ILDSC2,ILSRTX,ILALN,ILTDAY)
Values(TO_CHAR(SYSDATE,'mm/dd/yyyy HH24:MI:SS'),
:new.IMITM,:new.IMLITM,:new.IMAITM,:new.IMDSC1,:new.IMDSC2,:new.IMSRTX,:new.IMALN,:new.IMTDAY);
End;
/ 



Please suggest me what is going wrong.

Regards,
Vir
Re: Invalid record error with triggers [message #423565 is a reply to message #423564] Fri, 25 September 2009 06:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With what you posted we:
1/ can't reproduce what you see
2/ can't see what you see

With 175 posts, you know what you have to do...

Regards
Michel
Re: Invalid record error with triggers [message #423602 is a reply to message #423564] Fri, 25 September 2009 08:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What would the :new value for a column be in case of a delete?
(Not claiming that this is the solution for your problem, just detecting another problem in your code)
Re: Invalid record error with triggers [message #423651 is a reply to message #423564] Sat, 26 September 2009 00:16 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
yes your problem description is lacking. Please provide a test case and more detailed information.

I would suggest also that the problem has little to do with Oracle directly (well maybe one of those pitiful oracle forms errors, is your app oracle forms? If so why didn't you tell us that?).

Quote:
"Invalid Record".....

This is no database error message "Invalid Record" that I recall ever seeing.

If this is the error you are getting, it is likely your application giving it to you. I might guess your app is doing something with locking (optimistic locking?) and has (incorrectly?) decided that the record is no longer in synch with the database and is thus rejecting your update. This would be a bug in the app, or your locking processes. This is just a guess though. I would also say that if this is the actual error message, then you should fire whoever wrote the app beacuse their error handling stinks as is evidenced by the total lack of information given by the error. How are you or we or anyone else supposed to figure out what went wrong with a crappy error message like that?

If on the other hand, ther is not the actual error (why did you truncate it?) then I care no longer to assist you for if you cannot take the time to post the actual error, then I can't take the time not to read what you did not post.

Sorry if I sound a little miffed, please post the full error message including error number, text, and any data presented by the error. With this we can acertain if it is an oracle error from some oracle component or likely the result of something else.

Let me ask you though, did you really think "Invalid Error"... was enough information?

On another note, is it possible to have more than one picture to display in our posts? I need one to show when I am unhappy with a post and the smiling one I got here just does my annoyance not emote.

Good luck, Kevin

[Updated on: Sat, 26 September 2009 00:22]

Report message to a moderator

Previous Topic: update statement with :new.column throwing error
Next Topic: Impact on table when it is renamed
Goto Forum:
  


Current Time: Wed Dec 07 03:20:53 CST 2016

Total time taken to generate the page: 0.08455 seconds