CREATE TABLE [dbo].[ca_discovered_user]
(
[user_uuid] [binary] (16) NOT NULL,
[label] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[user_name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[domain_uuid] [binary] (16) NOT NULL,
[creation_user] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[creation_date] [int] NULL,
[last_update_user] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[last_update_date] [int] NULL,
[version_number] [int] NULL CONSTRAINT [DF__ca_discov__versi__253C7D7E] DEFAULT ((0)),
[auto_rep_version] [timestamp] NULL,
[uri] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_type] [int] NULL,
[usage_list] [binary] (32) NULL,
[directory_url] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL
) ON [PRIMARY]
GO
/****** Object:  Trigger dbo.r_d_discovered_user    Script Date: 12/1/2005 11:51:18 PM ******/



/*
************************************************
* delete ca_discovered user
*/

CREATE trigger r_d_discovered_user
    on ca_discovered_user
    after delete
as
begin

    declare @old_object_uuid as binary(16);
    declare cur_cl cursor for
        select user_uuid from deleted;

    open cur_cl;
    fetch cur_cl into @old_object_uuid; -- get first
    while @@fetch_status = 0
    begin
    
        /* no more part of ingres mdb */
        /* execute  p_integrity_d_agent @old_object_uuid ; */
        execute p_d_discovered_user @old_object_uuid;
        fetch cur_cl into @old_object_uuid;

     end; -- while
     close cur_cl;
     deallocate cur_cl;    

end
GO
/*
*******************************************
  trigger if a new user was created
*/
CREATE trigger r_i_new_user
on ca_discovered_user
instead of insert
as
begin
  declare @obj_uuid binary(16);
  declare @_olduri nvarchar(255);

  declare @_newuri nvarchar(255);
  declare @_dom_uuid binary(16);
  declare @return integer;

  set nocount on;

  select @_olduri= null;
  select @_newuri= (select uri from inserted);
  select @_dom_uuid = (select domain_uuid from inserted);


  -- check of right uri
  execute @return = p_i_dis_user_uri_check @_olduri, @_newuri, @_dom_uuid;
  if (    @return = 0 )
  begin


      -- insert into ca_discovered_user
      insert into ca_discovered_user(user_uuid, label, user_name, domain_uuid, creation_user, creation_date,
                last_update_user, last_update_date, version_number, uri, user_type, usage_list, directory_url)
        select user_uuid, label, user_name, domain_uuid, creation_user, creation_date,
                last_update_user, last_update_date, version_number, uri, user_type, usage_list, directory_url
        from inserted;

      -- do so updates on ca_object_ace
      select @obj_uuid = (select user_uuid from inserted);
      execute  proc_i_new_so_user @obj_uuid;
  end;

end;
GO
CREATE trigger r_u_dis_user_uri_check
on ca_discovered_user
after update
as
declare @_olduri nvarchar(255);
declare @_newuri nvarchar(255);
declare @_dom_uuid binary(16);

begin
if update(uri)
    BEGIN
        DECLARE mycur CURSOR LOCAL
        FOR select deleted.uri, inserted.uri, deleted.domain_uuid
        from deleted, inserted
        where deleted.user_uuid=inserted.user_uuid /* itrac 12093*/
                and deleted.domain_uuid=inserted.domain_uuid /* itrac 12093*/

        OPEN mycur
        FETCH NEXT FROM mycur INTO @_olduri,@_newuri,@_dom_uuid;
        WHILE @@FETCH_STATUS = 0
            BEGIN

                execute p_iu_dis_user_uri_check @_olduri,@_newuri,@_dom_uuid;
                FETCH NEXT FROM mycur INTO @_olduri,@_newuri,@_dom_uuid;

            END

            CLOSE mycur
            DEALLOCATE mycur

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


/*
***********************************************
trigger on update on ca_discovered_user
*/
CREATE trigger r_upd_verno_dis_user
        on ca_discovered_user
        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
/****** Object:  Trigger dbo.rule_d_so_removed_user    Script Date: 12/1/2005 11:51:36 PM ******/


/*
*******************************************
  trigger if a user is removed user
*/


