- 建触发器状态控制表
-- Create table
create table ATPTRIG.TR_SWITCH
(
TRIGGER_NAME VARCHAR2(30) not null,
SWITCH_FOR VARCHAR2(20) not null,
STATUS VARCHAR2(1) default '1' not null
);
-- Add comments to the table
comment on table ATPTRIG.TR_SWITCH
is 'trigger控制表';
-- Add comments to the columns
comment on column ATPTRIG.TR_SWITCH.TRIGGER_NAME
is '触发器名称';
comment on column ATPTRIG.TR_SWITCH.SWITCH_FOR
is '属组';
comment on column ATPTRIG.TR_SWITCH.STATUS
is '状态';
-- Create/Recreate primary, unique and foreign key constraints
alter table ATPTRIG.TR_SWITCH
add constraint PK_TR_SWITCH primary key (TRIGGER_NAME, SWITCH_FOR);
- 建记录表
-- Create table
create table GAPTRIG.TR_LOG_PUBLIC
(
TABLE_NAME VARCHAR2(30) not null,
COLUMN_NAME VARCHAR2(30) not null,
PK_SERIAL VARCHAR2(20) not null,
LOG_TYPE VARCHAR2(1) not null,
OLD_VALUE VARCHAR2(2000),
NEW_VALUE VARCHAR2(2000),
TRIGGER_NO VARCHAR2(20) not null,
TRIGGER_NAME VARCHAR2(30) not null,
TRIGGER_USER VARCHAR2(30) default user not null,
TRIGGER_DATE DATE default sysdate not null
)
PARTITION BY RANGE (TRIGGER_DATE) INTERVAL (numtoyminterval(1, 'month'))
(partition part_t01 values less than(to_date('2023-05-01', 'yyyy-mm-dd')));
-- Add comments to the columns
comment on column GAPTRIG.TR_LOG_PUBLIC.TABLE_NAME
is '业务表';
comment on column GAPTRIG.TR_LOG_PUBLIC.COLUMN_NAME
is '字段';
comment on column GAPTRIG.TR_LOG_PUBLIC.PK_SERIAL
is '业务主键';
comment on column GAPTRIG.TR_LOG_PUBLIC.LOG_TYPE
is '类型（I:insert;D:delete;U:update）';
comment on column GAPTRIG.TR_LOG_PUBLIC.OLD_VALUE
is '旧值';
comment on column GAPTRIG.TR_LOG_PUBLIC.NEW_VALUE
is '新值';
comment on column GAPTRIG.TR_LOG_PUBLIC.TRIGGER_NO
is '';
comment on column GAPTRIG.TR_LOG_PUBLIC.TRIGGER_NAME
is '触发器名称';
comment on column GAPTRIG.TR_LOG_PUBLIC.TRIGGER_USER
is '触发人';
comment on column GAPTRIG.TR_LOG_PUBLIC.TRIGGER_DATE
is '触发时间';
-- Create/Recreate check constraints
alter table GAPTRIG.TR_LOG_PUBLIC
add constraint CK_TR_LOG_LOGTYPE
check (Log_type in ('I','D','U'));
-- 建序列
create sequence GAPTRIG.SEQ_TR_LOG_PUBLIC
minvalue 1
maxvalue 9999999999999999
start with 1
increment by 1
cache 30
cycle;
- 建表新增触发器
create or replace trigger gaptrig.tr_bi_gap_bus_interface_fmcs
before insert on GAPDATA.GAP_BUSINESS_INTERFACE_FMCS
referencing new as new old as old
for each row
declare
v_trigger_user varchar2(50);
v_trigger_date date;
cursor c_switch(cp_switch atp_tr_switch.switch_for%type) is
select status from tr_switch
where trigger_name = upper('tr_bi_gap_bus_interface_fmcs')
and switch_for = cp_switch;
v_status atp_tr_switch.status%type;
v_sqlcode varchar2(6);
v_sqlerrm varchar2(200);
v_error_comment varchar2(300);
begin
v_error_comment:='before get_user';
v_trigger_user := pub_sys_package.get_user();
v_trigger_date := sysdate;
--功能描述：逻辑控制内容，用于保证数据完整性
v_error_comment:='before ATP_SYNCH';
open c_switch('ATP_SYNCH'); --获取子功能控制信息
fetch c_switch into v_status;
if c_switch%FOUND and v_status = '1' then
:new.created_user := v_trigger_user;
:new.created_date := v_trigger_date;
:new.updated_user := 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 gap_tr_error_log
(error_no , --系统错误代码
error_message , --系统错误信息
trigger_name , --出错的trigger
trigger_user , --出错的用户
trigger_date , --出错的时间
error_comment --出错详细信息
)
values
(v_sqlcode,
v_sqlerrm,
upper('tr_bi_gap_bus_interface_fmcs'),
v_trigger_user,
v_trigger_date,
v_error_comment
);
end tr_bi_gap_bus_interface_fmcs;
/
- 建表更新触发器
create or replace trigger gaptrig.tr_bu_gap_bus_interface_fmcs
before update ON GAPDATA.GAP_BUSINESS_INTERFACE_FMCS
referencing new as new old as old
for each row
declare
v_trigger_user varchar2(50);
v_trigger_date date;
v_trigger_name varchar2(30) := 'tr_bu_gap_bus_interface_fmcs';
v_switch_for varchar2(30) := 'ATP_SYNCH';
cursor c_switch(cp_switch atp_tr_switch.switch_for%type) is
select status
from tr_switch
where trigger_name = upper(v_trigger_name)
and switch_for = v_switch_for
;
v_status atp_tr_switch.status%type;
v_sqlcode varchar2(6);
v_sqlerrm varchar2(200);
v_error_comment varchar2(300);
v_seq varchar2(20);
v_flag varchar2(1);
v_message varchar2(400);
begin
v_error_comment := 'before get_user';
v_trigger_user := pub_sys_package.get_user();
v_trigger_date := sysdate;
--需求来源：SLIS数据同步
--功能描述：逻辑控制内容，用于保证数据完整性
v_error_comment:='before ATP_SYNCH';
open c_switch(v_switch_for); --获取子功能控制信息
fetch c_switch into v_status;
if c_switch%FOUND and v_status = '1' then
:new.updated_user := 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 gap_tr_error_log(
error_no --系统错误代码
,error_message --系统错误信息
,trigger_name --出错的trigger
,trigger_user --出错的用户
,trigger_date --出错的时间
,error_comment --出错详细信息
)
values(v_sqlcode
,v_sqlerrm
,upper(v_trigger_name)
,v_trigger_user
,v_trigger_date
,v_error_comment
);
end tr_bu_gap_bus_interface_fmcs;
/
- 建表字段变更触发器
create or replace trigger GAPTRIG.TR_BU_GAP_BUS_INFACE_SSC_LOG
after update on GAPDATA.GAP_BUSINESS_INTERFACE_SSC
referencing new as new old as old
for each row
declare
v_trigger_user varchar2(50);
v_trigger_date date;
v_trigger_name varchar2(30) := 'TR_BU_GAP_BUS_INFACE_SSC_LOG';
cursor c_switch(cp_switch varchar2) is
select status
from tr_switch
where trigger_name = upper(v_trigger_name)
and switch_for = cp_switch;
v_status atp_tr_switch.status%type;
v_sqlcode varchar2(6);
v_sqlerrm varchar2(200);
v_error_comment varchar2(300);
v_seq_no varchar2(20);
begin
v_error_comment := 'before get_user';
v_trigger_user := pub_sys_package.get_user();
v_trigger_date := sysdate;
open c_switch('ATP_SYNCH');
fetch c_switch
into v_status;
if c_switch%found and v_status = '1' then
select SEQ_GAP_BUSI_INTER_SSC_LOG.nextval into v_seq_no from dual;
v_error_comment := 'column log PK_SERIAL';
if (NVL(:old.PK_SERIAL, '~') <> NVL(:new.PK_SERIAL, '~')) then
insert into TR_LOG_GAP_BUSI_INTERFACE_SSC --????????
values
(v_seq_no,
'TR_BU_GAP_BUS_INFACE_SSC_LOG',
'GAP_BUSINESS_INTERFACE_SSC',
:old.pk_serial,
'PK_SERIAL',
'U',
:old.PK_SERIAL,
:new.PK_SERIAL,
v_trigger_user,
v_trigger_date);
end if;
v_error_comment := 'column log BUSINESS_NO_TYPE';
if (NVL(:old.BUSINESS_NO_TYPE, '~') <> NVL(:new.BUSINESS_NO_TYPE, '~')) then
insert into TR_LOG_GAP_BUSI_INTERFACE_SSC --????????
values
(v_seq_no,
'TR_BU_GAP_BUS_INFACE_SSC_LOG',
'GAP_BUSINESS_INTERFACE_SSC',
:old.pk_serial,
'BUSINESS_NO_TYPE',
'U',
:old.BUSINESS_NO_TYPE,
:new.BUSINESS_NO_TYPE,
v_trigger_user,
v_trigger_date);
end if;
v_error_comment := 'column log BUSINESS_NO';
if (NVL(:old.BUSINESS_NO, '~') <> NVL(:new.BUSINESS_NO, '~')) then
insert into TR_LOG_GAP_BUSI_INTERFACE_SSC --????????
values
(v_seq_no,
'TR_BU_GAP_BUS_INFACE_SSC_LOG',
'GAP_BUSINESS_INTERFACE_SSC',
:old.pk_serial,
'BUSINESS_NO',
'U',
:old.BUSINESS_NO,
:new.BUSINESS_NO,
v_trigger_user,
v_trigger_date);
end if;
v_error_comment := 'column log BUSINESS_BOOK';
if (NVL(:old.BUSINESS_BOOK, '~') <> NVL(:new.BUSINESS_BOOK, '~')) then
insert into TR_LOG_GAP_BUSI_INTERFACE_SSC --????????
values
(v_seq_no,
'TR_BU_GAP_BUS_INFACE_SSC_LOG',
'GAP_BUSINESS_INTERFACE_SSC',
:old.pk_serial,
'BUSINESS_BOOK',
'U',
:old.BUSINESS_BOOK,
:new.BUSINESS_BOOK,
v_trigger_user,
v_trigger_date);
end if;
v_error_comment := 'column log BUSINESS_SYSTEM';
if (NVL(:old.BUSINESS_SYSTEM, '~') <> NVL(:new.BUSINESS_SYSTEM, '~')) then
insert into TR_LOG_GAP_BUSI_INTERFACE_SSC --????????
values
(v_seq_no,
'TR_BU_GAP_BUS_INFACE_SSC_LOG',
'GAP_BUSINESS_INTERFACE_SSC',
:old.pk_serial,
'BUSINESS_SYSTEM',
'U',
:old.BUSINESS_SYSTEM,
:new.BUSINESS_SYSTEM,
v_trigger_user,
v_trigger_date);
end if;
v_error_comment := 'column log BRANCHID';
if (NVL(:old.BRANCHID, '~') <> NVL(:new.BRANCHID, '~')) then
insert into TR_LOG_GAP_BUSI_INTERFACE_SSC --????????
values
(v_seq_no,
'TR_BU_GAP_BUS_INFACE_SSC_LOG',
'GAP_BUSINESS_INTERFACE_SSC',
:old.pk_serial,
'BRANCHID',
'U',
:old.BRANCHID,
:new.BRANCHID,
v_trigger_user,
v_trigger_date);
end if;
v_error_comment := 'column log INTER_BRANCHID';
if (NVL(:old.INTER_BRANCHID, '~') <> NVL(:new.INTER_BRANCHID, '~')) then
insert into TR_LOG_GAP_BUSI_INTERFACE_SSC --????????
values
(v_seq_no,
'TR_BU_GAP_BUS_INFACE_SSC_LOG',
'GAP_BUSINESS_INTERFACE_SSC',
:old.pk_serial,
'INTER_BRANCHID',
'U',
:old.INTER_BRANCHID,
:new.INTER_BRANCHID,
v_trigger_user,
v_trigger_date);
end if;
v_error_comment := 'column log CURRENCY_CODE';
if (NVL(:old.CURRENCY_CODE, '~') <> NVL(:new.CURRENCY_CODE, '~')) then
insert into TR_LOG_GAP_BUSI_INTERFACE_SSC --????????
values
(v_seq_no,
'TR_BU_GAP_BUS_INFACE_SSC_LOG',
'GAP_BUSINESS_INTERFACE_SSC',
:old.pk_serial,
'CURRENCY_CODE',
'U',
:old.CURRENCY_CODE,
:new.CURRENCY_CODE,
v_trigger_user,
v_trigger_date);
end if;
v_error_comment := 'column log AMOUNT';
if (NVL(:old.AMOUNT, 0) <> NVL(:new.AMOUNT, 0)) then
insert into TR_LOG_GAP_BUSI_INTERFACE_SSC --????????
values
(v_seq_no,
'TR_BU_GAP_BUS_INFACE_SSC_LOG',
'GAP_BUSINESS_INTERFACE_SSC',
:old.pk_serial,
'AMOUNT',
'U',
:old.AMOUNT,
:new.AMOUNT,
v_trigger_user,
v_trigger_date);
end if;
v_error_comment := 'column log DOCUMENT_DATE';
if (NVL(:old.DOCUMENT_DATE, TO_DATE('20191001', 'YYYYMMDD')) <>
NVL(:new.DOCUMENT_DATE, TO_DATE('20191001', 'YYYYMMDD'))) then
insert into TR_LOG_GAP_BUSI_INTERFACE_SSC --????????
values
(v_seq_no,
'TR_BU_GAP_BUS_INFACE_SSC_LOG',
'GAP_BUSINESS_INTERFACE_SSC',
:old.pk_serial,
'DOCUMENT_DATE',
'U',
TO_CHAR(:old.DOCUMENT_DATE, 'YYYY-MM-DD HH24:MI:SS'),
TO_CHAR(:new.DOCUMENT_DATE, 'YYYY-MM-DD HH24:MI:SS'),
v_trigger_user,
v_trigger_date);
end if;
v_error_comment := 'column log PAYMENT_CHANNEL';
if (NVL(:old.PAYMENT_CHANNEL, '~') <> NVL(:new.PAYMENT_CHANNEL, '~')) then
insert into TR_LOG_GAP_BUSI_INTERFACE_SSC --????????
values
(v_seq_no,
'TR_BU_GAP_BUS_INFACE_SSC_LOG',
'GAP_BUSINESS_INTERFACE_SSC',
:old.pk_serial,
'PAYMENT_CHANNEL',
'U',
:old.PAYMENT_CHANNEL,
:new.PAYMENT_CHANNEL,
v_trigger_user,
v_trigger_date);
end if;
v_error_comment := 'column log BUSINESS_AMT_TYPE';
if (NVL(:old.BUSINESS_AMT_TYPE, '~') <>
NVL(:new.BUSINESS_AMT_TYPE, '~')) then
insert into TR_LOG_GAP_BUSI_INTERFACE_SSC --????????
values
(v_seq_no,
'TR_BU_GAP_BUS_INFACE_SSC_LOG',
'GAP_BUSINESS_INTERFACE_SSC',
:old.pk_serial,
'BUSINESS_AMT_TYPE',
'U',
:old.BUSINESS_AMT_TYPE,
:new.BUSINESS_AMT_TYPE,
v_trigger_user,
v_trigger_date);
end if;
v_error_comment := 'column log ACCOUNT';
if (NVL(:old.ACCOUNT, '~') <> NVL(:new.ACCOUNT, '~')) then
insert into TR_LOG_GAP_BUSI_INTERFACE_SSC --????????
values
(v_seq_no,
'TR_BU_GAP_BUS_INFACE_SSC_LOG',
'GAP_BUSINESS_INTERFACE_SSC',
:old.pk_serial,
'ACCOUNT',
'U',
:old.ACCOUNT,
:new.ACCOUNT,
v_trigger_user,
v_trigger_date);
end if;
v_error_comment := 'column log SUBJECTS';
if (NVL(:old.SUBJECTS, '~') <> NVL(:new.SUBJECTS, '~')) then
insert into TR_LOG_GAP_BUSI_INTERFACE_SSC --????????
values
(v_seq_no,
'TR_BU_GAP_BUS_INFACE_SSC_LOG',
'GAP_BUSINESS_INTERFACE_SSC',
:old.pk_serial,
'SUBJECTS',
'U',
:old.SUBJECTS,
:new.SUBJECTS,
v_trigger_user,
v_trigger_date);
end if;
v_error_comment := 'column log SUBSUBJECTS';
if (NVL(:old.SUBSUBJECTS, '~') <>
NVL(:new.SUBSUBJECTS, '~')) then
insert into TR_LOG_GAP_BUSI_INTERFACE_SSC --????????
values
(v_seq_no,
'TR_BU_GAP_BUS_INFACE_SSC_LOG',
'GAP_BUSINESS_INTERFACE_SSC',
:old.pk_serial,
'SUBSUBJECTS',
'U',
:old.SUBSUBJECTS,
:new.SUBSUBJECTS,
v_trigger_user,
v_trigger_date);
end if;
v_error_comment := 'column log CASH_FLOW';
if (NVL(:old.CASH_FLOW, '~') <>
NVL(:new.CASH_FLOW, '~')) then
insert into TR_LOG_GAP_BUSI_INTERFACE_SSC --????????
values
(v_seq_no,
'TR_BU_GAP_BUS_INFACE_SSC_LOG',
'GAP_BUSINESS_INTERFACE_SSC',
:old.pk_serial,
'CASH_FLOW',
'U',
:old.CASH_FLOW,
:new.CASH_FLOW,
v_trigger_user,
v_trigger_date);
end if;
v_error_comment := 'column log REMARK';
if (NVL(:old.REMARK, '~') <> NVL(:new.REMARK, '~')) then
insert into TR_LOG_GAP_BUSI_INTERFACE_SSC --????????
values
(v_seq_no,
'TR_BU_GAP_BUS_INFACE_SSC_LOG',
'GAP_BUSINESS_INTERFACE_SSC',
:old.pk_serial,
'REMARK',
'U',
:old.REMARK,
:new.REMARK,
v_trigger_user,
v_trigger_date);
end if;
v_error_comment := 'column log BEP_VOUCHER_NO';
if (NVL(:old.BEP_VOUCHER_NO, '~') <> NVL(:new.BEP_VOUCHER_NO, '~')) then
insert into TR_LOG_GAP_BUSI_INTERFACE_SSC --????????
values
(v_seq_no,
'TR_BU_GAP_BUS_INFACE_SSC_LOG',
'GAP_BUSINESS_INTERFACE_SSC',
:old.pk_serial,
'BEP_VOUCHER_NO',
'U',
:old.BEP_VOUCHER_NO,
:new.BEP_VOUCHER_NO,
v_trigger_user,
v_trigger_date);
end if;
v_error_comment := 'column log EBS_CASH_FOW';
if (NVL(:old.EBS_CASH_FOW, '~') <> NVL(:new.EBS_CASH_FOW, '~')) then
insert into TR_LOG_GAP_BUSI_INTERFACE_SSC --????????
values
(v_seq_no,
'TR_BU_GAP_BUS_INFACE_SSC_LOG',
'GAP_BUSINESS_INTERFACE_SSC',
:old.pk_serial,
'EBS_CASH_FOW',
'U',
:old.EBS_CASH_FOW,
:new.EBS_CASH_FOW,
v_trigger_user,
v_trigger_date);
end if;
v_error_comment := 'column log SUMMARY';
if (NVL(:old.SUMMARY, '~') <> NVL(:new.SUMMARY, '~')) then
insert into TR_LOG_GAP_BUSI_INTERFACE_SSC --????????
values
(v_seq_no,
'TR_BU_GAP_BUS_INFACE_SSC_LOG',
'GAP_BUSINESS_INTERFACE_SSC',
:old.pk_serial,
'SUMMARY',
'U',
:old.SUMMARY,
:new.SUMMARY,
v_trigger_user,
v_trigger_date);
end if;
v_error_comment := 'column log INVALID_FLAG';
if (NVL(:old.INVALID_FLAG, '~') <> NVL(:new.INVALID_FLAG, '~')) then
insert into TR_LOG_GAP_BUSI_INTERFACE_SSC --????????
values
(v_seq_no,
'TR_BU_GAP_BUS_INFACE_SSC_LOG',
'GAP_BUSINESS_INTERFACE_SSC',
:old.pk_serial,
'INVALID_FLAG',
'U',
:old.INVALID_FLAG,
:new.INVALID_FLAG,
v_trigger_user,
v_trigger_date);
end if;
v_error_comment := 'column log DOCUMENT_MSG';
if (NVL(:old.DOCUMENT_MSG, '~') <> NVL(:new.DOCUMENT_MSG, '~')) then
insert into TR_LOG_GAP_BUSI_INTERFACE_SSC --????????
values
(v_seq_no,
'TR_BU_GAP_BUS_INFACE_SSC_LOG',
'GAP_BUSINESS_INTERFACE_SSC',
:old.pk_serial,
'DOCUMENT_MSG',
'U',
:old.DOCUMENT_MSG,
:new.DOCUMENT_MSG,
v_trigger_user,
v_trigger_date);
end if;
v_error_comment := 'column log FIN_ACCOUNT_FLAG';
if (NVL(:old.FIN_ACCOUNT_FLAG, '~') <> NVL(:new.FIN_ACCOUNT_FLAG, '~')) then
insert into TR_LOG_GAP_BUSI_INTERFACE_SSC --????????
values
(v_seq_no,
'TR_BU_GAP_BUS_INFACE_SSC_LOG',
'GAP_BUSINESS_INTERFACE_SSC',
:old.pk_serial,
'FIN_ACCOUNT_FLAG',
'U',
:old.FIN_ACCOUNT_FLAG,
:new.FIN_ACCOUNT_FLAG,
v_trigger_user,
v_trigger_date);
end if;
v_error_comment := 'column log COST_UNIQUE_NO';
if (NVL(:old.COST_UNIQUE_NO, '~') <> NVL(:new.COST_UNIQUE_NO, '~')) then
insert into TR_LOG_GAP_BUSI_INTERFACE_SSC --????????
values
(v_seq_no,
'TR_BU_GAP_BUS_INFACE_SSC_LOG',
'GAP_BUSINESS_INTERFACE_SSC',
:old.pk_serial,
'COST_UNIQUE_NO',
'U',
:old.COST_UNIQUE_NO,
:new.COST_UNIQUE_NO,
v_trigger_user,
v_trigger_date);
end if;
v_error_comment := 'column log REQUESTID';
if (NVL(:old.REQUESTID, '~') <> NVL(:new.REQUESTID, '~')) then
insert into TR_LOG_GAP_BUSI_INTERFACE_SSC --????????
values
(v_seq_no,
'TR_BU_GAP_BUS_INFACE_SSC_LOG',
'GAP_BUSINESS_INTERFACE_SSC',
:old.pk_serial,
'REQUESTID',
'U',
:old.REQUESTID,
:new.REQUESTID,
v_trigger_user,
v_trigger_date);
end if;
end if;
--????
exception
when others then
v_sqlcode := sqlcode;
v_sqlerrm := substr(sqlerrm, 1, 200);
insert into gap_tr_error_log
(error_no --??????
,
error_message --??????
,
trigger_name --???trigger
,
trigger_user --?????
,
trigger_date --?????
,
error_comment --??????
)
values
(v_sqlcode,
v_sqlerrm,
upper(v_trigger_name),
v_trigger_user,
v_trigger_date,
v_error_comment);
end TR_BU_GAP_BUS_INFACE_SSC_LOG;
获取用户名
create or replace package body appcde.pub_sys_package is
p_user varchar2(100);
--**********************************************************************************
--功能说明: 设置当前用户的id 2010.11.8 ACE
--调 用 者:
--被调用者:
--入口参数:
--出口参数:
--说 明: 用于设置在context中的当前用户的id，用于权限控制（视图）
--**********************************************************************************
procedure set_user(username varchar2)
is
begin
p_user:=username;
end set_user;
function get_user return varchar2
is
v_user varchar2(100);
begin
if p_user is null then
v_user:=SYS_CONTEXT ('USERENV', 'OS_USER');
else
v_user:=p_user;
end if;
if v_user is null then
select user into v_user from dual;
end if;
return v_user;
end get_user;
end pub_sys_package;
6092
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
