Trying to create a trigger/function that will update a field with CMDBuild. Here is what i have:
CREATE OR REPLACE FUNCTION set_computer_eol()
RETURNS trigger AS
$BODY$
BEGIN
CASE "Computers"."ComputerType"
WHEN '34' THEN NEW."cEOL" = "Computers"."AssetPurchaseDate" + '3 years'::interval;
WHEN '35' THEN NEW."cEOL" = "Computers"."AssetPurchaseDate" + '3 years'::interval;
WHEN '36' THEN NEW."cEOL" = "Computers"."AssetPurchaseDate" + '5 years'::interval;
WHEN '38' THEN NEW."cEOL" = "Computers"."AssetPurchaseDate" + '5 years'::interval;
WHEN '210' THEN NEW."cEOL" = "Computers"."AssetPurchaseDate" + '5 years'::interval;
ELSE NULL;
END CASE;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION set_computer_eol()
OWNER TO postgres;
CREATE TRIGGER set_computer_eol
BEFORE INSERT OR UPDATE ON "Computers"
FOR EACH ROW
EXECUTE PROCEDURE set_computer_eol()
When the trigger is enables i get the following:
Call: services/json/management/modcard/updatecard -------------------------------------------------- Error: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [UPDATE "Computers" SET "AssetPurchaser" = ?, "Description" = ?, "ComputerType" = ?, "AssetCompany" = ?, "AssetEOL" = ?, "ComputerCPU" = ?, "AssetDeviceName" = ?, "AssetMake" = ?, "AssetPurchaseDate" = ?, "Code" = ?, "AssetVendor" = ?, "AssetSN" = ?, "Notes" = ?, "AssetNotes" = ?, "AssetUser" = ?, "AssetLocation" = ?, "AssetMAC" = ?, "ComputerRAM" = ?, "AssetModel" = ?, "AssetStatus" = ?, "AssetID" = ?, "User" = ? WHERE "Id" = ?]; nested exception is org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table "Computers" Where: PL/pgSQL function "set_computer_eol" line 3 at CASE at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:237) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:605) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:818) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:874) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:882) at org.cmdbuild.dao.driver.postgres.EntryUpdateCommand.execute(EntryUpdateCommand.java:35) at org.cmdbuild.dao.driver.postgres.PostgresDriver.update(PostgresDriver.java:170) at org.cmdbuild.dao.entry.DBEntry.updateOnly(DBEntry.java:136) at org.cmdbuild.dao.entry.DBCard.save(DBCard.java:73) at org.cmdbuild.dao.entry.DBCard.save(DBCard.java:9) at org.cmdbuild.dao.entry.ForwardingCardDefinition.save(ForwardingCardDefinition.java:48) at org.cmdbuild.services.event.ObservableDataView$ObservableExistingCardDefinition.save(ObservableDataView.java:59) at org.cmdbuild.data.store.dao.DataViewStore.update(DataViewStore.java:249) at org.cmdbuild.logic.data.access.DefaultDataAccessLogic.updateCard(DefaultDataAccessLogic.java:566) at org.cmdbuild.servlets.json.management.ModCard.updateCard(ModCard.java:338) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:622) at org.cmdbuild.servlets.JSONDispatcher.dispatch(JSONDispatcher.java:97) at org.cmdbuild.servlets.JSONDispatcher.doPost(JSONDispatcher.java:57) at javax.servlet.http.HttpServlet.service(HttpServlet.java:641) at javax.servlet.http.HttpServlet.service(HttpServlet.java:722) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at org.cmdbuild.filters.AuthFilter.doFilter(AuthFilter.java:158) at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346) at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:259) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at org.cmdbuild.filters.PatchManagerFilter.doFilter(PatchManagerFilter.java:48) at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346) at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:259) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at org.cmdbuild.filters.ConfCheckFilter.doFilter(ConfCheckFilter.java:31) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at org.cmdbuild.filters.TranslationFilter.doFilter(TranslationFilter.java:52) at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346) at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:259) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:224) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:927) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407) at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:987) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:579) at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:309) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1146) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:701) Caused by: org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table "Computers" Where: PL/pgSQL function "set_computer_eol" line 3 at CASE at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:334) at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:824) at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:818) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:589)
... 55 more An direction would we appreciated.
