CREATE TABLE [dbo].[ca_discovered_hardware_ext_sys]
(
[dis_hw_uuid] [binary] (16) NOT NULL,
[external_sys_name] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[discovery_id_1] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[discovery_id_2] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[discovery_id_3] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[discovery_id_4] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[discovery_id_5] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[loader_processed_date] [int] NULL,
[discovery_changed_date] [int] NULL,
[creation_user] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[creation_date] [int] NULL,
[last_update_user] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[last_update_date] [int] NULL,
[version_number] [int] NULL CONSTRAINT [DF__ca_discov__versi__1ABEEF0B] DEFAULT ((0))
) ON [PRIMARY]
GO

/****** Object:  Trigger dbo.t_d_ca_discovered_hw_ext_sys    Script Date: 12/1/2005 11:51:52 PM ******/



CREATE TRIGGER t_d_ca_discovered_hw_ext_sys ON ca_discovered_hardware_ext_sys
FOR DELETE AS

    /* Check whether any Reconciliation tasks are tracking discovery deletions. */
    /* If none are, then don't continue.                                        */
        
    if (SELECT count(*) FROM arg_reconcile_task WHERE discovery_deleted_indicator <> 0) = 0
        return

    declare @own_resource_uuid binary(16)
    declare @max_id integer
    declare @mod_rec_count integer
    declare @external_sys_name varchar(30)
    declare @date_in_seconds integer
    declare @dis_hw_uuid binary(16)
    declare @update_user varchar(30)
    declare @host_name nvarchar(255)
    declare @serial_number nvarchar(64)
    declare @vendor_name nvarchar(255)
    
    set @mod_rec_count = 0
    set @date_in_seconds = 0
    set @update_user = 't_d_ca_discovered_hw_ext_sys'
    
    declare delete_list cursor for select dis_hw_uuid, external_sys_name from deleted
           
    open delete_list
        
    fetch next from delete_list into @dis_hw_uuid, @external_sys_name
        
    while @@fetch_status = 0
        begin
    
            /* Check whether the discovered asset record is linked to an owned resource record. */
        /* If not, skip this record.                                                        */
        
        select @own_resource_uuid = (SELECT oa.own_resource_uuid
                                  FROM ca_asset_source ds
                             INNER JOIN ca_asset_source os
                                 ON os.logical_asset_uuid = ds.logical_asset_uuid and os.subschema_id = 1
                             INNER JOIN ca_owned_resource oa
                                 ON oa.asset_source_uuid = os.asset_source_uuid
                               WHERE ds.asset_source_uuid = @dis_hw_uuid)
                
        if @own_resource_uuid is null
            goto Skip_Record
            
        /* Check whether an arg_reconcile_modification record already exists for this asset. */
        /* If so, skip this record.                                                          */
        
        select @mod_rec_count = (SELECT count(*)
                       FROM arg_reconcile_modification
                      WHERE dis_hw_uuid = @dis_hw_uuid)
           
        if @mod_rec_count > 0
            goto Skip_Record
      
        /* Get information from ca_discovered_hardware record to pass to arg_reconcile_modification. */
    
        SELECT @host_name = host_name, @serial_number = serial_number, @vendor_name = vendor_name
          FROM ca_discovered_hardware
         WHERE dis_hw_uuid = @dis_hw_uuid
    
        /* Set up creation_date, last_update_date  */
        
            set @date_in_seconds = (SELECT DATEDIFF(second, cast('1/1/1970' as datetime), getdate()))
        
        /* Get value to put into reconcile_modification_id. */
    
        select @max_id = (SELECT max(reconcile_modification_id)
                    FROM arg_reconcile_modification
                   WHERE sys_name = @external_sys_name)
    
        if @max_id is null
            set @max_id = 0
        else
            set @max_id = @max_id + 1
      
        /* Create the arg_reconcile_modification record. */
            
        INSERT INTO arg_reconcile_modification
               (sys_name,
                reconcile_modification_id,
                reconcile_action,
                own_resource_uuid,
                dis_hw_uuid,
                discovery_asset_name,
                discovery_asset_serial_num,
                discovery_asset_sys_vendor,
                creation_user,
                creation_date,
                last_update_user,
                last_update_date,
                version_number)
            
            VALUES
               (@external_sys_name,
                @max_id,
                3,
                @own_resource_uuid,
                @dis_hw_uuid,
                @host_name,
                @serial_number,
                @vendor_name,
                @update_user,
                @date_in_seconds,
                @update_user,
                @date_in_seconds,
                0)
                
        Skip_Record:
    
            fetch next from delete_list into @dis_hw_uuid, @external_sys_name
            
        end
        
    close delete_list
    
    deallocate delete_list  


