[
https://issues.apache.org/jira/browse/DERBY-4874?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13810589#comment-13810589
]
ASF subversion and git services commented on DERBY-4874:
--------------------------------------------------------
Commit 1537593 from [~mamtas] in branch 'code/trunk'
[ https://svn.apache.org/r1537593 ]
DERBY-6383(Update trigger defined on one column fires on update of other
columns).
This regression is caused by 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)
The regression is for Statement level triggers. The statement trigger gets
fired incorrectly for any column update rather than just the column specified
in the UPDATE of column clause. The fix is going to ensure that SYSTRIGGERS has
the correct list of columns on which trigger should be fired. Row level
triggers work fine. The only workaround at this point is to drop and recreate
the statement triggers.
> 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
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4874
> URL: https://issues.apache.org/jira/browse/DERBY-4874
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.2.1, 10.3.3.1, 10.4.2.1, 10.5.3.0, 10.6.2.1,
> 10.7.1.1
> Reporter: Kathey Marsden
> Assignee: Mamta A. Satoor
> Fix For: 10.3.3.1, 10.4.2.1, 10.5.3.2, 10.6.2.3, 10.7.1.4,
> 10.8.2.2
>
> Attachments: DERBY4874_106_backport_diff_ver4.txt,
> DERBY4874_106_backport_stat_ver4.txt,
> DERBY4874_comments_and_test_missing_diff_ver3.txt,
> DERBY4874_comments_and_test_missing_stat_ver3.txt,
> DERBY4874_not_ready_for_commit_diff.txt,
> DERBY4874_not_ready_for_commit_diff_ver2.txt,
> DERBY4874_not_ready_for_commit_stat.txt,
> DERBY4874_not_ready_for_commit_stat_ver2.txt, derby.log, droptable.diff,
> releaseNote.html, trigAlterColumn.sql
>
>
> Trigger created before VARCHAR column is expanded with ALTER TABLE does not
> recognize new size and fails with:
> ERROR 22001: A truncation error was encountered trying to shrink VARCHAR
> '012345
> 678901234567890123456789001234567890' to length 30.
> CREATE TABLE tab (
> element_id INTEGER NOT NULL,
> altered_id VARCHAR(30) NOT NULL,
> counter SMALLINT NOT NULL DEFAULT 0,
> timets TIMESTAMP NOT NULL
> );
> 0 rows inserted/updated/deleted
> ij> -- Create a trigger against the table
> CREATE TRIGGER mytrig
> AFTER UPDATE ON tab
> REFERENCING NEW AS newt OLD AS oldt
> FOR EACH ROW MODE DB2SQL
> UPDATE tab set tab.counter = CASE WHEN (oldt.counter < 32767) THEN
> (oldt.count
> er + 1) ELSE 1 END
> WHERE ((newt.counter is null) or (oldt.counter = newt.counter))
> AND newt.element_id = tab.element_id
> AND newt.altered_id = tab.altered_id;
> 0 rows inserted/updated/deleted
> ij> -- Alter the table to increase column
> ALTER TABLE tab ALTER altered_id SET DATA TYPE VARCHAR(64);
> 0 rows inserted/updated/deleted
> ij> -- insert the data
> insert into tab values (99,
> '012345678901234567890123456789001234567890',1,CURRE
> NT_TIMESTAMP);
> 1 row inserted/updated/deleted
> ij> -- update and reproduce the issue
> update tab set timets = CURRENT_TIMESTAMP where ELEMENT_ID = 99;
> ERROR 22001: A truncation error was encountered trying to shrink VARCHAR
> '012345
> 678901234567890123456789001234567890' to length 30.
> java.sql.SQLDataException: A truncation error was encountered trying to
> shrink V
> ARCHAR '012345678901234567890123456789001234567890' to length 30.
> at
> org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLE
> xceptionFactory40.java:79)
> at
> org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:256)
> at
> org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException
> (TransactionResourceImpl.java:391)
> at
> org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Tr
> ansactionResourceImpl.java:346)
> at
> org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConne
> ction.java:2269)
> at
> org.apache.derby.impl.jdbc.ConnectionChild.handleException(Connection
> Child.java:81)
> at
> org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedState
> ment.java:1321)
> at
> org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java
> :625)
> at
> org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java
> :555)
> at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:367)
> at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:521)
> at
> org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(utilMain.java:3
> 63)
> at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:261)
> at org.apache.derby.impl.tools.ij.Main.go(Main.java:229)
> at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:184)
> at org.apache.derby.impl.tools.ij.Main.main(Main.java:75)
> at org.apache.derby.tools.ij.main(ij.java:59)
> Caused by: java.sql.SQLException: A truncation error was encountered trying
> to s
> hrink VARCHAR '012345678901234567890123456789001234567890' to length 30.
> at
> org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExc
> eptionFactory.java:45)
> at
> org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransport
> AcrossDRDA(SQLExceptionFactory40.java:119)
> at
> org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLE
> xceptionFactory40.java:70)
> ... 16 more
> Caused by: ERROR 22001: A truncation error was encountered trying to shrink
> VARC
> HAR '012345678901234567890123456789001234567890' to length 30.
> at
> org.apache.derby.iapi.error.StandardException.newException(StandardEx
> ception.java:343)
> at
> org.apache.derby.iapi.types.SQLChar.hasNonBlankChars(SQLChar.java:176
> 6)
> at org.apache.derby.iapi.types.SQLChar.setWidth(SQLChar.java:1840)
> at
> org.apache.derby.exe.ac0b5b0099x012bxf542xab11x0000001bd2983.e2(Unkno
> wn Source)
> at
> org.apache.derby.impl.services.reflect.DirectCall.invoke(ReflectGener
> atedClass.java:143)
> at
> org.apache.derby.impl.sql.execute.GenericQualifier.getOrderable(Gener
> icQualifier.java:96)
> at
> org.apache.derby.impl.sql.execute.NoPutResultSetImpl.clearOrderableCa
> che(NoPutResultSetImpl.java:313)
> at
> org.apache.derby.impl.sql.execute.TableScanResultSet.openScanControll
> er(TableScanResultSet.java:350)
> at
> org.apache.derby.impl.sql.execute.TableScanResultSet.openCore(TableSc
> anResultSet.java:262)
> at
> org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.openCore(P
> rojectRestrictResultSet.java:174)
> at
> org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.openCore(P
> rojectRestrictResultSet.java:174)
> at
> org.apache.derby.impl.sql.execute.NormalizeResultSet.openCore(Normali
> zeResultSet.java:146)
> at
> org.apache.derby.impl.sql.execute.UpdateResultSet.setup(UpdateResultS
> et.java:344)
> at
> org.apache.derby.impl.sql.execute.UpdateResultSet.open(UpdateResultSe
> t.java:263)
> at
> org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Generi
> cPreparedStatement.java:436)
> at
> org.apache.derby.impl.sql.GenericPreparedStatement.executeSubStatemen
> t(GenericPreparedStatement.java:306)
> at
> org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(G
> enericTriggerExecutor.java:173)
> at
> org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(RowT
> riggerExecutor.java:111)
> at
> org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(T
> riggerEventActivator.java:278)
> at
> org.apache.derby.impl.sql.execute.UpdateResultSet.fireAfterTriggers(U
> pdateResultSet.java:817)
> at
> org.apache.derby.impl.sql.execute.UpdateResultSet.open(UpdateResultSe
> t.java:280)
> at
> org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Generi
> cPreparedStatement.java:436)
> at
> org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPre
> paredStatement.java:317)
> at
> org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedState
> ment.java:1232)
> ... 10 more
> ij>
--
This message was sent by Atlassian JIRA
(v6.1#6144)
