[DERBY-6726] NPE from trigger

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.10.1.1
• Fix Version/s: 10.12.1.2, 10.13.1.0
• Component/s: SQL
• Assignee: Bryan Pendleton
• Reporter: Tim Dudgeon
• Created: 03/Sep/14 15:40
• Updated: 16/Sep/17 19:50
• Resolved: 02/Oct/16 17:05

Description

Saw this strange exception when doing an insert to a table with a trigger

Tue Sep 02 13:39:09 BST 2014 Thread[SQLExecution,1,system] (XID = 62693), (SESSIONID = 1), (DATABASE = C:/Users/timbo/Documents/IJCProjects/mini-regs/Vanilla Oracle/.config/derby-minireg-01-sep/db), (DRDAID = null), Failed Statement is: UPDATE samples SET sample_code = 'S123456' WHERE sample_id = CAST (org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getNewRow().getObject(1) AS INTEGER)
java.lang.NullPointerException
    at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getTriggerActionString(Unknown Source)
    at org.apache.derby.iapi.sql.dictionary.TriggerDescriptor.getActionSPS(Unknown Source)
    at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.getAction(Unknown Source)
    at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown Source)
    at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)
    at org.apache.derby.impl.sql.execute.UpdateResultSet.fireAfterTriggers(Unknown Source)
    at org.apache.derby.impl.sql.execute.UpdateResultSet.open(Unknown Source)
    at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown Source)
    at org.apache.derby.impl.sql.GenericPreparedStatement.executeSubStatement(Unknown Source)
    at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown Source)
    at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown Source)
    at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)
    at org.apache.derby.impl.sql.execute.InsertResultSet.normalInsertCore(Unknown Source)
    at org.apache.derby.impl.sql.execute.InsertResultSet.open(Unknown Source)
    at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown Source)
    at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)

The trigger definition is this:

CREATE TRIGGER samples_code_trg
AFTER INSERT ON samples
REFERENCING NEW AS newrow FOR EACH ROW MODE DB2SQL
UPDATE samples SET sample_code = 'S123456'
WHERE samples.sample_id = newrow.sample_id;

As mentioned here: http://mail-archives.apache.org/mod_mbox/db-derby-user/201408.mbox/%3Cltq5hl$kps$[e-mail]%3E
it could be that its caused by another AFTER UPDATE trigger that's on the table.

Unfortunately I rebuilt all the tables and triggers and not the problem doesn't happen, so I can't provide a test case.

Issue Links

• is related to: DERBY-4874 Trigger does not recognize new size of VARCHAR column expanded with ALTER TABLE. It fails with ERROR 22001: A truncation error was encountered trying to shrink VARCHAR
• is related to: DERBY-6783 WHEN clause in CREATE TRIGGER for UPDATE is not working for the sql script below