我想在Java中创建一个动态触发器

huangapple go评论85阅读模式
英文:

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);

huangapple
  • 本文由 发表于 2023年6月19日 22:21:35
  • 转载请务必保留本文链接:https://go.coder-hub.com/76507526.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定