英文:
I want to create a dynamically trigger in java
问题
我想在Java中创建一个触发器(Firebird),并使用一个已创建的序列(SEQUENCE_NUMERO)。以下是我的代码:
try (Statement stmt = connection.createStatement())
{
stmt.executeUpdate("CREATE TRIGGER NEXTVALUE "
+ "BEFORE INSERT ON PERSONNE "
+ "FOR EACH ROW "
+ "BEGIN "
+ "NEW.NUMERO_ = SELECT NEXT VALUE FOR SEQUENCE_NUMERO FROM RDB$DATABASE;"
+ "END NEXTVALUE;");
}
但是我遇到了以下异常:
Caused by: java.sql.SQLSyntaxErrorException: Dynamic SQL Error; SQL error code = -104; Token unknown - line 2, column 1; FOR [SQLState:42000, ISC error code:335544634]
at org.firebirdsql.gds.ng.FbExceptionBuilder$Type$1.createSQLException(FbExceptionBuilder.java:527)
at org.firebirdsql.gds.ng.FbExceptionBuilder.toFlatSQLException(FbExceptionBuilder.java:302)
at org.firebirdsql.gds.ng.wire.AbstractWireOperations.readStatusVector(AbstractWireOperations.java:138)
at org.firebirdsql.gds.ng.wire.AbstractWireOperations.processOperation(AbstractWireOperations.java:202)
at org.firebirdsql.gds.ng.wire.AbstractWireOperations.readSingleResponse(AbstractWireOperations.java:169)
at org.firebirdsql.gds.ng.wire.AbstractWireOperations.readResponse(AbstractWireOperations.java:153)
at org.firebirdsql.gds.ng.wire.AbstractWireOperations.readGenericResponse(AbstractWireOperations.java:255)
at org.firebirdsql.gds.ng.wire.AbstractFbWireDatabase.readGenericResponse(AbstractFbWireDatabase.java:173)
at org.firebirdsql.gds.ng.wire.version11.V11Statement.prepare(V11Statement.java:89)
at org.firebirdsql.jdbc.FBStatement.prepareFixedStatement(FBStatement.java:1370)
at org.firebirdsql.jdbc.FBStatement.internalExecute(FBStatement.java:1356)
at org.firebirdsql.jdbc.FBStatement.executeUpdate(FBStatement.java:280)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:118)
at com.sun.proxy.$Proxy49.executeUpdate(Unknown Source)
我尝试创建一个在每次插入后都会触发的触发器。
英文:
I want to create a Trigger (firebird) in java by using a created sequence (SEQUENCE_NUMERO).
Here is my code:
try (Statement stmt = avonnection.createStatement())
{
stmt.executeUpdate("CREATE TRIGGER NEXTVALUE" + "BEFORE INSERT ON PERSONNE"
+ "\nFOR EACH ROW "
+ "\nBEGIN "
+ "\nNEW.NUMERO_= SELECT NEXT VALUE FOR SEQUENCE_NUMERO FROM RDB$DATABASE;"
+ "\nEND NEXTVALUE; "
+ "/");
}
But I have this exception:
Caused by: java.sql.SQLSyntaxErrorException: Dynamic SQL Error; SQL error code = -104; Token unknown - line 2, column 1; FOR [SQLState:42000, ISC error code:335544634]
at org.firebirdsql.gds.ng.FbExceptionBuilder$Type$1.createSQLException(FbExceptionBuilder.java:527)
at org.firebirdsql.gds.ng.FbExceptionBuilder.toFlatSQLException(FbExceptionBuilder.java:302)
at org.firebirdsql.gds.ng.wire.AbstractWireOperations.readStatusVector(AbstractWireOperations.java:138)
at org.firebirdsql.gds.ng.wire.AbstractWireOperations.processOperation(AbstractWireOperations.java:202)
at org.firebirdsql.gds.ng.wire.AbstractWireOperations.readSingleResponse(AbstractWireOperations.java:169)
at org.firebirdsql.gds.ng.wire.AbstractWireOperations.readResponse(AbstractWireOperations.java:153)
at org.firebirdsql.gds.ng.wire.AbstractWireOperations.readGenericResponse(AbstractWireOperations.java:255)
at org.firebirdsql.gds.ng.wire.AbstractFbWireDatabase.readGenericResponse(AbstractFbWireDatabase.java:173)
at org.firebirdsql.gds.ng.wire.version11.V11Statement.prepare(V11Statement.java:89)
at org.firebirdsql.jdbc.FBStatement.prepareFixedStatement(FBStatement.java:1370)
at org.firebirdsql.jdbc.FBStatement.internalExecute(FBStatement.java:1356)
at org.firebirdsql.jdbc.FBStatement.executeUpdate(FBStatement.java:280)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:118)
at com.sun.proxy.$Proxy49.executeUpdate(Unknown Source)
I try to created a trigger which will work after each insertion
答案1
得分: 1
尝试这个,它应该解决你遇到的语法问题:
CREATE TRIGGER NEXTVALUE FOR PERSONNE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
NEW.NUMERO_ = NEXT VALUE FOR SEQUENCE_NUMERO;
END
如评论中所提到的,可以在不使用SELECT查询的情况下从序列中获取下一个值,所以你可以将它从以下方式更改:
(SELECT NEXT VALUE FOR SEQUENCE_NUMERO FROM RDB$DATABASE);
英文:
Try this it should fix the syntax problems you're having
CREATE TRIGGER NEXTVALUE FOR PERSONNE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
NEW.NUMERO_ = NEXT VALUE FOR SEQUENCE_NUMERO;
END
as noted in the comments Next value from sequence can be fetched without the select query as well, so you could change it from
(SELECT NEXT VALUE FOR SEQUENCE_NUMERO FROM RDB$DATABASE);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论