审计字段即记录数据的创建人、创建时间、修改人、修改时间的字段、体现在每一张数据库表中。为了减少代码量需要设置一套通用的方法。
思路:登录用户存入session,访问数据库的时候拦截器获取链接先设入数据库session,随后进行业务逻辑,每个表设置trigger,每次更新时获取session中的用户名称,设置入审计字段。
1、用户信息Web到App的传递。
思路:拦截分发器,每次分发请求的时候在ServiceRequest中设置用户信息传递到App。
首先设置页面拦截,获取session中的用户信息放入用户线程上下文。
web-context.xml
<bean id="sessionInterceptor" class="com.palic.egis.common.web.util.SessionInterceptor">
<description>AuthorizationController用来检查用户是否login</description>
<property name="worktableAssociatedFilter">
<list>
<value>/index.screen</value>
</list>
</property>
</bean>
<bean id="defaultHandlerMapping" class="com.paic.pafa.app.web.servlet.handler.BeanNameUrlHandlerMapping">
<description>
当一个HTTP请求进来的时候,interceptors先拦截请求,进行预先处理。
</description>
<property name="interceptors">
<list>
<ref local="sessionInterceptor"/>
</list>
</property>
</bean>
common-context.xml
<!--=====================================================================-->
<!-- 线程context的配置 -->
<!--=====================================================================-->
<bean id="userThreadContext"
class="com.paic.pafa.core.service.PafaThreadContext">
<description>线程的Context</description>
</bean>
拦截器实现
/**
* 用于检查Session里是有用户登录信息的拦截器。Interceptor的功能类似于Servlet的Filter。
* 当一个HTTP请求进来的时候,interceptors先拦截请求,进行预先处理。
*
* @author Leo Liao, 2005-4-14, created
* @version $Revision$Date$
* @see com.palic.egis.support.privilege.web.controller.LoginController
*/
public class SessionInterceptor extends HandlerInterceptorAdapter {
// 用于获取用户信息的LoginController
private Controller authorizationController;
// 目前SessionAdmin有待完善,请暂时不要使用
// private SessionAdmin sessionAdmin;
/**
* 检查session里是不是有用户信息
*
* @see com.palic.egis.support.privilege.web.controller.LoginController
*/
private boolean checkSession(HttpServletRequest request) {
HttpSession session = request.getSession();
if (session != null && session.getAttribute("currentUser") != null) {
return true;
}
return false;
}
public final boolean preHandle(HttpServletRequest request,
HttpServletResponse response, Object handler)
throws PafaWebException {
try {
if (!checkSession(request)) {
authorizationController.handleRequest(request, response);
if (!checkSession(request)) {
throw new PafaWebException("appdemo.error.session.invalid");
}
}
// begin 设置当前用户到线程上下文
HttpSession session = request.getSession();
String uid = (String) session.getAttribute("currentUser");
PafaThreadContext tc = (PafaThreadContext) PafaAppCommonContexton
.getInstance()
.getBean(SystemObjectName.USER_THREAD_CONTEXT);
tc.putTxnID(PafaCoreContexton.getInstance().getIDGenerator()
.getID());
tc.putUserID(uid);
// end
} catch (PafaWebException ex) {
// throw new PafaWebException("appdemo.error.session.invalid", ex);
request.getSession().setAttribute("loginError",
ex.getInitialCause().getMessage());
} catch (Exception ex) {
// throw new PafaWebException("appdemo.error.session.invalid", ex);
request.getSession().setAttribute("loginError", ex.getMessage());
}
return true;
}
/**
* 清除threadContext中设置的用户信息
*/
public final void afterCompletion(HttpServletRequest httpservletrequest,
HttpServletResponse httpservletresponse, Object obj,
Exception exception) throws Exception {
PafaThreadContext tc = (PafaThreadContext) PafaAppCommonContexton
.getInstance().getBean(SystemObjectName.USER_THREAD_CONTEXT);
tc.clear();
super.afterCompletion(httpservletrequest, httpservletresponse, obj,
exception);
}
}
/**
* 用于检查Session里是有用户登录信息的拦截器。Interceptor的功能类似于Servlet的Filter。
* 当一个HTTP请求进来的时候,interceptors先拦截请求,进行预先处理。
*
* @author Leo Liao, 2005-4-14, created
* @version $Revision$Date$
* @see com.palic.egis.support.privilege.web.controller.LoginController
*/
public class SessionInterceptor extends HandlerInterceptorAdapter {
// 用于获取用户信息的LoginController
private Controller authorizationController;
// 目前SessionAdmin有待完善,请暂时不要使用
// private SessionAdmin sessionAdmin;
/**
* 检查session里是不是有用户信息
*
* @see com.palic.egis.support.privilege.web.controller.LoginController
*/
private boolean checkSession(HttpServletRequest request) {
HttpSession session = request.getSession();
if (session != null && session.getAttribute("currentUser") != null) {
return true;
}
return false;
}
public final boolean preHandle(HttpServletRequest request,
HttpServletResponse response, Object handler)
throws PafaWebException {
try {
if (!checkSession(request)) {
authorizationController.handleRequest(request, response);
if (!checkSession(request)) {
throw new PafaWebException("appdemo.error.session.invalid");
}
}
// begin 设置当前用户到线程上下文
HttpSession session = request.getSession();
String uid = (String) session.getAttribute("currentUser");
PafaThreadContext tc = (PafaThreadContext) PafaAppCommonContexton
.getInstance()
.getBean(SystemObjectName.USER_THREAD_CONTEXT);
tc.putTxnID(PafaCoreContexton.getInstance().getIDGenerator()
.getID());
tc.putUserID(uid);
// end
} catch (PafaWebException ex) {
// throw new PafaWebException("appdemo.error.session.invalid", ex);
request.getSession().setAttribute("loginError",
ex.getInitialCause().getMessage());
} catch (Exception ex) {
// throw new PafaWebException("appdemo.error.session.invalid", ex);
request.getSession().setAttribute("loginError", ex.getMessage());
}
return true;
}
/**
* 清除threadContext中设置的用户信息
*/
public final void afterCompletion(HttpServletRequest httpservletrequest,
HttpServletResponse httpservletresponse, Object obj,
Exception exception) throws Exception {
PafaThreadContext tc = (PafaThreadContext) PafaAppCommonContexton
.getInstance().getBean(SystemObjectName.USER_THREAD_CONTEXT);
tc.clear();
super.afterCompletion(httpservletrequest, httpservletresponse, obj,
exception);
}
}
common-context.xml 分发器配置
<!-- Real pafaAC -->
<bean id="pafaACTarget"
class="com.paic.pafa.app.lwc.service.remoting.access.ejb.SmartRemoteStatelessSessionProxyFactoryBean">
<property name="jndiName">
<value>ejb/egis/PafaAC</value>
</property>
<property name="businessInterface">
<value>
com.paic.pafa.app.biz.ac.ApplicationController
</value>
</property>
<property name="jndiTemplate">
<ref local="pafaACJndi" />
</property>
</bean>
<!-- pafaAC Proxy -->
<bean id="pafaAC"
class="com.paic.pafa.app.lwc.core.aop.framework.ProxyFactoryBean">
<property name="target">
<ref local="pafaACTarget"/>
</property>
<property name="proxyInterfaces">
<value>com.paic.pafa.app.biz.ac.ApplicationController</value>
</property>
<property name="interceptorNames">
<list>
<value>dispatchServiceAdvisor</value>
</list>
</property>
</bean>
<bean id="dispatchServiceAdvisor"
class="com.paic.pafa.app.lwc.core.aop.support.NameMatchMethodPointcutAdvisor">
<property name="mappedName">
<value>handleRequest</value>
</property>
<property name="advice">
<ref bean="dispatchServiceInterceptor"/>
</property>
</bean>
<bean id="dispatchServiceInterceptor"
class="com.palic.egis.common.web.util.ThreadContextInterceptor">
<property name="threadContext">
<ref bean="userThreadContext"/>
</property>
</bean>
ThreadContextInterceptor.java
public class ThreadContextInterceptor implements MethodInterceptor {
private PafaThreadContext threadContext = null;
public PafaThreadContext getThreadContext() {
return threadContext;
}
public void setThreadContext(PafaThreadContext threadContext) {
this.threadContext = threadContext;
}
public Object invoke(MethodInvocation method) throws Throwable {
Object[] args = method.getArguments();
ServiceRequest sr = (ServiceRequest) args[0];
if (sr != null) {
SessionDTO dto = sr.getSessionDTO();
if(dto.getUserId() == null){
dto.setUserId(threadContext.getUserID());
}
if (dto.getTxnId() == null) {
dto.setTxnId(threadContext.getTxnID());
}
}
return method.proceed();
}
public class ThreadContextInterceptor implements MethodInterceptor {
private PafaThreadContext threadContext = null;
public PafaThreadContext getThreadContext() {
return threadContext;
}
public void setThreadContext(PafaThreadContext threadContext) {
this.threadContext = threadContext;
}
public Object invoke(MethodInvocation method) throws Throwable {
Object[] args = method.getArguments();
ServiceRequest sr = (ServiceRequest) args[0];
if (sr != null) {
SessionDTO dto = sr.getSessionDTO();
if(dto.getUserId() == null){
dto.setUserId(threadContext.getUserID());
}
if (dto.getTxnId() == null) {
dto.setTxnId(threadContext.getTxnID());
}
}
return method.proceed();
}
2、APP层传入数据库
思路:拦截数据源,先设置数据库session在执行业务逻辑
biz-context.xml
<bean id="dsFactory"
class="com.paic.pafa.app.lwc.service.persistence.datasource.DataSourceFactoryBean" >
<property name="defaultDSKey">
<description>缺省的数据源,必须指定为下面map中的entry key之一</description>
<value>GBSDS</value>
</property>
<property name="dataSources">
<description>可以在map属性里面添加多个数据源</description>
<map>
<entry key="GBSDS">
<ref local="defaultDS"/>
</entry>
<entry key="GBSDS_XA">
<ref local="gbsDS_XA"/>
</entry>
</map>
</property>
</bean>
<bean id="defaultDS"
class="com.paic.pafa.app.lwc.core.aop.framework.ProxyFactoryBean">
<property name="target">
<ref local="defaultDSTarget" />
</property>
<property name="proxyInterfaces">
<value>javax.sql.DataSource</value>
</property>
<property name="interceptorNames">
<list>
<value>dbConnectionAdvisor</value>
</list>
</property>
</bean>
<bean id="dbConnectionAdvisor"
class="com.paic.pafa.app.lwc.core.aop.support.NameMatchMethodPointcutAdvisor">
<property name="mappedName">
<value>getConnection</value>
</property>
<property name="advice">
<ref bean="dbConnectionInterceptor"/>
</property>
</bean>
<bean id="dbConnectionInterceptor"
class="com.palic.egis.common.util.SetLcuInterceptor">
</bean>
<bean id="defaultDSTarget"
class="com.paic.pafa.app.lwc.core.naming.JndiObjectFactoryBean">
<property name="jndiName">
<value>${defaultDS}</value>
</property>
<property name="jndiTemplate">
<ref local="jndiTemplate"/>
</property>
</bean>
<bean id="gbsDS_XA"
class="com.paic.pafa.app.lwc.core.naming.JndiObjectFactoryBean">
<property name="jndiName">
<value>${GBSDS_XA}</value>
</property>
<property name="jndiTemplate">
<ref local="jndiTemplate"/>
</property>
</bean>
SetLcuInterceptor.java
public class SetLcuInterceptor implements MethodInterceptor {
public Object invoke(MethodInvocation mi) throws Throwable {
String userId = PafaCoreContexton.getInstance().getThreadContext().getUserID();
Connection conn = (Connection) mi.proceed();
if (userId != null && !userId.startsWith("V_") && !userId.equalsIgnoreCase("GMONIUSER")) {
try {
PreparedStatement stat = null;
// 设置LCU
try {
stat = conn.prepareStatement("{call pub_sys_package.set_attributes(?)}");
stat.setString(1, userId);
stat.execute();
} catch (Throwable e) {
throw e;
} finally {
if (stat != null) {
stat.close();
}
}
} catch (Throwable e) {
DevLog.error("SetLcuInterceptor Error:" + e.getMessage());
}
}
return conn;
}
}
public class SetLcuInterceptor implements MethodInterceptor {
public Object invoke(MethodInvocation mi) throws Throwable {
String userId = PafaCoreContexton.getInstance().getThreadContext().getUserID();
Connection conn = (Connection) mi.proceed();
if (userId != null && !userId.startsWith("V_") && !userId.equalsIgnoreCase("GMONIUSER")) {
try {
PreparedStatement stat = null;
// 设置LCU
try {
stat = conn.prepareStatement("{call pub_sys_package.set_attributes(?)}");
stat.setString(1, userId);
stat.execute();
} catch (Throwable e) {
throw e;
} finally {
if (stat != null) {
stat.close();
}
}
} catch (Throwable e) {
DevLog.error("SetLcuInterceptor Error:" + e.getMessage());
}
}
return conn;
}
}
pub_sys_package
CREATE OR REPLACE PACKAGE BODY pub_sys_package IS
--get user的两种访问1/pro
PROCEDURE get_user_p(p_user OUT VARCHAR2) IS
BEGIN
p_user := get_user;
END get_user_p;
--get user的两种访问2/fun
FUNCTION get_user RETURN VARCHAR2 IS
v_user VARCHAR2(100);
CURSOR cur_empno IS
SELECT user_empno
FROM gbs_user
--根据登录名称查询
WHERE user_name = USER;
BEGIN
SELECT sys_context('user_policy_context', 'uid')
INTO v_user
FROM dual;
IF v_user IS NULL THEN
OPEN cur_empno;
FETCH cur_empno
INTO v_user;
CLOSE cur_empno;
END IF;
IF v_user IS NULL THEN
v_user := USER;
END IF;
RETURN v_user;
END get_user;
--***********************************************
-- 功能说明:
-- 在获得Connection时调用,用于将用户信息设置到数据库的context中
-- 参数说明:
-- uid 用户标志
-- 调用函数:
-- 无
--***********************************************
PROCEDURE set_attributes(uid VARCHAR2) IS
regionlist VARCHAR2(100) := NULL;
BEGIN
dbms_session.set_context('user_policy_context', 'logon', 'true');
dbms_session.set_context('user_policy_context', 'uid', uid);
END set_attributes;
--***********************************************
-- 功能说明:
-- 在获得Connection时调用,用于将用户信息设置到数据库的context中。egis-pos专用
-- 在lcu长度不够的时代,保全利用该过程传入full_uid,记录在另外的字段中
-- 参数说明:
-- uid 用户标志
-- 调用函数:
-- 无
--***********************************************
PROCEDURE set_attributes(uid VARCHAR2, full_uid VARCHAR2) IS
regionlist VARCHAR2(100) := NULL;
BEGIN
dbms_session.set_context('user_policy_context', 'logon', 'true');
dbms_session.set_context('user_policy_context', 'uid', uid);
dbms_session.set_context('user_policy_context', 'full_uid', full_uid);
END set_attributes;
--***********************************************
-- 功能说明:
-- 数据库集中:将从UM中获取的region信息set到context中
-- 参数说明:
-- uid 用户名
-- access_region 可访问的region列表,以,分隔
-- 调用函数:
-- 无
--***********************************************
PROCEDURE set_access_attributes(uid VARCHAR2, access_region VARCHAR2) IS
BEGIN
dbms_session.set_context('user_policy_context', 'logon', 'true');
dbms_session.set_context('user_policy_context', 'uid', uid);
dbms_session.set_context('user_policy_context',
'access_region',
access_region);
END set_access_attributes;
FUNCTION get_fcu(infcu IN VARCHAR2) RETURN VARCHAR2 IS
v_fcu VARCHAR2(100);
v_user_empno VARCHAR2(100);
BEGIN
v_user_empno := pub_sys_package.get_user;
IF v_user_empno = 'SOLIX' THEN
--如果是归档用户,则使用原有记录的数据
v_fcu := infcu;
ELSE
v_fcu := v_user_empno;
END IF;
RETURN v_fcu;
END;
FUNCTION get_fcd(infcd IN VARCHAR2) RETURN DATE IS
v_fcd DATE;
v_user_empno VARCHAR2(100);
BEGIN
v_user_empno := pub_sys_package.get_user;
IF v_user_empno = 'SOLIX' THEN
--如果是归档用户,则使用原有记录的数据
v_fcd := infcd;
ELSE
v_fcd := SYSDATE;
END IF;
RETURN v_fcd;
END;
FUNCTION get_lcu(inlcu IN VARCHAR2) RETURN VARCHAR2 IS
v_lcu VARCHAR2(100);
BEGIN
v_lcu := pub_sys_package.get_user;
IF v_lcu IS NULL THEN
v_lcu := inlcu;
end IF;
RETURN v_lcu;
END;
FUNCTION get_lcd(inlcd IN VARCHAR2) RETURN DATE IS
v_lcd DATE;
BEGIN
v_lcd := SYSDATE;
RETURN v_lcd;
END;
END pub_sys_package;
CREATE OR REPLACE PACKAGE BODY pub_sys_package IS
--get user的两种访问1/pro
PROCEDURE get_user_p(p_user OUT VARCHAR2) IS
BEGIN
p_user := get_user;
END get_user_p;
--get user的两种访问2/fun
FUNCTION get_user RETURN VARCHAR2 IS
v_user VARCHAR2(100);
CURSOR cur_empno IS
SELECT user_empno
FROM gbs_user
--根据登录名称查询
WHERE user_name = USER;
BEGIN
SELECT sys_context('user_policy_context', 'uid')
INTO v_user
FROM dual;
IF v_user IS NULL THEN
OPEN cur_empno;
FETCH cur_empno
INTO v_user;
CLOSE cur_empno;
END IF;
IF v_user IS NULL THEN
v_user := USER;
END IF;
RETURN v_user;
END get_user;
--***********************************************
-- 功能说明:
-- 在获得Connection时调用,用于将用户信息设置到数据库的context中
-- 参数说明:
-- uid 用户标志
-- 调用函数:
-- 无
--***********************************************
PROCEDURE set_attributes(uid VARCHAR2) IS
regionlist VARCHAR2(100) := NULL;
BEGIN
dbms_session.set_context('user_policy_context', 'logon', 'true');
dbms_session.set_context('user_policy_context', 'uid', uid);
END set_attributes;
--***********************************************
-- 功能说明:
-- 在获得Connection时调用,用于将用户信息设置到数据库的context中。egis-pos专用
-- 在lcu长度不够的时代,保全利用该过程传入full_uid,记录在另外的字段中
-- 参数说明:
-- uid 用户标志
-- 调用函数:
-- 无
--***********************************************
PROCEDURE set_attributes(uid VARCHAR2, full_uid VARCHAR2) IS
regionlist VARCHAR2(100) := NULL;
BEGIN
dbms_session.set_context('user_policy_context', 'logon', 'true');
dbms_session.set_context('user_policy_context', 'uid', uid);
dbms_session.set_context('user_policy_context', 'full_uid', full_uid);
END set_attributes;
--***********************************************
-- 功能说明:
-- 数据库集中:将从UM中获取的region信息set到context中
-- 参数说明:
-- uid 用户名
-- access_region 可访问的region列表,以,分隔
-- 调用函数:
-- 无
--***********************************************
PROCEDURE set_access_attributes(uid VARCHAR2, access_region VARCHAR2) IS
BEGIN
dbms_session.set_context('user_policy_context', 'logon', 'true');
dbms_session.set_context('user_policy_context', 'uid', uid);
dbms_session.set_context('user_policy_context',
'access_region',
access_region);
END set_access_attributes;
FUNCTION get_fcu(infcu IN VARCHAR2) RETURN VARCHAR2 IS
v_fcu VARCHAR2(100);
v_user_empno VARCHAR2(100);
BEGIN
v_user_empno := pub_sys_package.get_user;
IF v_user_empno = 'SOLIX' THEN
--如果是归档用户,则使用原有记录的数据
v_fcu := infcu;
ELSE
v_fcu := v_user_empno;
END IF;
RETURN v_fcu;
END;
FUNCTION get_fcd(infcd IN VARCHAR2) RETURN DATE IS
v_fcd DATE;
v_user_empno VARCHAR2(100);
BEGIN
v_user_empno := pub_sys_package.get_user;
IF v_user_empno = 'SOLIX' THEN
--如果是归档用户,则使用原有记录的数据
v_fcd := infcd;
ELSE
v_fcd := SYSDATE;
END IF;
RETURN v_fcd;
END;
FUNCTION get_lcu(inlcu IN VARCHAR2) RETURN VARCHAR2 IS
v_lcu VARCHAR2(100);
BEGIN
v_lcu := pub_sys_package.get_user;
IF v_lcu IS NULL THEN
v_lcu := inlcu;
end IF;
RETURN v_lcu;
END;
FUNCTION get_lcd(inlcd IN VARCHAR2) RETURN DATE IS
v_lcd DATE;
BEGIN
v_lcd := SYSDATE;
RETURN v_lcd;
END;
END pub_sys_package;
数据库表中trigger实例:
插入
create or replace trigger TR_I_table_name
before insert on table_name
for each row
declare
--通用变量定义
v_trigger_user varchar2(100);
v_trigger_date date;
v_sqlcode varchar2(6);
v_sqlerrm varchar2(200);
v_error_comment varchar2(300);
--针对审计字段更新功能定义的游标和变量
cursor c_switch(cp_switch gbs_tr_switch.switch_for%type) is
select status from gbs_tr_switch
where trigger_name='TR_I_table_name' and switch_for =cp_switch;
v_status gbs_tr_switch.status%type;
begin
v_error_comment:='before get user';
v_trigger_user :=pub_sys_package.get_user;
v_trigger_date :=sysdate;
--需求来源:表中记录的审计字段信息更新
--功能描述:用于保证审计信息的完整性
v_error_comment:='before GBS_insert_4_audit_column';
open c_switch('table_name_in');
fetch c_switch into v_status;
if c_switch%found and v_status ='1' then
:new.created_by:=v_trigger_user;
:new.created_date:=v_trigger_date;
:new.updated_by:=v_trigger_user;
:new.updated_date:=v_trigger_date;
end if;
close c_switch;
--需球来源:XXXX
--功能描述:XXXX
--错误处理
--触发器执行有误,将出错信息插入到gbs_tr_error_log表
exception
when others then
v_sqlcode :=sqlcode;
v_sqlerrm :=substr(sqlerrm,1,200);
insert into gbs_tr_error_log
(
error_no , --系统错误代码
error_message , --系统错误信息
trigger_name , --出错的trigger
trigger_user , --出错的用户
trigger_date , --出错的时间
error_comment --出错详细信息
)
values
(
v_sqlcode,
v_sqlerrm,
'TR_I_table_name',
v_trigger_user,
v_trigger_date,
v_error_comment
);
end;
create or replace trigger TR_I_table_name
before insert on table_name
for each row
declare
--通用变量定义
v_trigger_user varchar2(100);
v_trigger_date date;
v_sqlcode varchar2(6);
v_sqlerrm varchar2(200);
v_error_comment varchar2(300);
--针对审计字段更新功能定义的游标和变量
cursor c_switch(cp_switch gbs_tr_switch.switch_for%type) is
select status from gbs_tr_switch
where trigger_name='TR_I_table_name' and switch_for =cp_switch;
v_status gbs_tr_switch.status%type;
begin
v_error_comment:='before get user';
v_trigger_user :=pub_sys_package.get_user;
v_trigger_date :=sysdate;
--需求来源:表中记录的审计字段信息更新
--功能描述:用于保证审计信息的完整性
v_error_comment:='before GBS_insert_4_audit_column';
open c_switch('table_name_in');
fetch c_switch into v_status;
if c_switch%found and v_status ='1' then
:new.created_by:=v_trigger_user;
:new.created_date:=v_trigger_date;
:new.updated_by:=v_trigger_user;
:new.updated_date:=v_trigger_date;
end if;
close c_switch;
--需球来源:XXXX
--功能描述:XXXX
--错误处理
--触发器执行有误,将出错信息插入到gbs_tr_error_log表
exception
when others then
v_sqlcode :=sqlcode;
v_sqlerrm :=substr(sqlerrm,1,200);
insert into gbs_tr_error_log
(
error_no , --系统错误代码
error_message , --系统错误信息
trigger_name , --出错的trigger
trigger_user , --出错的用户
trigger_date , --出错的时间
error_comment --出错详细信息
)
values
(
v_sqlcode,
v_sqlerrm,
'TR_I_table_name',
v_trigger_user,
v_trigger_date,
v_error_comment
);
end;
更新
CREATE OR REPLACE TRIGGER tr_u_table_name
BEFORE UPDATE ON table_name
FOR EACH ROW
DECLARE
--通用变量定义
v_trigger_user VARCHAR2(100);
v_trigger_date DATE;
v_sqlcode VARCHAR2(6);
v_sqlerrm VARCHAR2(200);
v_error_comment VARCHAR2(300);
--针对审计字段更新功能定义的游标和变量
CURSOR c_switch(cp_switch gbs_tr_switch.switch_for%TYPE) IS
SELECT status
FROM gbs_tr_switch
WHERE trigger_name = 'tr_u_table_name'
AND switch_for = cp_switch;
v_status gbs_tr_switch.status%TYPE;
BEGIN
v_error_comment := 'before get user';
v_trigger_user := pub_sys_package.get_user;
v_trigger_date := SYSDATE;
--需求来源:表中记录的审计字段信息更新
--功能描述:用于保证审计信息的完整性
v_error_comment := 'before GBS_update_2_audit_column';
OPEN c_switch('table_name_up');
FETCH c_switch
INTO v_status;
IF c_switch%FOUND
AND v_status = '1' THEN
:new.updated_by := v_trigger_user;
:new.updated_date := v_trigger_date;
END IF;
CLOSE c_switch;
EXCEPTION
WHEN OTHERS THEN
v_sqlcode := SQLCODE;
v_sqlerrm := substr(SQLERRM, 1, 200);
INSERT INTO tr_error_log
(error_no, --系统错误代码
error_message, --系统错误信息
trigger_name, --出错的trigger
trigger_user, --出错的用户
trigger_date, --出错的时间
error_comment --出错详细信息
)
VALUES
(v_sqlcode,
v_sqlerrm,
'tr_u_table_name',
v_trigger_user,
v_trigger_date,
v_error_comment);
END;
CREATE OR REPLACE TRIGGER tr_u_table_name
BEFORE UPDATE ON table_name
FOR EACH ROW
DECLARE
--通用变量定义
v_trigger_user VARCHAR2(100);
v_trigger_date DATE;
v_sqlcode VARCHAR2(6);
v_sqlerrm VARCHAR2(200);
v_error_comment VARCHAR2(300);
--针对审计字段更新功能定义的游标和变量
CURSOR c_switch(cp_switch gbs_tr_switch.switch_for%TYPE) IS
SELECT status
FROM gbs_tr_switch
WHERE trigger_name = 'tr_u_table_name'
AND switch_for = cp_switch;
v_status gbs_tr_switch.status%TYPE;
BEGIN
v_error_comment := 'before get user';
v_trigger_user := pub_sys_package.get_user;
v_trigger_date := SYSDATE;
--需求来源:表中记录的审计字段信息更新
--功能描述:用于保证审计信息的完整性
v_error_comment := 'before GBS_update_2_audit_column';
OPEN c_switch('table_name_up');
FETCH c_switch
INTO v_status;
IF c_switch%FOUND
AND v_status = '1' THEN
:new.updated_by := v_trigger_user;
:new.updated_date := v_trigger_date;
END IF;
CLOSE c_switch;
EXCEPTION
WHEN OTHERS THEN
v_sqlcode := SQLCODE;
v_sqlerrm := substr(SQLERRM, 1, 200);
INSERT INTO tr_error_log
(error_no, --系统错误代码
error_message, --系统错误信息
trigger_name, --出错的trigger
trigger_user, --出错的用户
trigger_date, --出错的时间
error_comment --出错详细信息
)
VALUES
(v_sqlcode,
v_sqlerrm,
'tr_u_table_name',
v_trigger_user,
v_trigger_date,
v_error_comment);
END;