Spacewalk DB Schema - Table-RHNUSERGROUPMEMBERS

Main Tables Views Materialized Views Indexes Constraints Triggers Procedures Functions Packages Sequences Java Sources Jobs Sanity Check Index DDL scrips

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

RHNUSERGROUPMEMBERS

DDL script

Columns

Name	Type	Nullable	Default value
USER_ID	NUMBER(38)	N
USER_GROUP_ID	NUMBER(38)	N
TEMPORARY	CHAR(1)	N	('N')
CREATED	TIMESTAMP(6) WITH LOCAL TIME ZONE	N	(current_timestamp)
MODIFIED	TIMESTAMP(6) WITH LOCAL TIME ZONE	N	(current_timestamp)

Check Constraints:

Constraint Name	Check Condition
RHN_UGMEMBERS_T_CK	temporary in ('Y', 'N')

Foreign Keys:

Constraint Name	Columns	Referenced table	Referenced Constraint	On Delete Rule
RHN_UGMEMBERS_UGID_FK	USER_GROUP_ID	RHNUSERGROUP	RHN_USER_GROUP_PK	NO ACTION
RHN_UGMEMBERS_UID_FK	USER_ID	WEB_CONTACT	WEB_CONTACT_PK	CASCADE

Options:

Option	Settings
Tablespace	USERS
Index Organized	No
Generated by Oracle	No
Clustered	No
Nested	No
Temporary	No

Indexes:

Index Name	Type	Unuqueness	Columns	DDL script
RHN_UGMEMBERS_UGID_IDX	NORMAL	NONUNIQUE	USER_GROUP_ID	DDL script
RHN_UGMEMBERS_UID_UGID_TEMP_UQ	NORMAL	UNIQUE	USER_ID , USER_GROUP_ID , TEMPORARY	DDL script

Triggers

RHN_UG_MEMBER_DEL_TRIG

Legend: comment string keyword reserved word operator

CREATE TRIGGER 
rhn_ug_member_del_trig
before delete on rhnUserGroupMembers
for each row

REFERENCING NEW AS NEW OLD AS OLD
begin
        update rhnUserGroup
        set current_members = current_members - 1
        where id = :old.user_group_id;
end;

RHN_UG_MEMBER_MOD_TRIG

Legend: comment string keyword reserved word operator

CREATE TRIGGER 
rhn_ug_member_mod_trig
before insert or update on rhnUserGroupMembers
for each row

REFERENCING NEW AS NEW OLD AS OLD
declare
        ug              rhnUserGroup%ROWTYPE;
begin
        :new.modified := current_timestamp;

        if inserting then
                select
                        * into ug
                from
                        rhnUserGroup
                where
                        id = :new.user_group_id;

                if ug.max_members is not null and
                ug.current_members+1 gt; ug.max_members then
                        rhn_exception.raise_exception('usergroup_max_members');
                end if;

                update rhnUserGroup
                set current_members = current_members + 1
                where id = :new.user_group_id;
        end if;
end;

RHN_USER_GROUP_ORG_MAPPING

Legend: comment string keyword reserved word operator

CREATE TRIGGER 
rhn_user_group_org_mapping
BEFORE INSERT OR UPDATE ON rhnUserGroupMembers
FOR EACH ROW

REFERENCING NEW AS NEW OLD AS OLD
DECLARE
        same_org        NUMBER;
BEGIN
        same_org := 0;
        SELECT 1 INTO same_org
          FROM web_contact U, rhnUserGroup UG
         WHERE UG.org_id = U.org_id
           AND U.id = :new.user_id
           AND UG.id = :new.user_group_id;

        IF same_org = 0 THEN
          rhn_exception.raise_exception('ugm_different_orgs');
        END IF;
EXCEPTION
        WHEN NO_DATA_FOUND THEN
          rhn_exception.raise_exception('ugm_different_orgs');
END;