Details
Description
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>
Attachments
Attachments
Issue Links
- breaks
-
DERBY-6383 Update trigger defined on one column fires on update of other columns
-
- Closed
-
- relates to
-
-
- Resolved
-