oracle错误(四) ORA-04088: 触发器 'SL.CMS_CHANNEL_TRI' 执行过程中出错的解决办法

创建一个新表后,插入第一条新数据时,报错信息如下:

2015-06-08 14:51:45,956 DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - Returning JDBC Connection to DataSourceorg.springframework.jdbc.UncategorizedSQLException: ### Error updating database.  Cause: java.sql.SQLException: ORA-08002: 序列 CMS_CHANNEL_SEQ.CURRVAL 尚未在此会话中定义ORA-06512: 在 "SL.CMS_CHANNEL_TRI", line 2ORA-04088: 触发器 'SL.CMS_CHANNEL_TRI' 执行过程中出错### The error may involve defaultParameterMap### The error occurred while setting parameters### Cause: java.sql.SQLException: ORA-08002: 序列 CMS_CHANNEL_SEQ.CURRVAL 尚未在此会话中定义ORA-06512: 在 "SL.CMS_CHANNEL_TRI", line 2ORA-04088: 触发器 'SL.CMS_CHANNEL_TRI' 执行过程中出错; uncategorized SQLException for SQL []; SQL state [72000]; error code [8002]; ORA-08002: 序列 CMS_CHANNEL_SEQ.CURRVAL 尚未在此会话中定义ORA-06512: 在 "SL.CMS_CHANNEL_TRI", line 2ORA-04088: 触发器 'SL.CMS_CHANNEL_TRI' 执行过程中出错; nested exception is java.sql.SQLException: ORA-08002: 序列 CMS_CHANNEL_SEQ.CURRVAL 尚未在此会话中定义ORA-06512: 在 "SL.CMS_CHANNEL_TRI", line 2ORA-04088: 触发器 'SL.CMS_CHANNEL_TRI' 执行过程中出错at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:71)at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:346)at com.sun.proxy.$Proxy8.insert(Unknown Source)at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:231)at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:59)at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:38)at com.sun.proxy.$Proxy36.insertCmsChannel(Unknown Source)at xyx.dsw.business.admin.cmschannel.CmsChannelInfo.onAdd(CmsChannelInfo.java:327)at xyx.dsw.business.base.BaseBusiness.add(BaseBusiness.java:67)at cn.xyx.dsw.admin.cmschannel.CmsChannelController.saveJson(CmsChannelController.java:141)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:606)at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:219)at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:745)at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:686)at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:925)at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856)at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:936)at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:838)at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:812)at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:511)at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1166)at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:388)at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:182)at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:765)at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:418)at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)at org.mortbay.jetty.Server.handle(Server.java:326)at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:542)at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:938)at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:755)at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:218)at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:404)at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:409)at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:582)Caused by: java.sql.SQLException: ORA-08002: 序列 CMS_CHANNEL_SEQ.CURRVAL 尚未在此会话中定义ORA-06512: 在 "SL.CMS_CHANNEL_TRI", line 2ORA-04088: 触发器 'SL.CMS_CHANNEL_TRI' 执行过程中出错at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:966)at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1170)at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3445)at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:2929)at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:2927)at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:94)at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:476)at sun.reflect.GeneratedMethodAccessor13.invoke(Unknown Source)at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)at java.lang.reflect.Method.invoke(Method.java:606)at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:45)at com.sun.proxy.$Proxy20.execute(Unknown Source)at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:22)at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:51)at org.apache.ibatis.executor.ReuseExecutor.doUpdate(ReuseExecutor.java:32)at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:88)at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:43)at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:121)at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:110)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:606)at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:338)... 44 more2015-06-08 14:51:45,958 ERROR [cn.xyx.dsw.admin.cmschannel.CmsChannelController] - add saveorg.springframework.jdbc.UncategorizedSQLException: ### Error updating database.  Cause: java.sql.SQLException: ORA-08002: 序列 CMS_CHANNEL_SEQ.CURRVAL 尚未在此会话中定义ORA-06512: 在 "SL.CMS_CHANNEL_TRI", line 2ORA-04088: 触发器 'SL.CMS_CHANNEL_TRI' 执行过程中出错### The error may involve defaultParameterMap### The error occurred while setting parameters### Cause: java.sql.SQLException: ORA-08002: 序列 CMS_CHANNEL_SEQ.CURRVAL 尚未在此会话中定义ORA-06512: 在 "SL.CMS_CHANNEL_TRI", line 2ORA-04088: 触发器 'SL.CMS_CHANNEL_TRI' 执行过程中出错; uncategorized SQLException for SQL []; SQL state [72000]; error code [8002]; ORA-08002: 序列 CMS_CHANNEL_SEQ.CURRVAL 尚未在此会话中定义ORA-06512: 在 "SL.CMS_CHANNEL_TRI", line 2ORA-04088: 触发器 'SL.CMS_CHANNEL_TRI' 执行过程中出错; nested exception is java.sql.SQLException: ORA-08002: 序列 CMS_CHANNEL_SEQ.CURRVAL 尚未在此会话中定义ORA-06512: 在 "SL.CMS_CHANNEL_TRI", line 2ORA-04088: 触发器 'SL.CMS_CHANNEL_TRI' 执行过程中出错at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:71)at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:346)at com.sun.proxy.$Proxy8.insert(Unknown Source)at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:231)at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:59)at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:38)at com.sun.proxy.$Proxy36.insertCmsChannel(Unknown Source)at xyx.dsw.business.admin.cmschannel.CmsChannelInfo.onAdd(CmsChannelInfo.java:327)at xyx.dsw.business.base.BaseBusiness.add(BaseBusiness.java:67)at cn.xyx.dsw.admin.cmschannel.CmsChannelController.saveJson(CmsChannelController.java:141)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:606)at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:219)at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:745)at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:686)at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:925)at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856)at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:936)at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:838)at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:812)at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:511)at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1166)at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:388)at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:182)at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:765)at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:418)at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)at org.mortbay.jetty.Server.handle(Server.java:326)at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:542)at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:938)at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:755)at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:218)at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:404)at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:409)at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:582)Caused by: java.sql.SQLException: ORA-08002: 序列 CMS_CHANNEL_SEQ.CURRVAL 尚未在此会话中定义ORA-06512: 在 "SL.CMS_CHANNEL_TRI", line 2ORA-04088: 触发器 'SL.CMS_CHANNEL_TRI' 执行过程中出错at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:966)at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1170)at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3445)at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:2929)at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:2927)at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:94)at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:476)at sun.reflect.GeneratedMethodAccessor13.invoke(Unknown Source)at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)at java.lang.reflect.Method.invoke(Method.java:606)at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:45)at com.sun.proxy.$Proxy20.execute(Unknown Source)at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:22)at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:51)at org.apache.ibatis.executor.ReuseExecutor.doUpdate(ReuseExecutor.java:32)at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:88)at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:43)at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:121)at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:110)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:606)at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:338)... 44 more

二 解决办法:

之前的创建触发器SQL脚本:

--Create triggercreate or replace trigger CMS_CHANNEL_TRI  before insert on CMS_CHANNEL  REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROWbegin    SELECT CMS_CHANNEL_SEQ.CURRVAL INTO :NEW.ID FROM DUAL;end;

优化后的SQL脚本:

--Create triggercreate or replace trigger CMS_CHANNEL_TRI  before insert on CMS_CHANNEL  REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROWbegin    SELECT CMS_CHANNEL_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;end;