Are CLOB type fields not supported in after insert trigger?
When I try to access VARCHAR fields everything is OK but CLOB are empty (both values are
inserted properly into table).

Marcin,

<< Are CLOB type fields not supported in after insert trigger? >>

Yes.

<< When I try to access VARCHAR fields everything is OK but CLOB are empty
(both values are inserted properly into table). >>

Could you post the trigger code that you're using ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,
Here is my trigger:

TRIGGER "trg_CreateManufacturerAlias"
BEGIN
  DECLARE Alias VARCHAR(128);
  IF (COALESCE(NEWROW.Name, '') <> '') THEN
     SET Alias = ufn_GetManufacturerAlias(NEWROW.Name);
     IF (COALESCE(Alias, '') = '') THEN
        SET Alias = NEWROW.Name;
     END IF;
     CALL usp_AddManufacturerAlias(NEWROW.ID, Alias);
  END IF;
END

The table is very simple:

CREATE TABLE Manufacturer(
              ID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1),
              Name CLOB,
           PRIMARY KEY (ID)
           )

I have to use CLOB column type because Name can be even more that 5000 characters.

For now I fixed it like this:

TRIGGER "trg_CreateManufacturerAlias"
BEGIN
  DECLARE Alias VARCHAR(128);
  DECLARE ValName CLOB;
  DECLARE Cur CURSOR FOR Stmt;

  PREPARE Stmt FROM 'SELECT Name FROM Manufacturer WHERE ID = ?';
  OPEN Cur USING NEWROW.ID;
  IF (ROWCOUNT(Cur) > 0) THEN
     FETCH FIRST FROM Cur(Name) INTO ValName;

  IF (COALESCE(ValName, '') <> '') THEN
     SET Alias = ufn_GetManufacturerAlias(ValName);
     IF (COALESCE(Alias, '') = '') THEN
        SET Alias = ValName;
     END IF;
     CALL usp_AddManufacturerAlias(NEWROW.ID, Alias);
  END IF;                    

  END IF;
  CLOSE Cur;
END

My fix for sure is less efficient.

Marcin

Marcin,

<< Here is my trigger: >>

Yep, it's a bug.  The BLOB values are getting reset before the after insert
trigger is evaluated.

A fix will be in 2.02 B4.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>> Yep, it's a bug.  The BLOB values are getting reset before the after insert
>> trigger is evaluated.

Thank you for confirmation. Will wait for the build.