| Trigger after Update? [message #205915] |
Tue, 28 November 2006 02:29  |
pw1975
Messages: 24
Registered: June 2006
|
Junior Member |
|
|
Hello Folks,
i tried to write a trigger which updates a small table (20 rows only). A new field was added to this table, which has to be filled whenever the old table is truncated and then refilled via import.
The import fills the first three columns the trigger should fill the fourth. For the fourth column, the trigger has to look in another table an receive the value to fill in from there.
So this is my code:
CREATE OR REPLACE TRIGGER T_CORELIST_ISO
BEFORE INSERT OR UPDATE
ON CORELIST
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
iso_cntry VARCHAR2(100);
BEGIN
SELECT ISO_COUNTRY INTO iso_cntry FROM ISO_LOOKUP WHERE AREAL = :NEW.AREAL;
IF iso_cntry IS NOT NULL THEN
:NEW.ISO_COUNTRY := iso_cntry;
ELSE
:NEW.ISO_COUNTRY := 'undefined';
END IF;
END T_CORELIST_ISO;
This trigger threw errors at a reimport. I am not sure whether "BEFORE UPDATE" is the right command here. Shouldn't it be AFTER the update for EACH ROW ?
Thanks a lot,
pw
|
|
|
|
| Re: Trigger after Update? [message #205917 is a reply to message #205915] |
Tue, 28 November 2006 02:33  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Hang on - I'll just get the old psychic helmet out and save you the trouble of telling us what the error was.
I'm thinking it was an ORA-77654: Invalid Quantum Flux density, which means that it's time for the hemlet to go in for more maintenance.
Sorry - you'll just have to actually tell us what the error you're getting was.
|
|
|
|
| Re: Trigger after Update? [message #205918 is a reply to message #205915] |
Tue, 28 November 2006 02:34 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
Since you didn't say what error you got, I'm gonna take a guess here.
I guess there was no corresponding row found in ISO_LOOKUP, making the select into fail with a NO DATA FOUND error.
You should handle this by wrapping it up in an anonymous block with an exception handler for the NO_DATA_FOUND.
[Edit: hm, I see my helmet seems to be working better than JRowbottom, but he IS right. You should add more info next time.]
[Updated on: Tue, 28 November 2006 02:35] Report message to a moderator |
|
|
|
|
|
| Re: Trigger after Update? [message #205920 is a reply to message #205919] |
Tue, 28 November 2006 02:40 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Ok.
Now, you can only get a No_Data_Found from either
1) A statement that says 'RAISE no_data_found;', or
2) A piece of SQL.
I leave working out exactly which statement in the trigger could be raising the error as an exercise for the reader.
You need to wrap that statement in a BEGIN...EXCEPTION.. END block, and handle the NDF in the exception handler.
How you handle it is up to you - what needs to be done when ISO_LOOKUPS doesn't contain the value you're looking for?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
I am sorry. Here you go: