CREATE TABLE [dbo].[ca_group_def]
(
[group_uuid] [binary] (16) NOT NULL,
[domain_uuid] [binary] (16) NOT NULL,
[eval_freq] [int] NULL,
[last_eval_date_time] [int] NULL,
[query_uuid] [binary] (16) NULL,
[member_type] [int] NULL,
[label] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[evaluation_uuid] [binary] (16) NULL,
[group_inheritance] [smallint] NULL,
[evaluation_location] [smallint] NULL,
[last_update_user] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[last_update_date] [int] NULL,
[version_number] [int] NULL CONSTRAINT [DF__ca_group___versi__42CCE065] DEFAULT ((0)),
[creation_date] [int] NULL,
[creation_user] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[group_flag] [smallint] NULL,
[group_system] [smallint] NULL,
[security_group] [int] NULL,
[description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[auto_rep_version] [timestamp] NULL
) ON [PRIMARY]
GO
/****** Object:  Trigger dbo.r_del_group_def    Script Date: 12/1/2005 11:51:20 PM ******/

/*
*********************************************************
*********************************************************
* Procedures and triggers to manage integrity of groups
*********************************************************
*********************************************************
*/

/*
***********************************************
delete all entries of the ca_group_meber tabel if
a ca_group_def object is deleted
*/

CREATE trigger r_del_group_def
    on ca_group_def
    instead of delete
as
    
begin
    

  delete from ca_group_member   from deleted  d  where  ca_group_member.group_uuid = d.group_uuid;
  delete from ca_group_member   from deleted  d  where  ca_group_member.member_uuid = d.group_uuid;

  delete from ca_agent  from deleted d where ca_agent.object_uuid = d.group_uuid and ca_agent.agent_type = 0;
  delete from ca_group_def from deleted d where ca_group_def.group_uuid = d.group_uuid and ca_group_def.domain_uuid = d.domain_uuid;

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


/*
***********************************************
    trigger on after update (label, description)
    on ca_group_def to track all changes in
    ca_group_def about label and description
***********************************************
*/

CREATE trigger r_u_ca_group_def
    on ca_group_def
    after update
as
    if update(label) or update(description)
begin
    declare @group_uuid binary(16);
    declare @label nvarchar(255);
    declare @description nvarchar(255);    
    declare @domain_uuid binary(16);

    declare cursor_ca_group_def cursor local for
        select group_uuid, label, description, domain_uuid from inserted

    open cursor_ca_group_def;
    fetch next from cursor_ca_group_def into @group_uuid, @label, @description, @domain_uuid; -- get first
    while @@fetch_status = 0
    begin
    
        execute p_urc_ab_ca_group_def_updated @group_uuid, @label, @description, @domain_uuid;
        fetch next from cursor_ca_group_def into @group_uuid, @label, @description, @domain_uuid;

    end; -- while
    close cursor_ca_group_def;
    deallocate cursor_ca_group_def;

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



/*
***********************************************
trigger on update on ca_group_def
*/
CREATE trigger r_upd_verno_group_def
        on ca_group_def
        after update        
        as
                declare @old_verno as int;
                declare @new_verno as int;
        begin
            if update (version_number)
            begin

                DECLARE mycur CURSOR
                    FOR select deleted.version_number, inserted.version_number from inserted, deleted

                OPEN mycur
                FETCH NEXT FROM mycur INTO @old_verno,@new_verno
                WHILE @@FETCH_STATUS = 0
                BEGIN

                    execute  p_integrity_version_number @old_verno ,  @new_verno ;
                    FETCH NEXT FROM mycur INTO @old_verno,@new_verno;

                END

                CLOSE mycur
                DEALLOCATE mycur

                
            end;
        end;
GO
/*
**********************************************
procedure runs if a group entity was deleted
delete assigned entries in
ca_group_ace
ca_object_ace
ca_link_object_owner
*/

/*
**********************************************
* cleanup OLS if a group entity was deleted
*/
CREATE trigger rule_d_so_removed_group
on ca_group_def
after delete
as
begin

  set nocount on;
  delete ca_group_ace
    from deleted d
    where ca_group_ace.group_def_uuid = d.group_uuid;

  delete ca_object_ace
    from deleted d
      where ca_object_ace.object_def_uuid = d.group_uuid;

  delete ca_link_object_owner
    from deleted d
    where object_uuid = d.group_uuid;

  delete ols_area_ace
    from deleted d
    where object_def_uuid = d.group_uuid;

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




/*
********************************************
trigger for creating a new group
*/
CREATE trigger rule_i_new_so_group
       on ca_group_def
    after insert
as
begin
  declare @_obj_uuid binary(16);
  declare @_security_group integer;
  declare @_grp_type  integer;
  declare @_uri  nvarchar(255);


    select @_obj_uuid = (select group_uuid from inserted);
    select @_security_group = (select security_group from inserted);
    select @_grp_type = (select member_type from inserted);
    select @_uri = (select creation_user from inserted);


    execute  proc_i_new_so_group @_obj_uuid, @_security_group, @_grp_type, @_uri;
end;
GO
/****** Object:  Trigger dbo.rule_u_so_updated_group    Script Date: 12/1/2005 11:51:50 PM ******/



/*
************************************
trigger for updating a group definition
and the column security_group  was changed
  update of (security_group)
*/
CREATE trigger [dbo].[rule_u_so_updated_group]
       on [dbo].[ca_group_def]
          after update
as
begin
    declare @ugrp_uuid binary(16);
    declare @old_grpinheritance integer;
    declare @new_grpinheritance integer;
    
    DECLARE cur_u_so_updated_group cursor local
        FOR select deleted.group_uuid, deleted.security_group, inserted.security_group from deleted, inserted
            where inserted.group_uuid = deleted.group_uuid /* itrac 12093*/

    OPEN cur_u_so_updated_group
    FETCH NEXT FROM cur_u_so_updated_group INTO @ugrp_uuid, @old_grpinheritance, @new_grpinheritance;    
    WHILE @@FETCH_STATUS = 0
    BEGIN

        execute  proc_u_so_updated_group    @ugrp_uuid, @old_grpinheritance, @new_grpinheritance;    
        FETCH NEXT FROM cur_u_so_updated_group INTO @ugrp_uuid, @old_grpinheritance, @new_grpinheritance;    

    END

    CLOSE cur_u_so_updated_group
    DEALLOCATE cur_u_so_updated_group

end;
GO
/****** Object:  Trigger dbo.usd_trg_d_ca_group_def_tbl_ver    Script Date: 12/1/2005 11:51:55 PM ******/
/* Rule on ca_group_def, on delete change USD table version */
CREATE trigger usd_trg_d_ca_group_def_tbl_ver
on ca_group_def
for delete as
declare
    @i_member_type int
begin
        set @i_member_type = (select top 1 member_type from deleted)
        exec usd_proc_u_tbl_ver 1, @i_member_type, -1, -1
end
GO
/****** Object:  Trigger dbo.usd_trg_d_group_usd_rel    Script Date: 12/1/2005 11:51:56 PM ******/
CREATE trigger usd_trg_d_group_usd_rel
on ca_group_def
for delete as
declare
    @countedTempl integer,
    @myDomainUuid binary(16)
begin
    -- Check templates

    -- Check if the group about to be deleted is a replicated group
    -- If it is then go ahead and unseal and allow the deletion of the group
    -- If not check if it is linked to a sealed template, if it is
    -- then raise error, that will then result in a warning message to the user
    set @myDomainUuid  = (select set_val_uuid from ca_settings where set_id = 1)
    set @countedTempl = (select count(*)
    from usd_job_cont jc, deleted d
    where jc.qtemplfold = d.group_uuid
    and seal = 1
    and d.domain_uuid = @myDomainUuid)
    if(@countedTempl > 0)
    begin
        -- This is a local group, raise error
    raiserror('Error 9012: Cannot remove the group because it is linked to a sealed Software Policy', 16, 1 )
        rollback transaction
    return
    end
    else
    begin
        -- Delete relation from catalog folder(s) to asset groups
        update usd_swfold set auxtext2 = '',
        aux = 0x00000000000000000000000000000000,
        version = version + 1
        from deleted d
        where aux = d.group_uuid

        -- Delete relation from job container (template) to asset groups and unseal it
        update usd_job_cont set auxtext2 = '',
        qtemplfold = 0x00000000000000000000000000000000,
        seal = 0,
        version = version + 1
        from deleted d
        where qtemplfold = d.group_uuid

        -- Delete relation from usd_link_act_grp (target groups) to asset groups
        delete from usd_link_act_grp
        from deleted d
        where grp = d.group_uuid

        -- Make sure we update the class version for the above operations
        update usd_class_version set modify_version = modify_version + 1
        where name = 'swfold'
        or name = 'job_cont'
        or name = 'link_act_grp'
    end
end
GO
/****** Object:  Trigger dbo.usd_trg_u_ca_group_def_tbl_ver    Script Date: 12/1/2005 11:51:57 PM ******/
/* Rule on ca_group_def, on update change USD table version */
CREATE trigger usd_trg_u_ca_group_def_tbl_ver
on ca_group_def
for insert, update as
declare
    @i_member_type int
begin
    if update(version_number) or update(creation_user) or update(evaluation_uuid) or update(label) or update(description) or update(creation_date) or update(last_update_date) or update(eval_freq) or update(last_eval_date_time) or update(query_uuid) or update(evaluation_location) or update(group_flag) or update(group_system)
    begin
        set @i_member_type = (select top 1 member_type from inserted)
        exec usd_proc_u_tbl_ver 1, @i_member_type, -1, -1
    end
end
GO
ALTER TABLE [dbo].[ca_group_def] ADD CONSTRAINT [XPKca_group_def] PRIMARY KEY CLUSTERED ([group_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_group_def_idx_01] ON [dbo].[ca_group_def] ([label]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ca_group_def] ADD CONSTRAINT [$ca_gr_r0000088c00000000] FOREIGN KEY ([evaluation_uuid]) REFERENCES [dbo].[ca_engine] ([engine_uuid])
GO
ALTER TABLE [dbo].[ca_group_def] ADD CONSTRAINT [$ca_gr_r0000089600000000] FOREIGN KEY ([query_uuid]) REFERENCES [dbo].[ca_query_def] ([query_uuid])
GO
GRANT SELECT ON  [dbo].[ca_group_def] TO [ca_itrm_group]
GRANT INSERT ON  [dbo].[ca_group_def] TO [ca_itrm_group]
GRANT DELETE ON  [dbo].[ca_group_def] TO [ca_itrm_group]
GRANT UPDATE ON  [dbo].[ca_group_def] TO [ca_itrm_group]
GRANT SELECT ON  [dbo].[ca_group_def] TO [ca_itrm_group_ams]
GRANT SELECT ON  [dbo].[ca_group_def] TO [dms_backup_group]
GRANT INSERT ON  [dbo].[ca_group_def] TO [dms_backup_group]
GRANT DELETE ON  [dbo].[ca_group_def] TO [dms_backup_group]
GRANT UPDATE ON  [dbo].[ca_group_def] TO [dms_backup_group]
GRANT SELECT ON  [dbo].[ca_group_def] TO [regadmin]
GRANT SELECT ON  [dbo].[ca_group_def] TO [upmuser_group]
GO