CREATE trigger rule_d_so_removed_user
on ca_discovered_user
after delete
as
begin
    declare @old_object_uuid as binary(16);
    declare cur_cl cursor for
        select user_uuid from deleted;

    open cur_cl;
    fetch cur_cl into @old_object_uuid; -- get first
    while @@fetch_status = 0
    begin
    
          execute  proc_d_so_removed_user @old_object_uuid ;
        fetch cur_cl into @old_object_uuid;

     end; -- while
     close cur_cl;
     deallocate cur_cl;


end;
GO
ALTER TABLE [dbo].[ca_discovered_user] ADD CONSTRAINT [XPKca_discovered_user] PRIMARY KEY CLUSTERED ([user_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_disc_user_idx_02] ON [dbo].[ca_discovered_user] ([last_update_date]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_discovered_user_idx_03] ON [dbo].[ca_discovered_user] ([uri], [domain_uuid]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ca_discovered_user] ADD CONSTRAINT [$ca_di_r00000a2f00000000] FOREIGN KEY ([domain_uuid]) REFERENCES [dbo].[ca_n_tier] ([domain_uuid])
GO
GRANT SELECT ON  [dbo].[ca_discovered_user] TO [aiadmin]
GRANT SELECT ON  [dbo].[ca_discovered_user] TO [ca_itrm_group]
GRANT INSERT ON  [dbo].[ca_discovered_user] TO [ca_itrm_group]
GRANT DELETE ON  [dbo].[ca_discovered_user] TO [ca_itrm_group]
GRANT UPDATE ON  [dbo].[ca_discovered_user] TO [ca_itrm_group]
GRANT SELECT ON  [dbo].[ca_discovered_user] TO [ca_itrm_group_ams]
GRANT SELECT ON  [dbo].[ca_discovered_user] TO [dms_backup_group]
GRANT INSERT ON  [dbo].[ca_discovered_user] TO [dms_backup_group]
GRANT DELETE ON  [dbo].[ca_discovered_user] TO [dms_backup_group]
GRANT UPDATE ON  [dbo].[ca_discovered_user] TO [dms_backup_group]
GRANT SELECT ON  [dbo].[ca_discovered_user] TO [regadmin]
GRANT SELECT ON  [dbo].[ca_discovered_user] TO [swcmadmin]
GRANT INSERT ON  [dbo].[ca_discovered_user] TO [swcmadmin]
GRANT DELETE ON  [dbo].[ca_discovered_user] TO [swcmadmin]
GRANT UPDATE ON  [dbo].[ca_discovered_user] TO [swcmadmin]
GRANT SELECT ON  [dbo].[ca_discovered_user] TO [uapmadmin]
GRANT INSERT ON  [dbo].[ca_discovered_user] TO [uapmadmin]
GRANT DELETE ON  [dbo].[ca_discovered_user] TO [uapmadmin]
GRANT UPDATE ON  [dbo].[ca_discovered_user] TO [uapmadmin]
GRANT SELECT ON  [dbo].[ca_discovered_user] TO [uapmadmin_group]
GRANT INSERT ON  [dbo].[ca_discovered_user] TO [uapmadmin_group]
GRANT DELETE ON  [dbo].[ca_discovered_user] TO [uapmadmin_group]
GRANT UPDATE ON  [dbo].[ca_discovered_user] TO [uapmadmin_group]
GRANT SELECT ON  [dbo].[ca_discovered_user] TO [uapmbatch]
GRANT INSERT ON  [dbo].[ca_discovered_user] TO [uapmbatch]
GRANT DELETE ON  [dbo].[ca_discovered_user] TO [uapmbatch]
GRANT UPDATE ON  [dbo].[ca_discovered_user] TO [uapmbatch]
GRANT SELECT ON  [dbo].[ca_discovered_user] TO [uapmbatch_group]
GRANT INSERT ON  [dbo].[ca_discovered_user] TO [uapmbatch_group]
GRANT DELETE ON  [dbo].[ca_discovered_user] TO [uapmbatch_group]
GRANT UPDATE ON  [dbo].[ca_discovered_user] TO [uapmbatch_group]
GRANT SELECT ON  [dbo].[ca_discovered_user] TO [uapmreporting]
GRANT SELECT ON  [dbo].[ca_discovered_user] TO [uapmreporting_group]
GRANT SELECT ON  [dbo].[ca_discovered_user] TO [upmuser_group]
GO