触发器

DROP TRIGGER GAM.TRIG_UPDATE_RESOURCE_PROPERTY@

CREATE TRIGGER GAM.TRIG_UPDATE_RESOURCE_PROPERTY
  AFTER UPDATE
  ON GAM.GAM_SYS_SYSTEM
  REFERENCING
              NEW AS NEWR
  FOR EACH ROW
BEGIN ATOMIC

declare  v_Enterprise_No  varchar(50);
 declare v_System_No     varchar(50);
 declare v_Org_Status    INTEGER;
 declare v_State_Status  INTEGER;

 

              SET v_Enterprise_No = NEWR.ENTERPRISE_NO;
  SET v_System_No     = NEWR.SYSTEM_NO;
  SET v_Org_Status    = INTEGER(substr(NEWR.RESOURCE_PROPERTY_STATUS, 2, 1));
  SET v_State_Status  = INTEGER(substr(NEWR.RESOURCE_PROPERTY_STATUS, 5, 1));

 
                 update gam_res_type_prop
     set status = v_Org_Status
   where prop_code = 'ORG'
     and is_public = 1
     and res_type_id in (select line_id
                           from gam_res_type
                          where enterprise_no = v_Enterprise_No
                            and system_no = v_System_No);
                 update gam_res_type_prop
     set status = v_State_Status
   where prop_code = 'STATE'
     and is_public = 1
     and res_type_id in (select line_id
                           from gam_res_type
                          where enterprise_no = v_Enterprise_No
                            and system_no = v_System_No);
 END@