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

     

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks