CREATE TABLE [dbo].[tng_managedobject]
(
[uuid] [binary] (16) NOT NULL,
[name] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
[label] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[address] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[address_type] [int] NULL,
[class_name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
[interface_type] [int] NULL,
[autoarrange_type] [int] NULL,
[hidden] [tinyint] NULL,
[propagate_status] [tinyint] NULL,
[status_no] [int] NULL,
[severity] [int] NULL,
[tng_delete_flag] [int] NULL,
[posted] [tinyint] NULL,
[acknowledge] [tinyint] NULL,
[ip_address_hex] [binary] (4) NULL,
[mac_address] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[subnet_mask] [binary] (4) NULL,
[date_ins] [datetime] NULL CONSTRAINT [DF__tng_manag__date___74AF2013] DEFAULT ('now'),
[date_modify] [datetime] NULL CONSTRAINT [DF__tng_manag__date___75A3444C] DEFAULT ('now'),
[alarmset_name] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[code_page] [int] NULL,
[admin_status] [int] NULL,
[dsm_server] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[propagated_status_no] [int] NULL,
[propagated_sev] [int] NULL,
[dsm_address] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[license_machine_type] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[create_bpv] [tinyint] NULL,
[override_imagelarge] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[override_imagesmall] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[override_imagedecal] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[override_imagetintbool] [tinyint] NULL,
[override_model] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[background_image] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[weight] [int] NULL,
[reserved] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[weighted_severity] [int] NULL,
[max_sev] [int] NULL,
[user_reclass] [int] NULL,
[asset_uuid] [binary] (16) NULL,
[source_repository] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[dnsname] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[last_seen_time] [datetime] NULL,
[amsserver] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[emserver] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[ipv6_address] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ipv6_address_hex] [binary] (16) NULL,
[common_discovery_name] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[performance_report] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO

/* This tng_td_managedobject trigger is here as a temporary fix only.
    This is a fix for deletion problems on non-English OS such as Chinese.
    Deleting a managed object would generate a database native error
    because the collation sequence of the wtdbqueryinfo table does
    not match collation sequence of managedobject table. The solution
    is to dump all objects to be deleted in new table, then the new
    table collation will match wtdbqueryinfo table because they both
    use the MDB collation default. Creating a temporary table is not
    a solution since it is created in the 'tempdb' database which
    has a different collation thatn the wtdbqueryinfo table. But the
    table is temporary in the logical sense in that it will be
    deleted after the trigger is completed. This trigger is one part
    of the fix for deletion native error. The other part is to
    create the wtdbqueryinfo table with no specific collation sequence
    since they are different for localized versions of Windows.
    When the MDB change request for the wtdbqueryinfo is applied to
    MDB, these 2 fixes (trigger + table) will deleted from this script
    since they are no longer necessary.
*/


create trigger tng_td_managedobject on tng_managedobject for delete as
set nocount on


   if not exists (select * from deleted )
     return
   
   insert into tng_change_history ( operation, class_name, object_id1, portnum1,  portnum2, timestamp, user_name )
                          select         'd',   class_name,  uuid,         0,         0 , getdate(),Host_Name()
                          from deleted



/* Below two deletes are used to delete the links between the inclusions for that parent*/
delete from tng_link
     from tng_link a,tng_inclusion b,deleted c
         where (a.source_uuid = b.child_uuid
         and b.parent_uuid = c.uuid
         and a.source_seq  = b.child_sequence_no)
         
     delete from tng_link
         from tng_link a,tng_inclusion b,deleted c
         where (a.dest_uuid = b.child_uuid
         and b.parent_uuid = c.uuid
         and a.dest_seq =b.child_sequence_no)


   delete from tng_inclusion
         where parent_uuid in (select uuid from deleted ) or
                 child_uuid in (select uuid from deleted )

/*1/30/96 since removed tng_dependency table and class Dependency    
   delete from tng_dependency
         where dependent_uuid in (select uuid from deleted ) or
               target_uuid    in (select uuid from deleted )
*/
   
/*remove records for corresponding common_ancestor_class along with others*/
   
delete from tng_link
         where source_uuid in (select uuid from deleted ) or
                 dest_uuid in (select uuid from deleted ) or
         common_ancestor_uuid in (select uuid from deleted)

Begin tran tran1
Create table dbo.temp1 (name varchar(255))
Commit tran tran1

insert into dbo.temp1 select name from deleted

/* This is exclusively for dynbpv objects. We should remove them from dbpvqueryinfo.*/
delete from dbo.wtdbpvqueryinfo where query_name in (select name from dbo.temp1)
drop table dbo.temp1

if @@error != 0
         RAISERROR ('Deletion from DBPVQueryInfo table failed',16,1)

return



GO
SET QUOTED_IDENTIFIER ON
GO
/****** Object:  Trigger dbo.tng_ti_managedobject    Script Date: 12/1/2005 11:50:43 PM ******/

CREATE TRIGGER tng_ti_managedobject on tng_managedobject FOR INSERT AS
set nocount on
declare @timestamp datetime
declare @Max_Sev int
declare @sev int
declare @prop_sev int

  if not exists (select * from inserted )
    return
  insert into tng_change_history ( operation, class_name, object_id1, portnum1,  portnum2,    timestamp,user_name )
            select         'i',   class_name,  uuid,0,  0, getdate(), Host_Name()
                         from inserted
  
  select @timestamp=getdate()
  
  select @sev = severity from inserted
  select @prop_sev = propagated_sev from inserted
    if( @sev > @prop_sev)
        select @Max_Sev = @sev
    else
        select @Max_Sev = @prop_sev        

  /*5/2/96 force local time(SQL Server time to install time)*/
  update tng_managedobject set date_ins=@timestamp,weighted_severity = i.weight * i.severity,max_sev =@Max_Sev
      from inserted i, tng_managedobject m     
        where m.uuid=i.uuid and i.date_ins = NULL  /*10/31/96*/



return
GO

  create trigger dbo.tng_tu_managedobject on dbo.tng_managedobject for update as
  declare @uuid_bin UUID       
  declare @uuid int
  declare @class_id int
  declare @status_no int
  declare @cur_time datetime
  declare @severity int
  declare @propagate_status char(1)
  declare @acknowledge tinyint
  declare @timestamp datetime
  declare @IsInsertChangeHistory int
  declare @prop_sev int
  declare @sev int
  declare @weighted_sev int
  declare @Max_Sev int
  declare @DeltaGMT int
  declare @GMTDate datetime
declare @propagate_status_old int
declare @propagate_status_new int
  set nocount on
    
    if not exists (select * from deleted )
       return
   
  select @weighted_sev = -1
  select @Max_Sev = -1
  
      
     if( update(severity) or update(status_no))
       if( ( 1 in (select acknowledge from deleted)) and ( 1 in (select acknowledge from inserted)))
       begin
         select 80001    
         raiserror 80001 'Update severity or status_no failed Since one of acknowledges is on(1). '
         rollback transaction
         return
       end
     
     if( update(class_name) or update(uuid))
     begin
       select 80002    
       raiserror 80002 'Do not allow to update class name or uuid of an object, the transaction failed. '
       rollback transaction
       return
     end
   
     select @timestamp=getdate(),  @IsInsertChangeHistory = 0
     /*5/2/96*/
   if( update( date_modify) )
     update tng_managedobject set date_modify=@timestamp
         from inserted i, deleted d, tng_managedobject m     
           where m.uuid=i.uuid and i.uuid=d.uuid and
                   i.date_modify <> d.date_modify and i.date_modify = NULL /*10/31/96*/
   
   else
    update tng_managedobject set date_modify=@timestamp
         from inserted i, tng_managedobject m     
           where m.uuid=i.uuid
                   
   if( update( propagated_sev) )
   Begin
       
         insert into tng_prop_status_history( class_name,  record_uuid,  status_no,  severity,   timestamp,user_name)
                                    select  i.class_name,       i.uuid,i.propagated_status_no, i.propagated_sev, @timestamp,'ps'
                                    from  inserted i    
       select @prop_sev = propagated_sev from inserted
         
       
         if( not update(severity))
           select @sev = m.severity ,@weighted_sev = isNull(m.weighted_severity ,0)from tng_managedobject m, inserted i
               where m.uuid = i.uuid
         else        
           select @sev = severity from inserted
       
       
           if (@sev > @prop_sev)
               select @Max_Sev = @sev
           else
               select @Max_Sev = @prop_sev
           
           
   end
   /* 2/13/96 update whatever except uuid,class_name,status_no and severity, we log in tng_change_history...       */
     if ( update(label)          or update(address)          or update(name)             or update(address_type) or
          update(interface_type) or update(acknowledge)      or update(hidden)       or
          update(posted)         or update(ip_address_hex)   or update(mac_address)      or update(subnet_mask)  or
          update(alarmset_name)  or update(autoarrange_type) or /*or update(date_ins)         or update(date_modify) */
          update(DSM_Server)     or update(admin_status)     or update(DSM_Address)      or update(weight) or
          update(override_imagelarge) or
          update(override_imagelarge) or
          update(override_imagesmall) or
          update(override_imagedecal) or
          update(override_imagetintbool) or
          update(override_model)      or
          update(background_image) or update(ipv6_address)
          )
     begin
     
       insert into tng_change_history ( operation, class_name,  object_id1, portnum1,  portnum2, timestamp )
                              select          'u',    class_name,  uuid,          0,           0    , @timestamp
                              from deleted d
   
       select @IsInsertChangeHistory = 1
   
       /* Added for the weight_severity changes*/
       /* if weight changes  weight_sev changes*/
           if( not update(severity) or not update(status_no))
            begin
               select @weighted_sev = (m.severity* i.weight) from tng_managedobject m, inserted i
                   where m.uuid = i.uuid
                   and m.class_name = i.class_name
               
               select @sev = m.severity from tng_managedobject m, inserted i where m.uuid=i.uuid
            end
   
           else
            begin
               select @weighted_sev = (i.severity* i.weight) from inserted i
   
               select @sev = i.severity from inserted i
   
   
           end
   
           select @prop_sev = m.propagated_sev from tng_managedobject m, inserted i where m.uuid = i.uuid
   
           if (@sev > @prop_sev)
               select @Max_Sev = @sev
           else
               select @Max_Sev = @prop_sev
   
   
     end
   
   if update(propagate_status)
   begin
     if exists (select * from inserted i, tng_alarmset_entry a
                       where i.alarmset_name=a.alarmset_name and
                                 a.status_no=i.status_no  and
                             i.propagate_status<>a.propagate_status)
               begin

                 
                     insert into tng_change_history ( operation, class_name,  object_id1, portnum1,  portnum2, timestamp )
                              select          'u',    class_name,  uuid,          0,           0    , @timestamp
                              from deleted d
                       
               end
   end
   
     if( update(status_no) or update(severity) or update(alarmset_name))
     begin
       /*insert 1st update Info(deleted) in tng_status_history */
   /* 03/20/96 ignore the 1st status of an updated object
       insert into tng_status_history( class_name,  record_uuid, status_no, severity, timestamp)
                               select     d.class_name, d.uuid,   d.status_no, d.severity, @timestamp
                               from  deleted d        
                               where     (d.uuid not in (select record_uuid from tng_status_history))  
   */
                                      /*2/14/96    and    d.status_no=s.status_no */
       if(update(severity))
       begin
           select @weighted_sev = (i.severity* m.weight) from tng_managedobject m, inserted i
                   where m.uuid = i.uuid
                   and m.class_name = i.class_name
   
               select @sev = i.severity from inserted i
   
           select @prop_sev = m.propagated_sev from tng_managedobject m, inserted i where m.uuid = i.uuid
   
           if (@sev > @prop_sev)
               select @Max_Sev = @sev
           else
               select @Max_Sev = @prop_sev
       END
   
       if(update(severity)) /*05/28/96 if user update severity, we accept the severity value from user*/
       begin
         insert into tng_status_history( class_name,  record_uuid, status_no, severity, timestamp,user_name)
                               select     i.class_name, i.uuid,   i.status_no, i.severity, @timestamp,'severity'
                               from  inserted i
       end
   
       /*since status_no updated, we update severity and propagate_status to keep consistency*/
       if( (update(status_no) or update(alarmset_name)) and (not update(severity)) )
       begin
         if(not update(propagate_status))
         begin
       
           select @sev = a.severity from tng_alarmset_entry a, inserted i where i.alarmset_name=a.alarmset_name and
                   a.status_no=i.status_no
   
           select @prop_sev = m.propagated_sev from tng_managedobject m, inserted i where m.uuid = i.uuid
           
           if (@sev= NULL )
               select @sev = 0
           if (@sev > @prop_sev)
               select @Max_Sev = @sev
           else
               select @Max_Sev = @prop_sev
                    
               update tng_managedobject set severity=a.severity, weighted_severity = (isNull(m.weight ,0)* a.severity),
                       Max_Sev = @Max_Sev , propagate_status=a.propagate_status , date_modify=@timestamp
           from inserted i, tng_alarmset_entry a, tng_managedobject m     
             where m.uuid=i.uuid and i.alarmset_name=a.alarmset_name and
                   a.status_no=i.status_no    /*6/06/96and  i.severity <>a.severity*/
   
   
             if( @IsInsertChangeHistory = 0 and
                 exists ( select * from inserted i, tng_alarmset_entry a
                       where i.alarmset_name=a.alarmset_name and
                                 a.status_no=i.status_no  and
                             i.propagate_status<>a.propagate_status
                     )
             )    /*02/26/97*/
            begin
           
            insert into tng_change_history ( operation, class_name,  object_id1, portnum1,  portnum2, timestamp,user_name )
                              select          'u',    class_name,  uuid,          0,           0    , @timestamp,HOST_NAME()
                              from deleted
           end
           
           
         end
         else
            Begin
               
           select @sev = a.severity from tng_alarmset_entry a, inserted i where i.alarmset_name=a.alarmset_name and
                   a.status_no=i.status_no
   
           select @prop_sev = m.propagated_sev from tng_managedobject m, inserted i where m.uuid = i.uuid
   
           if (@sev = NULL)
               select @sev = 0
           if (@sev > @prop_sev)
               select @Max_Sev = @sev
           else
               select @Max_Sev = @prop_sev
   
            /*05/28/96 don't use trigger to enforce propagate_status in alarmset_entry if user updates propagate_status*/
         
               update tng_managedobject set severity=a.severity,weighted_severity = (isNull(m.weight ,0)*a.severity),
                       Max_Sev = @Max_Sev /*, date_modify=@timestamp*/
           from inserted i, tng_alarmset_entry a, tng_managedobject m     
             where m.uuid=i.uuid and i.alarmset_name=a.alarmset_name and
                   a.status_no=i.status_no     and  i.severity <>a.severity
             
         
            end
       end
   
   end    
   
   
   
   /* This is only used for updating the weighted severity and Max_Sev*/
   if(@Max_Sev > -1 and @weighted_sev > -1)
  begin
   update tng_managedobject
       Set weighted_severity = @weighted_sev,
           Max_Sev =@Max_Sev
   from tng_managedobject m, inserted i
   where m.uuid = i.uuid
   End
   
   
     /* 7/27/99 if acknowledge set to 1, turn off propagate_status */
     if (update(acknowledge) and ( 1 in (select acknowledge from inserted)))
     BEGIN
       update tng_managedobject set propagate_status = 0 from inserted i, tng_managedobject m
          where m.uuid = i.uuid and m.propagate_status <> 0 and m.acknowledge = 1
   
       insert into tng_change_history ( operation, class_name,  object_id1, portnum1,  portnum2, timestamp,user_name )
          select          'u',    m.class_name,  m.uuid,          0,           0    , @timestamp,HOST_NAME()
          from tng_managedobject m, inserted i
          where m.uuid = i.uuid and m.propagate_status <> 0 and m.acknowledge = 1
     END
   
   
   
    return

GO
CREATE NONCLUSTERED INDEX [x5_tng_manobj_address] ON [dbo].[tng_managedobject] ([address]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [x4_tng_manobj_asset_uuid] ON [dbo].[tng_managedobject] ([asset_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [x6_tng_manobj_clsname_add] ON [dbo].[tng_managedobject] ([class_name], [address]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [x3_tng_manobj_hidden] ON [dbo].[tng_managedobject] ([hidden]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [x7_tng_manobj_ip_addr_hex] ON [dbo].[tng_managedobject] ([ip_address_hex]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [x_tng_manobj_macaddress] ON [dbo].[tng_managedobject] ([mac_address], [class_name], [address_type]) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [baseidx_tng_managedobject] ON [dbo].[tng_managedobject] ([name], [class_name]) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [x1_tng_manobj_uuid] ON [dbo].[tng_managedobject] ([uuid], [class_name], [name], [weight], [propagated_sev]) ON [PRIMARY]
GO
GRANT SELECT ON  [dbo].[tng_managedobject] TO [amsgroup]
GRANT SELECT ON  [dbo].[tng_managedobject] TO [ca_itrm_group]
GRANT INSERT ON  [dbo].[tng_managedobject] TO [ca_itrm_group]
GRANT DELETE ON  [dbo].[tng_managedobject] TO [ca_itrm_group]
GRANT UPDATE ON  [dbo].[tng_managedobject] TO [ca_itrm_group]
GRANT SELECT ON  [dbo].[tng_managedobject] TO [ca_itrm_group_ams]
GRANT SELECT ON  [dbo].[tng_managedobject] TO [ucmadmin]
GRANT SELECT ON  [dbo].[tng_managedobject] TO [ucmuser]
GRANT SELECT ON  [dbo].[tng_managedobject] TO [uniadmin]
GRANT INSERT ON  [dbo].[tng_managedobject] TO [uniadmin]
GRANT DELETE ON  [dbo].[tng_managedobject] TO [uniadmin]
GRANT UPDATE ON  [dbo].[tng_managedobject] TO [uniadmin]
GRANT SELECT ON  [dbo].[tng_managedobject] TO [uniuser]
GRANT SELECT ON  [dbo].[tng_managedobject] TO [wvadmin]
GRANT INSERT ON  [dbo].[tng_managedobject] TO [wvadmin]
GRANT DELETE ON  [dbo].[tng_managedobject] TO [wvadmin]
GRANT UPDATE ON  [dbo].[tng_managedobject] TO [wvadmin]
GRANT SELECT ON  [dbo].[tng_managedobject] TO [wvuser]
GO