Icon View Incident Report

Serious Serious
Reported By: Marcin
Reported On: 11/7/2008
For: Version 2.02 Build 3
# 2852 NEWROW BLOB Column Values Are Always Empty in AFTER INSERT Triggers

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

Original 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

Workaround trigger:

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



Comments Comments and Workarounds
This was also an issue with any AFTER trigger (UPDATE and DELETE also). The workaround is as-indicated - instead of using the NEWROW values directly, query them from the base table.


Resolution Resolution
Fixed Problem on 11/10/2008 in version 2.02 build 4


Products Affected Products Affected
ElevateDB Additional Software and Utilities
ElevateDB DAC Client-Server
ElevateDB DAC Client-Server with Source
ElevateDB DAC Standard
ElevateDB DAC Standard with Source
ElevateDB DAC Trial
ElevateDB LCL Standard with Source
ElevateDB VCL Client-Server
ElevateDB VCL Client-Server with Source
ElevateDB VCL Standard
ElevateDB VCL Standard with Source
ElevateDB VCL Trial

Image