Edit D:\app\Administrator\product\11.2.0\dbhome

Edit D:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\mgdtrg.sql

Rem
Rem $Header: mgdtrg.sql 01-jun-2006.10:19:19 hgong Exp $
Rem
Rem mgdtrg.sql
Rem
Rem Copyright (c) 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      mgdtrg.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    hgong       05/31/06 - move after delete trigger logic to utility 
Rem    hgong       05/16/06 - rename MGD_ID_META to MGD_ID_UTL 
Rem    hgong       04/04/06 - rename oidcode.jar 
Rem    hgong       03/31/06 - create triggers 
Rem    hgong       03/31/06 - Created
Rem

prompt .. Creating the idcode_scheme_before_ins_trig trigger which validates each TDT before insertion and sets the type_name and encoding fields appropriately

CREATE OR REPLACE TRIGGER mgd_id_scheme_before_ins_trig
BEFORE INSERT ON mgd_id_scheme_tab
FOR EACH ROW
DECLARE
  type_and_encodings VARCHAR2(1024);
  pos1               INTEGER;
  pos2               INTEGER;
BEGIN
  type_and_encodings := DBMS_MGD_ID_UTL.validate_scheme(:new.tdt_xml);
  pos1 := instr(type_and_encodings, ';');
  pos2 := instr(type_and_encodings, ';', 1, 2);
  :new.type_name := substr(type_and_encodings, 0, pos1 - 1);
  :new.encodings := substr(type_and_encodings, pos1 + 1, pos2 - pos1 - 1); 
  :new.components := substr(type_and_encodings, pos2 + 1, length(type_and_encodings) - pos2); 
END;
/
show errors;

prompt .. Creating the idcode_scheme_before_upd_trig trigger which validates the new scheme after update and refreshes the category to which it belongs

CREATE OR REPLACE TRIGGER mgd_id_scheme_before_upd_trig
BEFORE UPDATE ON mgd_id_scheme_tab
FOR EACH ROW
DECLARE
  type_and_encodings VARCHAR2(1024);
  pos1               INTEGER;
  pos2               INTEGER;
BEGIN
  type_and_encodings := DBMS_MGD_ID_UTL.validate_scheme(:NEW.tdt_xml);
  pos1 := instr(type_and_encodings, ';');
  pos2 := instr(type_and_encodings, ';', 1, 2);
  :new.type_name := substr(type_and_encodings, 0, pos1 - 1);
  :new.encodings := substr(type_and_encodings, pos1 + 1, pos2 - pos1 - 1); 
  :new.components := substr(type_and_encodings, pos2 + 1, length(type_and_encodings) - pos2);  
  DBMS_MGD_ID_UTL.refresh_category(to_char(:old.category_id));
END;
/
show errors;

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de