GO
ALTER TABLE [dbo].[ca_discovered_hardware_ext_sys] ADD CONSTRAINT [XPKca_discovered_hardware_ext_sys] PRIMARY KEY CLUSTERED ([dis_hw_uuid]) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [ca_disc_hard_ext_sys_idx_01] ON [dbo].[ca_discovered_hardware_ext_sys] ([external_sys_name], [discovery_id_1], [discovery_id_2], [discovery_id_3], [discovery_id_4], [discovery_id_5]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ca_discovered_hardware_ext_sys] ADD CONSTRAINT [$ca_di_r000002b500000000] FOREIGN KEY ([dis_hw_uuid]) REFERENCES [dbo].[ca_discovered_hardware] ([dis_hw_uuid])
GO
GRANT SELECT ON  [dbo].[ca_discovered_hardware_ext_sys] TO [ams_group]
GRANT SELECT ON  [dbo].[ca_discovered_hardware_ext_sys] TO [amsgroup]
GRANT SELECT ON  [dbo].[ca_discovered_hardware_ext_sys] TO [ca_itrm_group]
GRANT INSERT ON  [dbo].[ca_discovered_hardware_ext_sys] TO [ca_itrm_group]
GRANT DELETE ON  [dbo].[ca_discovered_hardware_ext_sys] TO [ca_itrm_group]
GRANT UPDATE ON  [dbo].[ca_discovered_hardware_ext_sys] TO [ca_itrm_group]
GRANT SELECT ON  [dbo].[ca_discovered_hardware_ext_sys] TO [ca_itrm_group_ams]
GRANT SELECT ON  [dbo].[ca_discovered_hardware_ext_sys] TO [regadmin]
GRANT SELECT ON  [dbo].[ca_discovered_hardware_ext_sys] TO [swcmadmin]
GRANT INSERT ON  [dbo].[ca_discovered_hardware_ext_sys] TO [swcmadmin]
GRANT DELETE ON  [dbo].[ca_discovered_hardware_ext_sys] TO [swcmadmin]
GRANT UPDATE ON  [dbo].[ca_discovered_hardware_ext_sys] TO [swcmadmin]
GRANT SELECT ON  [dbo].[ca_discovered_hardware_ext_sys] TO [uapmadmin]
GRANT INSERT ON  [dbo].[ca_discovered_hardware_ext_sys] TO [uapmadmin]
GRANT DELETE ON  [dbo].[ca_discovered_hardware_ext_sys] TO [uapmadmin]
GRANT UPDATE ON  [dbo].[ca_discovered_hardware_ext_sys] TO [uapmadmin]
GRANT SELECT ON  [dbo].[ca_discovered_hardware_ext_sys] TO [uapmadmin_group]
GRANT INSERT ON  [dbo].[ca_discovered_hardware_ext_sys] TO [uapmadmin_group]
GRANT DELETE ON  [dbo].[ca_discovered_hardware_ext_sys] TO [uapmadmin_group]
GRANT UPDATE ON  [dbo].[ca_discovered_hardware_ext_sys] TO [uapmadmin_group]
GRANT SELECT ON  [dbo].[ca_discovered_hardware_ext_sys] TO [uapmbatch]
GRANT INSERT ON  [dbo].[ca_discovered_hardware_ext_sys] TO [uapmbatch]
GRANT DELETE ON  [dbo].[ca_discovered_hardware_ext_sys] TO [uapmbatch]
GRANT UPDATE ON  [dbo].[ca_discovered_hardware_ext_sys] TO [uapmbatch]
GRANT SELECT ON  [dbo].[ca_discovered_hardware_ext_sys] TO [uapmbatch_group]
GRANT INSERT ON  [dbo].[ca_discovered_hardware_ext_sys] TO [uapmbatch_group]
GRANT DELETE ON  [dbo].[ca_discovered_hardware_ext_sys] TO [uapmbatch_group]
GRANT UPDATE ON  [dbo].[ca_discovered_hardware_ext_sys] TO [uapmbatch_group]
GRANT SELECT ON  [dbo].[ca_discovered_hardware_ext_sys] TO [uapmreporting]
GRANT SELECT ON  [dbo].[ca_discovered_hardware_ext_sys] TO [uapmreporting_group]
GO