Main Tables Views Indexes Constraints Triggers Procedures Functions Packages Sanity check Index

ACS3 Data Model

Description Columns Primary key Check Constraints Foreign keys Unique Keys Options Indexes Referenced by Triggers

BM_LIST

Columns

NameTypeNullableDefault valueComment
BOOKMARK_IDNUMBER(38)N  
PARENT_SORT_KEYVARCHAR2(99.0)Y  
LOCAL_SORT_KEYCHAR(3.0)N  
OWNER_IDNUMBER(38)N  
CREATION_DATEDATEN  
MODIFICATION_DATEDATEY  
URL_IDNUMBER(38)Y  
LOCAL_TITLEVARCHAR2(500.0)Y  
PRIVATE_PCHAR(1.0)Y'f'  
HIDDEN_PCHAR(1.0)Y'f'  
FOLDER_PCHAR(1.0)Y'f'  
PARENT_IDNUMBER(38)Y  
CLOSED_PCHAR(1.0)Y't'  
IN_CLOSED_PCHAR(1.0)Y'f'  

Primary key:

Constraint NameColumns
SYS_C002087868BOOKMARK_ID

Check Constraints:

Constraint NameCheck Condition
SYS_C002087860"LOCAL_SORT_KEY" IS NOT NULL
SYS_C002087861"OWNER_ID" IS NOT NULL
SYS_C002087862"CREATION_DATE" IS NOT NULL
SYS_C002087863private_p in ('t','f')
SYS_C002087864hidden_p in ('t','f')
SYS_C002087865folder_p in ('t','f')
SYS_C002087866closed_p in ('t','f')
SYS_C002087867in_closed_p in ('t','f')

Foreign Keys:

Constraint NameColumnsReferenced tableReferenced ConstraintOn Delete Rule
SYS_C002087869OWNER_ID USERS SYS_C002087135 NO ACTION
SYS_C002087870URL_ID BM_URLS SYS_C002087859 NO ACTION
SYS_C002087871PARENT_ID BM_LIST SYS_C002087868 NO ACTION

Options:

OptionSettings
Index OrganizedNo
Generated by OracleNo
ClusteredNo
NestedNo
TemporaryNo

Indexes:

Index NameTypeUnuquenessColumns
BM_LIST_IDX1NORMALNONUNIQUEBOOKMARK_ID , PARENT_ID
BM_LIST_IDX2NORMALNONUNIQUEPARENT_ID , BOOKMARK_ID
SYS_C002087868NORMALUNIQUEBOOKMARK_ID

Referenced by:

TableConstraint
BM_LIST SYS_C002087871

Triggers

Name: BM_LIST_AFTER_U_TR
CREATE TRIGGER bm_list_after_u_tr
  after UPDATE OF parent_id ON bm_list
REFERENCING NEW AS NEW OLD AS OLD
DECLARE
  v_bookmark_id		bm_list.bookmark_id%TYPE;
  v_row 		bm_list%ROWTYPE;
  v_last_sort_key 	bm_list.local_sort_key%TYPE;
  v_parent_sort_key 	bm_list.parent_sort_key%TYPE;
  v_count               INTEGER;
BEGIN
   FOR v_loop_index IN 1 .. bm_list_pkg.v_num_entries LOOP
      -- Fix up local_sort_key and parent_sort_key.
      v_bookmark_id := bm_list_pkg.v_updated_ids(v_loop_index);
      bm_fixup_sort_key(v_bookmark_id);
   END LOOP;
   bm_list_pkg.v_num_entries := 0;
END bm_list_after_u_tr;

Name: BM_LIST_SORT_KEY_I_TR
CREATE TRIGGER bm_list_sort_key_i_tr before INSERT ON bm_list
FOR each row
REFERENCING NEW AS NEW OLD AS OLD
DECLARE
  v_last_sort_key bm_list.local_sort_key%TYPE;
  v_parent_sort_key bm_list.parent_sort_key%TYPE;
BEGIN
   IF :NEW.parent_id IS NULL THEN
      SELECT max(local_sort_key) INTO v_last_sort_key
	FROM bm_list
	WHERE parent_id IS NULL;
      v_parent_sort_key := null;
   ELSE
      SELECT max(local_sort_key) INTO v_last_sort_key
	FROM bm_list
	WHERE parent_id = :NEW.parent_id;
      SELECT parent_sort_key || local_sort_key INTO v_parent_sort_key
	FROM bm_list WHERE bookmark_id = :NEW.parent_id;
   END IF;

   :NEW.local_sort_key := new_sort_key(v_last_sort_key);
   :NEW.parent_sort_key := v_parent_sort_key;
END;

Name: BM_LIST_SORT_KEY_ROW_U_TR
CREATE TRIGGER bm_list_sort_key_row_u_tr
  before UPDATE OF parent_id ON bm_list
  FOR each row
REFERENCING NEW AS NEW OLD AS OLD
BEGIN
  bm_list_pkg.v_num_entries := bm_list_pkg.v_num_entries + 1;
  bm_list_pkg.v_updated_ids(bm_list_pkg.v_num_entries) := :NEW.bookmark_id;
END bm_list_sort_key_u_tr;



Generated by OraSchemaDoc, (c) Aram Kananov, 2002