CREATE TABLE [dbo].[ca_n_tier]
(
[domain_uuid] [binary] (16) NOT NULL,
[parent_domain_uuid] [binary] (16) NULL,
[label] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[db_host_name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[db_server] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[db_type] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[db_name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[db_user_name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[db_password] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[db_instance] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[creation_user] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[creation_date] [int] 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_n_tier__versi__316D4A39] DEFAULT ((0)),
[dis_hw_uuid] [binary] (16) NULL,
[domain_type] [int] NULL,
[domain_id] [int] NULL,
[usage_list] [binary] (32) NULL,
[contact_information] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL
) ON [PRIMARY]
GO
/****** Object:  Trigger dbo.r_u_ca_n_tier    Script Date: 12/1/2005 11:51:26 PM ******/


/*
**************************************************
trigger on
after update (label, description) on ca_n_tier
used to track changes / handover of changes made
for the address book server node entry
**************************************************
*/

CREATE trigger r_u_ca_n_tier
    on ca_n_tier
    after update
as    
    if update(label) or update(description)
begin     

    declare @domain_uuid binary(16);
    declare @label nvarchar(255);
    declare @description nvarchar(255);

    declare cursor_ca_n_tier cursor local for
        select domain_uuid, label, description from inserted
    
    open cursor_ca_n_tier;
    fetch next from cursor_ca_n_tier into @domain_uuid, @label, @description; -- get first
    while @@fetch_status = 0
    begin
    
         execute p_urc_ab_ca_n_tier_updated @domain_uuid, @label, @description;
        fetch next from cursor_ca_n_tier into @domain_uuid, @label, @description;

    end; -- while
    close cursor_ca_n_tier;
    deallocate cursor_ca_n_tier;

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


/*
***********************************************
trigger on update on ca_n_tier
*/
CREATE trigger r_upd_verno_n_tier
        on ca_n_tier
        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_domain    Script Date: 12/1/2005 11:51:34 PM ******/



/*
*********************************************
trigger fro removing a domain
*/
CREATE trigger rule_d_so_removed_domain
on ca_n_tier
after delete
as    
begin
   declare @ddis_hw_uuid binary(16);
  
    DECLARE mycur CURSOR
        FOR select domain_uuid from deleted

    OPEN mycur
    FETCH NEXT FROM mycur INTO @ddis_hw_uuid
    WHILE @@FETCH_STATUS = 0
    BEGIN

        execute  proc_d_so_removed_object @ddis_hw_uuid
        FETCH NEXT FROM mycur INTO @ddis_hw_uuid;

    END

    CLOSE mycur
    DEALLOCATE mycur

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


/*
*********************************************************
*********************************************************
* Procedures and rules to manage domain objects
*********************************************************
*********************************************************
*/


/*
*********************************************
trigger for creating a new domain represented
by an entryi in the ca_n_tier table
*/

CREATE trigger rule_i_new_so_domain
on ca_n_tier
after insert
as
begin
  declare @_obj_uuid binary(16);
  declare @_clsid integer;
  declare @_uri nvarchar(255);

  select @_obj_uuid = (select domain_uuid from inserted);
  select @_clsid = 1006;
  select @_uri = (select creation_user from inserted);
    
execute  proc_i_new_so_object @_obj_uuid, @_clsid, @_uri;

end;
GO
/****** Object:  Trigger dbo.usd_trg_d_ca_n_tier_tbl_ver    Script Date: 12/1/2005 11:51:55 PM ******/
/* Rule on ca_n_tier, on insert, update, delete change USD table version */
CREATE trigger usd_trg_d_ca_n_tier_tbl_ver
on ca_n_tier
for delete as
begin
    exec usd_proc_u_tbl_ver 2, -1, -1, -1
end
GO
/****** Object:  Trigger dbo.usd_trg_d_domain_usd_rel    Script Date: 12/1/2005 11:51:55 PM ******/
CREATE trigger usd_trg_d_domain_usd_rel
on ca_n_tier
for delete as
begin
    -- A check will be made by the CO API before starting to unlink the domain, so do not do it here
    --select count(objectid) from usd_cc where boundto = <domain_uuid>

    -- Go ahead and delete

    -- Delete distributed procedure and software template to the local domain object
    delete from usd_distap
    from usd_distsw dsw, deleted d
    where dsw.localsite = d.domain_uuid
    and distsw = dsw.objectid
   
    delete from usd_distsw
    from deleted d
    where localsite = d.domain_uuid
    
    delete from usd_disttempl
    from deleted d
    where domain = d.domain_uuid

    -- Delete fetch item orders and fetched items from domain object
    delete from usd_fio
    from usd_fitem fi, deleted d
    where fi.localsite = d.domain_uuid
    and usd_fio.objectid = fi.fio

    delete from usd_fitem
    from deleted d
    where localsite = d.domain_uuid

    /* Delete relation from carrier to domain object (local domain) */
    update usd_carrier set version = version + 1,
    boundto = 0x00000000000000000000000000000000
    from deleted d
    where boundto = d.domain_uuid

    /* Make sure we update the class version for the above operations */
    update usd_class_version set modify_version = modify_version + 1
    where name = 'distap'
    or name = 'distsw'
    or name = 'disttempl'
    or name = 'fio'
    or name = 'fitem'
end
GO
/****** Object:  Trigger dbo.usd_trg_u_ca_n_tier_tbl_ver    Script Date: 12/1/2005 11:51:58 PM ******/
/* Rule on ca_n_tier, on insert, update, delete change USD table version */
CREATE trigger usd_trg_u_ca_n_tier_tbl_ver
on ca_n_tier
for insert, update as
begin
    if (update(version_number) or update(creation_user) or update(label) or update(contact_information) or update(creation_date) or update(last_update_date) or update(description))
    begin
        exec usd_proc_u_tbl_ver 2, -1, -1, -1
    end
end
GO
ALTER TABLE [dbo].[ca_n_tier] ADD CONSTRAINT [XPKca_n_tier] PRIMARY KEY CLUSTERED ([domain_uuid]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ca_n_tier] ADD CONSTRAINT [$ca_n__r000008db00000000] FOREIGN KEY ([dis_hw_uuid]) REFERENCES [dbo].[ca_discovered_hardware] ([dis_hw_uuid])
GO
ALTER TABLE [dbo].[ca_n_tier] ADD CONSTRAINT [$ca_n__r000008e500000000] FOREIGN KEY ([dis_hw_uuid]) REFERENCES [dbo].[ca_asset] ([asset_uuid])
GO
ALTER TABLE [dbo].[ca_n_tier] ADD CONSTRAINT [$ca_n__r000008ef00000000] FOREIGN KEY ([parent_domain_uuid]) REFERENCES [dbo].[ca_n_tier] ([domain_uuid])
GO
GRANT SELECT ON  [dbo].[ca_n_tier] TO [ams_group]
GRANT SELECT ON  [dbo].[ca_n_tier] TO [amsgroup]
GRANT SELECT ON  [dbo].[ca_n_tier] TO [ca_itrm_group]
GRANT INSERT ON  [dbo].[ca_n_tier] TO [ca_itrm_group]
GRANT DELETE ON  [dbo].[ca_n_tier] TO [ca_itrm_group]
GRANT UPDATE ON  [dbo].[ca_n_tier] TO [ca_itrm_group]
GRANT SELECT ON  [dbo].[ca_n_tier] TO [ca_itrm_group_ams]
GRANT SELECT ON  [dbo].[ca_n_tier] TO [regadmin]
GRANT SELECT ON  [dbo].[ca_n_tier] TO [swcmadmin]
GRANT INSERT ON  [dbo].[ca_n_tier] TO [swcmadmin]
GRANT DELETE ON  [dbo].[ca_n_tier] TO [swcmadmin]
GRANT UPDATE ON  [dbo].[ca_n_tier] TO [swcmadmin]
GRANT SELECT ON  [dbo].[ca_n_tier] TO [uapmadmin]
GRANT INSERT ON  [dbo].[ca_n_tier] TO [uapmadmin]
GRANT DELETE ON  [dbo].[ca_n_tier] TO [uapmadmin]
GRANT UPDATE ON  [dbo].[ca_n_tier] TO [uapmadmin]
GRANT SELECT ON  [dbo].[ca_n_tier] TO [uapmadmin_group]
GRANT INSERT ON  [dbo].[ca_n_tier] TO [uapmadmin_group]
GRANT DELETE ON  [dbo].[ca_n_tier] TO [uapmadmin_group]
GRANT UPDATE ON  [dbo].[ca_n_tier] TO [uapmadmin_group]
GRANT SELECT ON  [dbo].[ca_n_tier] TO [uapmbatch]
GRANT INSERT ON  [dbo].[ca_n_tier] TO [uapmbatch]
GRANT DELETE ON  [dbo].[ca_n_tier] TO [uapmbatch]
GRANT UPDATE ON  [dbo].[ca_n_tier] TO [uapmbatch]
GRANT SELECT ON  [dbo].[ca_n_tier] TO [uapmbatch_group]
GRANT INSERT ON  [dbo].[ca_n_tier] TO [uapmbatch_group]
GRANT DELETE ON  [dbo].[ca_n_tier] TO [uapmbatch_group]
GRANT UPDATE ON  [dbo].[ca_n_tier] TO [uapmbatch_group]
GRANT SELECT ON  [dbo].[ca_n_tier] TO [uapmreporting]
GRANT SELECT ON  [dbo].[ca_n_tier] TO [uapmreporting_group]
GRANT SELECT ON  [dbo].[ca_n_tier] TO [upmadmin_group]
GRANT INSERT ON  [dbo].[ca_n_tier] TO [upmadmin_group]
GRANT DELETE ON  [dbo].[ca_n_tier] TO [upmadmin_group]
GRANT UPDATE ON  [dbo].[ca_n_tier] TO [upmadmin_group]
GRANT SELECT ON  [dbo].[ca_n_tier] TO [upmuser_group]
GRANT INSERT ON  [dbo].[ca_n_tier] TO [upmuser_group]
GRANT DELETE ON  [dbo].[ca_n_tier] TO [upmuser_group]
GRANT UPDATE ON  [dbo].[ca_n_tier] TO [upmuser_group]
GO