CREATE TABLE [dbo].[urc_ab_group_def]
(
[uid] [binary] (16) NOT NULL,
[domain_uuid] [binary] (16) NOT NULL,
[label] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[sub_root_item] [binary] (1) NOT NULL,
[inheritance] [binary] (1) NOT NULL,
[update_children] [binary] (1) NOT NULL,
[update_viewer_version] [int] NOT NULL CONSTRAINT [DF__urc_ab_gr__updat__056F97C1] DEFAULT ((1)),
[auto_rep_version] [timestamp] NULL
) ON [PRIMARY]
GO
/****** Object:  Trigger dbo.r_u_urc_group_def    Script Date: 12/1/2005 11:50:36 PM ******/



/*
**************************************************
**************************************************
* install URC specific database definitions
**************************************************
**************************************************
*/


/*
The following procedures are executed only
on the originating manager machine (own domain_uuid):
*/


/*
***********************************************
urc_ab_update_version - trigger and procedure
(used to update the urc_ab_group_def entry for the viewer)
***********************************************
*/
CREATE trigger r_u_urc_group_def
     on urc_ab_group_def
     after update
as     
    declare @rowcnt integer;
    set @rowcnt = @@ROWCOUNT;
    
    if update(inheritance) or update(sub_root_item) or update(update_children)
    begin

        declare @domain_uuid binary(16);
        
        /* retrieve own domain_uuid */
        set @domain_uuid = (select set_val_uuid
                            from ca_settings
                            where set_id = 1)
                            
        if @rowcnt = 1
        begin
            update urc_ab_group_def set update_viewer_version = update_viewer_version+1
                where urc_ab_group_def.uid = (select uid from inserted)
                    and urc_ab_group_def.domain_uuid = @domain_uuid;
        end
        else
        begin
            update urc_ab_group_def set update_viewer_version = update_viewer_version+1
                where urc_ab_group_def.uid IN (select uid from inserted)
                    and urc_ab_group_def.domain_uuid = @domain_uuid;
        end

    end
GO
/****** Object:  Trigger dbo.r_urc_ab_ca_group_def_deleted    Script Date: 12/1/2005 11:50:51 PM ******/

/*
***********************************************
an entry in urc_ab_group_def is deleted    
***********************************************
*/

CREATE trigger r_urc_ab_ca_group_def_deleted
    on urc_ab_group_def
    after delete
as    
begin
    declare @uid  binary(16);
    declare @domain_uuid binary(16);
    
    declare cursor_urc_ab_group_def cursor local for
        select uid, domain_uuid from deleted

    open cursor_urc_ab_group_def;
    fetch next from cursor_urc_ab_group_def into @uid, @domain_uuid; -- get first
    while @@fetch_status = 0
    begin
    
        execute p_urc_ab_ca_group_def_deleted @uid, @domain_uuid;
        fetch next from cursor_urc_ab_group_def into @uid, @domain_uuid;

    end; -- while
    close cursor_urc_ab_group_def;
    deallocate cursor_urc_ab_group_def;
    
end
GO
CREATE trigger r_urc_ab_group_def_created
    on urc_ab_group_def
    after insert
as
/****** Object:  Trigger dbo.r_urc_ab_group_def_created    Script Date: 12/1/2005 11:51:33 PM ******/

                                                

/*
***********************************************
an entry in urc_ab_group_def has been created

includes:
- p_urc_ab_group_def_create - procedure:
   creates an urc_ab_group_def entry
- p_urc_ab_group_def_created:
   a new entry in urc_ab_group_def is created
***********************************************
*/


