java - java postgresql 使用触发器

Question

SQL = "create view CSaccept as select sID, cName from Apply where major = 'CS' and decision = 'Y' ";
stmt.executeUpdate(SQL);

SQL = "create or replace function test1() returns trigger as $$\n" +
                    "begin\n" +
                    " update Apply set cName = New.cName where (sID = Old.sID and cName = Old.cName and Apply.major = 'CS' and Apply.decision = 'Y');\n" +
                    " return Old;\n" +
                    "end;\n" +
                    "$$\n" +
                    "language 'plpgsql';\n" +
                    "create trigger CSacceptUpdate\n" +
                    "instead of update of cName on CSaccept\n" +
                    "for each row\n" +

                    "execute procedure test1();";

 stmt.executeUpdate(SQL);

嗨，我正在使用 postgresql 在 java 中编写一个程序，并且在上面的 SQL 语句中不断弹出如下错误。问题是什么？

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: INSTEAD OF triggers cannot have column lists
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:322)
    at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:308)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:284)
    at org.postgresql.jdbc.PgStatement.executeUpdate(PgStatement.java:258)
    at SqlTest2.main(SqlTest2.java:270)

SQL = "create or replace function test2() returns trigger as $$\n" +
                    "begin\n" +
                    " delete from Apply where sID = old.sID and cName = old.cName and major = 'CS' and decision = 'Y';\n" +
                    " return Old;\n" +
                    "end;\n" +
                    "$$\n" +
                    "language 'plpgsql';\n" +
                    "create trigger CSacceptDelete\n" +
                    "instead of delete on CSaccept\n" +
                    "for each row\n" +
                    "execute procedure test2();";

以上一个效果很好。我不知道为什么第一个不起作用。

标签： javapostgresql