触发器 orcale 代码 和 添加 字段 和注释 和 常见sql

 



-- 在表custommanage 中新增如下字段--
ALTER TABLE custommanage 
alert table table
--投诉原因名称-- 
ADD  TSREASON VARCHAR2(50) ;
add colunm varchar2(50)
comment on column table.column is
'描述'
comment on column custommanage.TSREASON
  is '投诉原因名称';
 --投诉原因描述--
ALTER TABLE custommanage 
ADD  TSREASONDES VARCHAR2(200);
comment on column custommanage.TSREASONDES
  is '投诉原因描述';




--在表FX_CUSTOMER 中新增如下字段
--业务类型-- 
ALTER TABLE FX_CUSTOMER 
ADD  operationType VARCHAR2(20) ;
comment on column FX_CUSTOMER.operationType
  is '业务类型';
 --客户经理
ALTER TABLE FX_CUSTOMER 
ADD  customerManager VARCHAR2(20);
comment on column FX_CUSTOMER.customerManager
  is '客户经理';
--押金-- 
ALTER TABLE FX_CUSTOMER 
ADD  deposit Number(10) ;
comment on column FX_CUSTOMER.deposit
  is '客户经理';
 --合同编号
ALTER TABLE FX_CUSTOMER 
ADD  contractNo VARCHAR2(20);
comment on column FX_CUSTOMER.contractNo
  is '合同编号';
--信用额度-- 
ALTER TABLE FX_CUSTOMER 
ADD  credit Number(20) ;
comment on column FX_CUSTOMER.credit
  is '信用额度';


 --商旅资质
ALTER TABLE FX_CUSTOMER 
ADD  tourApt VARCHAR2(50);
comment on column FX_CUSTOMER.tourApt
  is '商旅资质';
--客户登记-- 
ALTER TABLE FX_CUSTOMER 
ADD  customerLevel VARCHAR2(10) ;
comment on column FX_CUSTOMER.customerLevel
  is '客户登记';










触发器


create or replace trigger TR_FX_CUSTOMER
before  update of 
  SUBCOMPANY,CUSTOMERLEVEL ,CONTRACTNO,DEPOSIT, FXCUSTOMER ,CODE,STATE,TYPE,NAME,EFFECTIVE ,
   BUSINESSLINE,QQ,EMAIL,COUNTRY,ADDRESS, PROVINCE,CITY,AREA,CONTACT,PHONE,MOBILE,PAYDAY,CREATENAME,CREATETIME,LASTUPDATENAME,LASTUPDATETIME,FAX,MSN,CUSTOMERCATEGORY, BANKNAME,BANKCODE,BANKUSERNAME,
ERPMESSAGE,YYZZ,IDCARD,CSTMATTRIBUTE,OPERATIONTYPE,CUSTOMERMANAGER,CREDIT,TOURAPT on FX_CUSTOMER 
for each row
declare
   begin
   if :new.SUBCOMPANY<>:old.SUBCOMPANY then
   insert into sys_log values(me_sequence.nextval,:new.ID,1,:new.lastupdatename,sysdate,'所属公司',:old.SUBCOMPANY,:new.SUBCOMPANY,sys_context('userenv','ip_address'),'fx_customer','');
     
   end if;
   if :new.CUSTOMERCATEGORY<>:old.CUSTOMERCATEGORY then
      insert into sys_log values(me_sequence.nextval,:new.ID,1,:new.lastupdatename,sysdate,'分类 ',:old.CUSTOMERCATEGORY,:new.CUST