Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » ORA-25006 issue
ORA-25006 issue [message #355800] Mon, 27 October 2008 11:05 Go to next message
lientkhanh
Messages: 4
Registered: October 2008
Junior Member
Hello everybody!
I'm trying to create a trigger to automatically update the import_date column of some records in my example_table whenever their clob_data are updated. To avoid mutual table problem. I used a global temporary table and a row-level trigger to keep track of the modified records. Then, a statement-level will do update on the import_date column. Following is my code:

-- Table to be updated
CREATE TABLE example_table
(id          NUMBER NOT NULL PRIMARY KEY,
 clob_data   CLOB,
 import_date TIMESTAMP);

-- Temporary table
CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  id NUMBER) 
ON COMMIT DELETE ROWS;

-- Trigger to keep track of modified records
CREATE OR REPLACE TRIGGER trig_get_modified_recs
AFTER UPDATE ON example_table
FOR EACH ROW
BEGIN
  INSERT INTO my_temp_table
  VALUES (:NEW.id);
END;
/

-- Trigger to update the import_date to SYSDATE
CREATE OR REPLACE TRIGGER trig_update_ts
AFTER UPDATE OF clob_data ON example_table
BEGIN
  UPDATE example_date
  SET import_date = SYSDATE
  WHERE id IN (SELECT id FROM my_temp_table);
END;
/



The problem is that trig_update_ts trigger get compilation error 'ORA-25006: cannot specify this column in UPDATE OF clause'. To solve this problem, someone told me that I should remove the UPDATE OF clause. However, if I do that, the UPDATE statement in this trigger will cause recursive problem, because the two triggers will fire again right after the second modifies the import_date column. Using UPDATE OF, I can prevent this. The second trigger only fires if clob_data is changed. But for CLOB colum, it is impossible. Could anybody give a clean solution to this issue?
Thank you very much,
lientkhanh




Re: ORA-25006 issue [message #355805 is a reply to message #355800] Mon, 27 October 2008 11:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-25006: cannot specify this column in UPDATE OF clause
 *Cause:  Attempt to create a trigger on update of a column whose
          datatype is disallowed in the clause, such as LOB and
          nested table.
 *Action: Remove the UPDATE OF clause.

Regards
Michel
Re: ORA-25006 issue [message #355808 is a reply to message #355805] Mon, 27 October 2008 12:41 Go to previous messageGo to next message
lientkhanh
Messages: 4
Registered: October 2008
Junior Member
Thanks Michel for your suggestion. But removing
UPDATE OF clob_data 
clause will get recursive issue because updating import_date forces the two triggers to execute again and again if we don't have a way to stop them.
How can I handle this indefinitive loop?
Re: ORA-25006 issue [message #355812 is a reply to message #355808] Mon, 27 October 2008 13:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why not:
SQL> CREATE OR REPLACE TRIGGER trig_get_modified_recs
  2  before UPDATE ON example_table
  3  FOR EACH ROW
  4  BEGIN
  5   :new.import_date := SYSDATE;
  6  END;
  7  /

Trigger created.

Instead of both triggers and temp table.

Regards
Michel
Re: ORA-25006 issue [message #355835 is a reply to message #355812] Mon, 27 October 2008 20:55 Go to previous messageGo to next message
lientkhanh
Messages: 4
Registered: October 2008
Junior Member
Well, you're right, Michel!

At first, I though that import_date is just a dummy column which does not exist in my update query so I even don't try to use
:NEW.import_date
in the trigger. However, it works well now.

Thanks again for your very simple solution Smile .

Best regards,
lientkhanh
Re: ORA-25006 issue [message #355843 is a reply to message #355835] Mon, 27 October 2008 23:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
By the way, import_date is a timestamp so SYSTIMESTAMP should be better than SYSDATE unless you don't need subsecond precision and on this case you should change column type to DATE.

Regards
Michel
Re: ORA-25006 issue [message #355844 is a reply to message #355843] Mon, 27 October 2008 23:47 Go to previous message
lientkhanh
Messages: 4
Registered: October 2008
Junior Member
Yes, I see. Actually, I only need the date and time information but subsecond precision. Change import_date to DATE is the right way to do.

Best regards,
lientkhanh
Previous Topic: Reg. DDL in Tabular form in Apex
Next Topic: Inline views
Goto Forum:
  


Current Time: Thu Mar 28 05:57:31 CDT 2024