Home » SQL & PL/SQL » SQL & PL/SQL » Trigger after Update?
Trigger after Update? [message #205915] Tue, 28 November 2006 02:29 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 #205919 is a reply to message #205917] Tue, 28 November 2006 02:35 Go to previous messageGo to next message
pw1975
Messages: 24
Registered: June 2006
Junior Member
Wink I am sorry. Here you go:


ORA-01403: no data found
ORA-06512: in "CORELIST", line 4
ORA-04088: Error while executing Trigger...



Re: Trigger after Update? [message #205920 is a reply to message #205919] Tue, 28 November 2006 02:40 Go to previous message
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?
Previous Topic: Desing issue - Please tell me if i am right
Next Topic: index by table to xml
Goto Forum:
  


Current Time: Sat Dec 03 10:15:06 CST 2016

Total time taken to generate the page: 0.17410 seconds