begin    
    declare @uid  binary(16);
    declare @domain_uuid binary(16);

    set @uid  = (select uid from inserted);
    set @domain_uuid = (select domain_uuid from inserted);

    -- execute p_urc_ab_group_def_created @uid, @domain_uuid;    

    declare @member_uuid binary(16);
    declare @group_uuid binary(16);
    declare @member_type integer;
    declare @group_domain_uuid binary(16);
    declare @member_domain_uuid binary(16);
    declare @urcObjCnt integer;
    declare @own_domain_uuid binary(16);
    declare @nTierDomainType integer;


    /* retrieve own domain_uuid */
    set @own_domain_uuid = (select set_val_uuid
                            from ca_settings
                            where set_id = 1)
    /* execute procedure only for own domain_uuid */
    if (@domain_uuid = @own_domain_uuid)
    begin

        declare cursor_ca_group_member cursor local
        for
            select member_uuid, group_uuid, member_type, group_domain_uuid , member_domain_uuid
            from ca_group_member
            where group_uuid = @uid
            order by member_type desc

        open cursor_ca_group_member
        fetch next from cursor_ca_group_member into @member_uuid, @group_uuid, @member_type, @group_domain_uuid, @member_domain_uuid
        while @@fetch_status = 0
        begin

            if (@member_type = 0)
            begin
            
                /* type = groupDef --> computer group */
                /* check if the child itself is part of urc_ab_group_def table */
                set @urcObjCnt = 0;
                set rowcount 1;
                set @urcObjCnt = (select count(*) from urc_ab_group_def where uid = @member_uuid);
                set rowcount 0;
                    
                if (@urcObjCnt = 0)
                begin
                    /* create an urc_ab_group_def entry: */
                    -- execute p_urc_ab_group_def_create @member_uuid, @domain_uuid;
                    insert into urc_ab_group_def (uid, domain_uuid, label, description, sub_root_item, inheritance, update_children, update_viewer_version)
                                (select @member_uuid, @member_domain_uuid, label, description, cast('0' as binary(1)), cast('1' as binary(1)), cast('0' as binary(1)), 1
                                    from ca_group_def where group_uuid = @member_uuid);

                end
                else
                begin
                    /* if it is, update the entry: */
                    /* the statement below will  update the version field for the viewer because of possibly new inherited permissions */
                    update urc_ab_group_def set update_children = (cast('1' as binary(1))) where uid = @member_uuid;
                end
                    
                /*    create a new urc_ab_group_member entry */
                execute p_urc_ab_group_member_create @member_uuid, @group_uuid, @member_type, @group_domain_uuid, @member_domain_uuid;

            end
            else
            begin
            
                if (@member_type = 1)
                begin
                
                    /* type = discoveredAsset --> computer */
                    /* check if member_uuid is part of the urc_ab_computer table */
                    /* of the own domain  */
                    set @urcObjCnt = 0;
                    set rowcount 1;
                    set @urcObjCnt = (select count(*) from urc_ab_computer where (uid = @member_uuid and domain_uuid = @domain_uuid));
                    set rowcount 0;

                    if (@urcObjCnt > 0)
                    begin
                        /* it is --> */
                        /* create a new urc_ab_group_member entry */
                        /* with the member_domain_uuid of the own domain ! */                    
                        execute p_urc_ab_group_member_create @member_uuid, @group_uuid, @member_type, @group_domain_uuid, @group_domain_uuid;
                    end                        
                    else
                    begin
                        /* :_urcObjCnt = 0 */
                        /* check if we are at an enterprise manager, get domain_type */
                        set @nTierDomainType = 0;
                        set @nTierDomainType = (select domain_type from ca_n_tier where domain_uuid = @own_domain_uuid)

                        if (@nTierDomainType = 1)
                        begin
                            /* we are an enterprise manager,                  */
                            /* entpr. urc_ab_computer entry doesn't exist yet */
                            /* --> create it with the enterprise domain_uuid  */    
                            insert into urc_ab_computer (uid, domain_uuid, label,
                                                host_name, connection_information, host_uuid)
                                                (select uid, @own_domain_uuid, label,
                                                 host_name, connection_information, host_uuid
                                                 from urc_ab_computer
                                                 where (uid = @member_uuid and domain_uuid = @member_domain_uuid));

                            /* --> p_urc_ab_computer_created is executed */
                            /* --> that will add the member entry and    */
                            /*     update the parents                    */
                        end
                        
                    end

                end -- if (@member_type = 1)
            end

    
            fetch next from cursor_ca_group_member into @member_uuid, @group_uuid, @member_type, @group_domain_uuid, @member_domain_uuid

        end    -- cursor loop
        
        close cursor_ca_group_member
        deallocate cursor_ca_group_member
        
    end -- if (@domain_uuid = @own_domain_uuid)

end
GO
/****** Object:  Trigger dbo.r_urc_ab_update_children    Script Date: 12/1/2005 11:50:39 PM ******/


/*
***********************************************
urc_ab_update_children - trigger - and procedure

used to update the children of an urc_ab_group_def,
through repeated activation of the trigger the whole
children tree will be updated recursively
***********************************************
*/

CREATE trigger r_urc_ab_update_children
    on urc_ab_group_def
    after update
as
    if update(update_children)    
    begin
    
        declare    @domain_uuid binary(16);

        /* retrieve own domain_uuid */
        set @domain_uuid = (select set_val_uuid
                            from ca_settings
                            where set_id = 1)

        declare @uid binary(16);
        declare @upd_children binary(1);
        declare @group_domain_uuid binary(16);

        declare cursor_urc_ab_group_def cursor local for
            select uid, domain_uuid, update_children from inserted
    
        open cursor_urc_ab_group_def;
        fetch next from cursor_urc_ab_group_def into @uid, @group_domain_uuid, @upd_children; -- get first
        while @@fetch_status = 0
        begin
        
            --execute p_urc_ab_update_children @uid, @upd_children, @group_domain_uuid;
            /* for all group children set update_children = '1' */
            if (@group_domain_uuid = @domain_uuid) and (@upd_children = (cast('1' as binary(1))))
            begin
                update urc_ab_group_def
                    set update_children = (cast('1' as binary(1)))
                    where uid IN (select member_uuid
                                  from urc_ab_group_member
                                  where group_uuid =@uid and member_type = 0)
            end
            
            fetch next from cursor_urc_ab_group_def into @uid, @group_domain_uuid, @upd_children;

        end; -- while
        close cursor_urc_ab_group_def;
        deallocate cursor_urc_ab_group_def;

    end
GO
ALTER TABLE [dbo].[urc_ab_group_def] ADD CONSTRAINT [XPKurc_ab_group_def] PRIMARY KEY CLUSTERED ([uid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [urc_ab_group_def_i2] ON [dbo].[urc_ab_group_def] ([domain_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [urc_ab_group_def_i1] ON [dbo].[urc_ab_group_def] ([sub_root_item]) ON [PRIMARY]
GO
GRANT SELECT ON  [dbo].[urc_ab_group_def] TO [ams_group]
GRANT SELECT ON  [dbo].[urc_ab_group_def] TO [ca_itrm_group]
GRANT INSERT ON  [dbo].[urc_ab_group_def] TO [ca_itrm_group]
GRANT DELETE ON  [dbo].[urc_ab_group_def] TO [ca_itrm_group]
GRANT UPDATE ON  [dbo].[urc_ab_group_def] TO [ca_itrm_group]
GRANT SELECT ON  [dbo].[urc_ab_group_def] TO [ca_itrm_group_ams]
GRANT SELECT ON  [dbo].[urc_ab_group_def] TO [upmuser_group]
GO