db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Thomas Hill <Thomas.K.H...@t-online.de>
Subject trigger calling a procedure
Date Thu, 20 Aug 2009 09:42:59 GMT
Hi,

I am trying to use an after insert trigger defined on a table to log values of
each row that gets inserted in a audit trail table.

The error I am getting which I do not understand / know how to resolve is a
syntax error:

ERROR 38000: The exception 'java.sql.SQLException: Syntax error: 
Encountered ";" at line 1, column 171.' was thrown while evaluating 
an expression.
ERROR 42X01: Syntax error: Encountered ";" at line 1, column 171.

Here are my trigger and procedure definitions:

CONNECT 'jdbc:derby://localhost:1527/pmsdev; create=true; user=derby';

DROP TABLE rte."Clients";

CREATE TABLE rte."Clients"
(
  "ClientID" character varying(15) NOT NULL,
  "ClientName" character varying(50),
  "RowID" integer generated always as identity,
  "CreatedBy" character varying(128), -- NOT NULL,
  "CreatedOn" timestamp, -- NOT NULL,
  "LastUpdatedBy" character varying(128), -- NOT NULL,
  "LastUpdatedOn" timestamp, -- NOT NULL,
  "DeletedBy" character varying(128),
  "DeletedOn" timestamp,
  CONSTRAINT "PK_Clients" PRIMARY KEY ("ClientID")
);
CREATE UNIQUE INDEX "UI_Clients_RowID" ON rte."Clients"("RowID");

DROP PROCEDURE "TF_ClientsAI"; 

CREATE PROCEDURE "TF_ClientsAI"()
LANGUAGE JAVA
  PARAMETER STYLE JAVA
  MODIFIES SQL DATA
  EXTERNAL NAME 'derbyPk.Functions.TF_ClientsAI'; 

-- DROP TRIGGER "TR_ClientsAI";

CREATE TRIGGER "TR_ClientsAI"
AFTER INSERT
ON rte."Clients"
REFERENCING NEW AS NEW
FOR EACH ROW 
CALL DERBY."TF_ClientsAI"();

DISCONNECT;
EXIT;

public static void TF_ClientsAI()
    throws SQLException
{
	Connection conn = getDefaultConnection();
       
    String     cSQL = "INSERT INTO rte.\"EntityAuditLog\" \n" +
                        "(\"TransactionID\", \"Statement\", \"TableName\",
\"RowID\", \"LogSequence\", \"ColumnName\") \n" +
                        "VALUES (1, 'INSERT', 'rte.\"Clients\"', 1, 0, '')";
    
    Statement   stmnt = conn.createStatement();
   	stmnt.executeUpdate(cSQL);

   	stmnt.close();
    conn.close();

}

Surprisingly I am getting the same error no matter which SQL statement I am
trying to submit!?

Thanks


Mime
View raw message