Learn how easy it is to sync an existing GitHub or Google Code repo to a SourceForge project! See Demo

Close

Created TRIGGER does not work anymore

Help
MichaeL
2013-01-16
2014-01-19
  • MichaeL
    MichaeL
    2013-01-16

    Hi,

    I have a problem with a TRIGGER on a VIEW-TABLE. This TRIGGER works fine with HSQLDB 2.2.7 but it does not work in 2.2.8 and 2.2.9. Is there any changes related to the TRIGGER-Feature in HSQLDB since version 2.2.7?

    My defined Trigger looks like:

    CREATE TRIGGER 
        "TRG_MY_VIEW_TABLE" 
    INSTEAD OF UPDATE ON 
        "MY_VIEW_TABLE" 
    REFERENCING 
        OLD ROW AS "oldrow" 
        NEW ROW AS "newrow" 
    FOR EACH ROW BEGIN ATOMIC
        UPDATE 
            "DATA_TABLE" 
        SET 
            "DATA_TABLE"."enable" = "newrow"."enable" 
        WHERE
            "newrow"."p_id" = "DATA_TABLE"."p_id";
    END
    

    If I create an UPDATE like:

    UPDATE "MY_VIEW_TABLE" SET "enable" = TRUE
    

    I get a NullPointerException:

    D:\hsqldb-2.2.9\hsqldb\bin>cd ..\data
    java.sql.SQLException: java.lang.NullPointerException java.lang.NullPointerExcep
    tion
            at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
            at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
            at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
            at org.hsqldb.jdbc.JDBCStatement.execute(Unknown Source)
            at org.hsqldb.util.DatabaseManagerSwing.executeSQL(Unknown Source)
            at org.hsqldb.util.DatabaseManagerSwing.access$1000(Unknown Source)
            at org.hsqldb.util.DatabaseManagerSwing$StatementExecRunnable.run(Unknow
    n Source)
            at java.awt.event.InvocationEvent.dispatch(Unknown Source)
            at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
            at java.awt.EventQueue.access$200(Unknown Source)
            at java.awt.EventQueue$3.run(Unknown Source)
            at java.awt.EventQueue$3.run(Unknown Source)
            at java.security.AccessController.doPrivileged(Native Method)
            at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Sour
    ce)
            at java.awt.EventQueue.dispatchEvent(Unknown Source)
            at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
            at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
            at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
            at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
            at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
            at java.awt.EventDispatchThread.run(Unknown Source)
    Caused by: org.hsqldb.HsqlException: java.lang.NullPointerException
            at org.hsqldb.error.Error.error(Unknown Source)
            at org.hsqldb.result.Result.newErrorResult(Unknown Source)
            at org.hsqldb.result.Result.newErrorResult(Unknown Source)
            at org.hsqldb.Session.executeDirectStatement(Unknown Source)
            at org.hsqldb.Session.execute(Unknown Source)
            ... 19 more
    Caused by: java.lang.NullPointerException
            at org.hsqldb.lib.ArrayUtil.projectRow(Unknown Source)
            at org.hsqldb.ParserDML.compileUpdateStatement(Unknown Source)
            at org.hsqldb.ParserCommand.compilePart(Unknown Source)
            at org.hsqldb.ParserCommand.compileStatements(Unknown Source)
            ... 21 more
    

    Is there maybe a new guideline to use a TRIGGER (on a VIEW-TABLE)?

    Kind regards
    Micha

     
  • Fred Toussi
    Fred Toussi
    2013-01-16

    This may be a regression that has been fixed. Please try with the latest snapshot jar and report. If there is still a problem, please report the CREATE TABLE and CREATE VIEW statements.

    http://www.hsqldb.org/repos/org/hsqldb/hsqldb/SNAPSHOT/

     
  • MichaeL
    MichaeL
    2013-01-16

    Hi Fredt,

    thank you for your quick reply. I tried hsqldb-20130109.113130-29.jar (and hsqldb-20130104.173549-28.jar) but the problem still occurs. I uploaded a small example DB view_with_trigger.zip. I get the NullPointerException by using

    UPDATE "TestViewTableJOIN" SET "aktiv" = TRUE
    

    Hope this is helpful.

    regards
    Micha

     
  • Fred Toussi
    Fred Toussi
    2013-01-16

    Please say correctly with which version your UPDATE works. There is no difference between 2.2.7 and 2.2.8 in this area.

     
  • MichaeL
    MichaeL
    2013-01-17

    Hi Fredt,

    I'm sorry, it works also in Version 2.2.8 but not in 2.2.9 or 2.3.0.

    kind regards
    Micha

     
  • Fred Toussi
    Fred Toussi
    2013-01-17

    Thanks. View and subquery optimisation and processing was improved a lot in 2.2.9. This caused some regressions which have been fixed.

    Your updatable view uses an ORDER BY with LIMIT for which there was no trigger update test. Now that we have one, it will be fixed.

     
  • MichaeL
    MichaeL
    2013-01-17

    Hello Fredt,

    thanks again for your reply and your explanation! I will wait for Version 2.2.3!

    all the best
    Micha

     
  • MichaeL
    MichaeL
    2013-01-24

    Hello Fredt,

    short Notice: I saw the new snapshot (hsqldb-20130123.192235-31.jar) on your website. A short test with this version provides reliable results.

    regards
    Micha