[
https://issues.apache.org/jira/browse/DERBY-6876?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15198740#comment-15198740
]
Bryan Pendleton commented on DERBY-6876:
----------------------------------------
A few more experimental queries:
ij> select dependentid,providerid from sys.sysdepends;
DEPENDENTID |PROVIDERID
-------------------------------------------------------------------------
df1940da-0146-b029-78df-000005b12f78|921800c1-0146-b029-78df-000005b12f78
a0d440e2-0146-b029-78df-000005b12f78|560c80d5-0146-b029-78df-000005b12f78
19ba803c-0147-fe71-6b06-000005b8e328|4b6f00c8-0146-b029-78df-000005b12f78
424a8045-0148-935e-986b-000005c08798|176240b2-0146-b029-78df-000005b12f78
424a8045-0148-935e-986b-000005c08798|41588035-0148-935e-986b-000005c08798
424a8045-0148-935e-986b-000005c08798|070a00b0-0146-b029-78df-000005b12f78
424a8045-0148-935e-986b-000005c08798|af8ec0b3-0146-b029-78df-000005b12f78
424a8045-0148-935e-986b-000005c08798|176240b2-0146-b029-78df-000005b12f78
da398044-0148-935e-986b-000005c08798|424a8045-0148-935e-986b-000005c08798
da398044-0148-935e-986b-000005c08798|176240b2-0146-b029-78df-000005b12f78
da398044-0148-935e-986b-000005c08798|41588035-0148-935e-986b-000005c08798
da398044-0148-935e-986b-000005c08798|176240b2-0146-b029-78df-000005b12f78
19ba803c-014e-b216-6d98-00000650b418|176240b2-0146-b029-78df-000005b12f78
19ba803c-014e-b216-6d98-00000650b418|c99d403a-014a-73f0-35f3-000005c52bf0
19ba803c-014e-b216-6d98-00000650b418|070a00b0-0146-b029-78df-000005b12f78
19ba803c-014e-b216-6d98-00000650b418|af8ec0b3-0146-b029-78df-000005b12f78
19ba803c-014e-b216-6d98-00000650b418|176240b2-0146-b029-78df-000005b12f78
17 rows selected
ij> select * from sys.sysstatements where stmtid=
'176240b2-0146-b029-78df-000005b12f78' or
stmtid='c99d403a-014a-73f0-35f3-000005c52bf0' or
stmtid='070a00b0-0146-b029-78df-000005b12f78' or
stmtid='af8ec0b3-0146-b029-78df-000005b12f78' or stmtid=
> '176240b2-0146-b029-78df-000005b12f78';
STMTID |STMTNAME
|SCHEMAID |&|VALID|TEXT
|LASTCOMPILED |COMPILATIONSCHEMAID
|USINGTEXT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0 rows selected
ij> select * from sys.sysstatements where stmtid in (select providerid from
sys.sysdepends);
STMTID |STMTNAME
|SCHEMAID |&|VALID|TEXT
|LASTCOMPILED |COMPILATIONSCHEMAID
|USINGTEXT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
424a8045-0148-935e-986b-000005c08798|TRIGGERACTN_da398044-0148-935e-986b-000005c08798_176240b2-0146-b029-78df-000005b12f78
|80000000-00d2-b38f-4cda-000a0a412c00|T|false|UPDATE "APP"."ACTIONS" SET
last_modified = "APP"."CURRENT_TIMESTAMP_IN_UTC"()
WHERE id=CAST (org.apache.derby.iapi.db.Factory::&|2016-03-13 22:28:39.457
|80000000-00d2-b38f-4cda-000a0a412c00|NULL
> Can't create triggers on a table - error 42X94
> ----------------------------------------------
>
> Key: DERBY-6876
> URL: https://issues.apache.org/jira/browse/DERBY-6876
> Project: Derby
> Issue Type: Bug
> Environment: Linux x86_64, Java 1.8.0_74
> Reporter: Aleksei Kovura
> Attachments: trigger_bug.zip
>
>
> I previously shared this on dev mailing list -
> http://thread.gmane.org/gmane.comp.apache.db.derby.devel/115427.
> I'm reworking triggers in my database and getting this message while trying
> to create one:
> ------------
> Error: StoredPreparedStatement '19ba803c-014e-b216-6d98-00000650b418' does
> not exist.
> SQLState: 42X94
> ErrorCode: 30000
> ------------
> This database is a couple years old, started out as 10.10.1.1, was upgraded
> to stable releases as they appeared (10.11.1.1 -> 10.12.1.1). Some DDL was
> changed in between upgrades, unfortunately I can't track which ones and when.
> SYSCS_UTIL.SYSCS_INVALIDATE_STORED_STATEMENTS() doesn't help.
> I tried my best to create a replicable test case through creating database on
> earlier versions, upgrading and playing with DDL - no such luck, so I'm
> attaching compressed database (with data deleted and tables compressed). Bug
> replication procedure is as follows:
> 1) Unpack attached file (it has a "trigger_bug" root directory);
> 2) Boot the db_trigger database in embedded mode, include "trigger_bug"
> directory in the classpath - there is one Java class that is referenced in
> Stored Procedure;
> 3) Try to run this SQL:
> CREATE TRIGGER APP."test"
> AFTER UPDATE OF description ON APP."ACTIONS"
> REFERENCING NEW ROW AS updated_row
> FOR EACH ROW
> UPDATE APP."ACTIONS" SET description = 'testing'
> WHERE id=updated_row.id
> 4) Get a 42X94 error.
> I'm hoping someone with enough knowledge can poke around in system tables and
> figure out what's going on.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
