CREATE TABLE [dbo].[ca_group_ace]
(
[group_def_uuid] [binary] (16) NOT NULL,
[security_profile_uuid] [binary] (16) NOT NULL,
[ace] [int] NOT NULL,
[access] [int] NOT NULL,
[enable_inheritance] [int] NOT NULL,
[object_type] [int] NULL CONSTRAINT [DF__ca_group___objec__3FF073BA] DEFAULT ((1005)),
[security_level] [int] NOT NULL CONSTRAINT [DF__ca_group___secur__4B399FE2] DEFAULT ((0))
) ON [PRIMARY]
GO
/*
****************************************
rule if a new group_ace was inserted
trigger checks for consistence of object_type
all object_type attributes in one group must have
the same value
*/


CREATE trigger [dbo].[r_i_inserted_group_ace]
       on [dbo].[ca_group_ace]
       after insert
as
begin
    SET NOCOUNT ON;

    declare    @_count integer;                /* number of groups */

    set @_count = ( select count(*) from ca_group_ace gace, inserted i
                    where gace.group_def_uuid = i.group_def_uuid
                            and gace.object_type != i.object_type )

    if ( @_count > 0 )
    begin
        raiserror ('Error 9015: insert of group_ace denied, group with different object type exists', 16,1);
        return
    end

end;
GO
/****** Object:  Trigger dbo.rule_i_so_inserted_group_ace    Script Date: 12/1/2005 11:51:46 PM ******/


/*
****************************************
rule if a new group_ace was inserted
*/

CREATE trigger rule_i_so_inserted_group_ace
       on ca_group_ace
       after insert
as
begin
    declare    @_group_uuid binary(16);        /* new.group_def_uuid */
    declare    @_sp_uuid binary(16);            /* new.security_profile_uuid */
    declare    @_group_ace  integer;            /* new.ace */
    declare    @_group_enable_inheritance integer;    /* new.enable_inheritance */
  
    declare lst_igace cursor for
        select  group_def_uuid, security_profile_uuid ,ace, enable_inheritance
        from inserted;

    -- get first inserted group ace
    open lst_igace;
    fetch from lst_igace into @_group_uuid, @_sp_uuid, @_group_ace, @_group_enable_inheritance;
    while @@fetch_status = 0
    begin
        execute  proc_i_so_inserted_group_ace @_group_uuid, @_sp_uuid, @_group_ace, @_group_enable_inheritance;
        -- fetch next
        fetch from lst_igace into @_group_uuid, @_sp_uuid, @_group_ace, @_group_enable_inheritance;
    
    end;  -- end of loop over inserted grozp aces
    close lst_igace;
    deallocate lst_igace;

end;
GO
/*
************************************************
trigger for updating a group ace
*/
CREATE trigger [dbo].[rule_u_so_updated_group_ace]
on [dbo].[ca_group_ace]
after update
as
begin
    if update(ace) or update(security_level)
    begin
        declare @_group_uuid binary(16);
        declare @_sp_uuid binary(16);
        declare @_group_ace integer;
        declare @_group_enable_inheritance integer;
        declare @_group_old_ace integer;
        declare @_group_old_enable_inheritance integer;
        declare @_level_before integer;
        declare @_level integer;
        declare @_object_type integer;

        set nocount on;

        DECLARE mycur cursor local
            FOR select inserted.group_def_uuid, inserted.security_profile_uuid, inserted.ace,
            inserted.enable_inheritance, deleted.ace, deleted.enable_inheritance,
            inserted.object_type, deleted.security_level, inserted.security_level
                    from deleted, inserted
                    where inserted.group_def_uuid = deleted.group_def_uuid
                          and inserted.security_profile_uuid = deleted.security_profile_uuid /* itrac 12093*/

        OPEN mycur
        FETCH NEXT FROM mycur INTO @_group_uuid, @_sp_uuid, @_group_ace, @_group_enable_inheritance, @_group_old_ace, @_group_old_enable_inheritance,
            @_object_type, @_level_before, @_level
        WHILE @@FETCH_STATUS = 0
        BEGIN

            if update(security_level) and @_level=3 and @_level_before=2
            begin
                execute  proc_u_so_group_ace_revert @_level, @_group_uuid, @_sp_uuid, @_level_before, @_object_type
            end
            else
            if update(ace)
            begin
                    execute  proc_u_so_updated_group_ace @_group_uuid, @_sp_uuid, @_group_ace, @_group_enable_inheritance, @_group_old_ace, @_group_old_enable_inheritance
            end
            FETCH NEXT FROM mycur INTO @_group_uuid, @_sp_uuid, @_group_ace, @_group_enable_inheritance, @_group_old_ace, @_group_old_enable_inheritance,
                @_object_type, @_level_before, @_level

        END

        CLOSE mycur
        DEALLOCATE mycur
    end
end
GO
ALTER TABLE [dbo].[ca_group_ace] ADD CONSTRAINT [XPKca_group_ace] PRIMARY KEY CLUSTERED ([group_def_uuid], [security_profile_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [x1ca_group_ace] ON [dbo].[ca_group_ace] ([security_profile_uuid]) ON [PRIMARY]
GO
GRANT SELECT ON  [dbo].[ca_group_ace] TO [ca_itrm_group]
GRANT INSERT ON  [dbo].[ca_group_ace] TO [ca_itrm_group]
GRANT DELETE ON  [dbo].[ca_group_ace] TO [ca_itrm_group]
GRANT UPDATE ON  [dbo].[ca_group_ace] TO [ca_itrm_group]
GRANT SELECT ON  [dbo].[ca_group_ace] TO [ca_itrm_group_ams]
GRANT SELECT ON  [dbo].[ca_group_ace] TO [dms_backup_group]
GRANT INSERT ON  [dbo].[ca_group_ace] TO [dms_backup_group]
GRANT DELETE ON  [dbo].[ca_group_ace] TO [dms_backup_group]
GRANT UPDATE ON  [dbo].[ca_group_ace] TO [dms_backup_group]
GRANT SELECT ON  [dbo].[ca_group_ace] TO [regadmin]
GRANT SELECT ON  [dbo].[ca_group_ace] TO [upmuser_group]
GO