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

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

I want to create a dynamically trigger in java

问题

我想在Java中创建一个触发器（Firebird），并使用一个已创建的序列（SEQUENCE_NUMERO）。以下是我的代码：

  1. try (Statement stmt = connection.createStatement())
  2. {
  3.     stmt.executeUpdate("CREATE TRIGGER NEXTVALUE "
  4.                     + "BEFORE INSERT ON PERSONNE "
  5.                     + "FOR EACH ROW "
  6.                     + "BEGIN "
  7.                     + "NEW.NUMERO_ = SELECT NEXT VALUE FOR SEQUENCE_NUMERO FROM RDB$DATABASE;"
  8.                     + "END NEXTVALUE;");
  9. }

但是我遇到了以下异常：

  1. 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]
  2.     at org.firebirdsql.gds.ng.FbExceptionBuilder$Type$1.createSQLException(FbExceptionBuilder.java:527)
  3.     at org.firebirdsql.gds.ng.FbExceptionBuilder.toFlatSQLException(FbExceptionBuilder.java:302)
  4.     at org.firebirdsql.gds.ng.wire.AbstractWireOperations.readStatusVector(AbstractWireOperations.java:138)
  5.     at org.firebirdsql.gds.ng.wire.AbstractWireOperations.processOperation(AbstractWireOperations.java:202)
  6.     at org.firebirdsql.gds.ng.wire.AbstractWireOperations.readSingleResponse(AbstractWireOperations.java:169)
  7.     at org.firebirdsql.gds.ng.wire.AbstractWireOperations.readResponse(AbstractWireOperations.java:153)
  8.     at org.firebirdsql.gds.ng.wire.AbstractWireOperations.readGenericResponse(AbstractWireOperations.java:255)
  9.     at org.firebirdsql.gds.ng.wire.AbstractFbWireDatabase.readGenericResponse(AbstractFbWireDatabase.java:173)
  10.     at org.firebirdsql.gds.ng.wire.version11.V11Statement.prepare(V11Statement.java:89)
  11.     at org.firebirdsql.jdbc.FBStatement.prepareFixedStatement(FBStatement.java:1370)
  12.     at org.firebirdsql.jdbc.FBStatement.internalExecute(FBStatement.java:1356)
  13.     at org.firebirdsql.jdbc.FBStatement.executeUpdate(FBStatement.java:280)
  14.     at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  15.     at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
  16.     at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  17.     at java.base/java.lang.reflect.Method.invoke(Method.java:566)
  18.     at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:118)
  19.     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:

  1. try (Statement stmt = avonnection.createStatement())
  2. {
  3. stmt.executeUpdate("CREATE  TRIGGER NEXTVALUE" + "BEFORE INSERT ON PERSONNE"
  4. + "\nFOR EACH ROW "
  5. + "\nBEGIN "
  6. + "\nNEW.NUMERO_= SELECT NEXT VALUE FOR SEQUENCE_NUMERO FROM RDB$DATABASE;"
  7. + "\nEND NEXTVALUE; "
  8. + "/");
  9. }

But I have this exception:

  1. 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]
  2. at org.firebirdsql.gds.ng.FbExceptionBuilder$Type$1.createSQLException(FbExceptionBuilder.java:527)
  3. at org.firebirdsql.gds.ng.FbExceptionBuilder.toFlatSQLException(FbExceptionBuilder.java:302)
  4. at org.firebirdsql.gds.ng.wire.AbstractWireOperations.readStatusVector(AbstractWireOperations.java:138)
  5. at org.firebirdsql.gds.ng.wire.AbstractWireOperations.processOperation(AbstractWireOperations.java:202)
  6. at org.firebirdsql.gds.ng.wire.AbstractWireOperations.readSingleResponse(AbstractWireOperations.java:169)
  7. at org.firebirdsql.gds.ng.wire.AbstractWireOperations.readResponse(AbstractWireOperations.java:153)
  8. at org.firebirdsql.gds.ng.wire.AbstractWireOperations.readGenericResponse(AbstractWireOperations.java:255)
  9. at org.firebirdsql.gds.ng.wire.AbstractFbWireDatabase.readGenericResponse(AbstractFbWireDatabase.java:173)
  10. at org.firebirdsql.gds.ng.wire.version11.V11Statement.prepare(V11Statement.java:89)
  11. at org.firebirdsql.jdbc.FBStatement.prepareFixedStatement(FBStatement.java:1370)
  12. at org.firebirdsql.jdbc.FBStatement.internalExecute(FBStatement.java:1356)
  13. at org.firebirdsql.jdbc.FBStatement.executeUpdate(FBStatement.java:280)
  14. at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  15. at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
  16. at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  17. at java.base/java.lang.reflect.Method.invoke(Method.java:566)
  18. at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:118)
  19. at com.sun.proxy.$Proxy49.executeUpdate(Unknown Source)

I try to created a trigger which will work after each insertion

答案1

得分: 1

尝试这个，它应该解决你遇到的语法问题：

  1. CREATE TRIGGER NEXTVALUE FOR PERSONNE
  2. ACTIVE BEFORE INSERT POSITION 0
  3. AS
  4. BEGIN
  5. NEW.NUMERO_ = NEXT VALUE FOR SEQUENCE_NUMERO;
  6. END

如评论中所提到的，可以在不使用SELECT查询的情况下从序列中获取下一个值，所以你可以将它从以下方式更改：

  1. (SELECT NEXT VALUE FOR SEQUENCE_NUMERO FROM RDB$DATABASE);

参考链接

英文:

Try this it should fix the syntax problems you're having

  1. CREATE TRIGGER NEXTVALUE FOR PERSONNE
  2. ACTIVE BEFORE INSERT POSITION 0
  3. AS
  4. BEGIN
  5. NEW.NUMERO_ = NEXT VALUE FOR SEQUENCE_NUMERO;
  6. 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

  1. (SELECT NEXT VALUE FOR SEQUENCE_NUMERO FROM RDB$DATABASE);

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

发表评论

匿名网友

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

确定