Trigger in Oracle

[uparna]

Hi All,
I want to create a trigger in oracle , but apparently i am wrong -> i am getting the Ora-04091 error.
Basically , i have a table "A" which has 5 columns. I am now adding a 6th column to this table. The 6th column is supposed to have information derived from the first 3 columns ( eg: if column1 = YES and Column2 = NO and column3=YES then column6 should be "CATEGORY 1". Now , inorder to derive this information , i will have to create a trigger on table"A", but inside the trigger , i cannot read from the same table "A". The app people cannot change their code at this point and i cannot see anyother way than a trigger to handle this situation automatically. Any idea what i have to do?



Regards,
S. Jayaram Uparna .

 

[mechadios]

To overcome this problem you will have to use packagae. Store the value of these new values in the package variable and then use it to determine the value in the sixth column. Try this.

 

[saketbansal]

one more point.

On a statement level trigger u can select the current table. So populate ur variable(package) in row level tigger and use it in statement level trigger.

HTH...
Saket

 

[uparna]

Hi ,
Thanks for the info....but am not very clear on the packages stuff...would you please shed some light on it so that i may have better insight into the same?




Regards,
S. Jayaram Uparna .

 

[hayo]

I do not understand your exact problem.
Do you want to populate column6 for all already existing rows, or do you just want to maintain column6, when new rows are inserted, or if updates are done ?

In both cases, you should be able to do that with a row trigger. You may code a trigger as follows:

CREATE OR REPLACE TRIGGER T_table
BEFORE UPDATE OR INSERT
    ON table
    FOR EACH ROW
BEGIN

if (updating('column1') or updating('column2') or updating('column3') or inserting) then
	   if (:new.column1='YES' and :new.column2='NO' and :new.column3='YES') then
  	   	  :new.column6 := 'category 1';
	   else ......;	  
	   end if;
	end if

EXCEPTION
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR( -20900, SQLERRM );
END;

This simple trigger will maintain column6.

If want to populate column6 for all already existing rows, then simply submit a "dummy" UPDATE like this:

UPDATE table
set column1=column1;

Hope this helps


Thanks
Hayo