CREATE TABLE [dbo].[tng_link]
(
[uuid] [binary] (16) NOT NULL,
[name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[label] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[class_name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
[source_class] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
[source_uuid] [binary] (16) NOT NULL,
[source_repository] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[source_port] [int] NULL,
[dest_class] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
[dest_uuid] [binary] (16) NOT NULL,
[dest_repository] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[dest_port] [int] NULL,
[date_ins] [datetime] NULL,
[status_no] [int] NULL,
[severity] [int] NULL,
[tng_delete_flag] [int] NULL,
[hidden] [tinyint] NULL CONSTRAINT [DF__tng_link__hidden__6E022284] DEFAULT ((0)),
[alarmset_name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[direction] [int] NULL,
[source_seq] [int] NULL,
[dest_seq] [int] NULL,
[parent_link] [binary] (16) NULL,
[implied] [tinyint] NULL CONSTRAINT [DF__tng_link__implie__6EF646BD] DEFAULT ((0)),
[link_flag] [int] NULL CONSTRAINT [DF__tng_link__link_f__6FEA6AF6] DEFAULT ((0)),
[reserved] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[unimplied] [tinyint] NULL CONSTRAINT [DF__tng_link__unimpl__70DE8F2F] DEFAULT ((0)),
[common_ancestor_class] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[common_ancestor_uuid] [binary] (16) NULL,
[user_data1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_data2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[max_parcel_size] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[p2p_protocol] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[p2p_params] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[fanout_protocol] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[fanout_params] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[p2m_protocol] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[p2m_params] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[p2m_naddr] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[throttle_factor] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[metric] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[concurrency] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[calendar] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[calendar_server] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
/****** Object:  Trigger dbo.tng_td_link    Script Date: 12/1/2005 11:50:41 PM ******/


CREATE trigger tng_td_link on tng_link for delete as
set nocount on

  if not exists (select * from deleted)
    return

  /*05/28/96*/
  insert into tng_change_history ( operation, class_name, object_id1, portnum1, object_id2, portnum2, object_id3,timestamp, source_seq, dest_seq, user_name, parent_src_class, child_des_class  )
                         select         'd',   class_name,  uuid,   source_port,source_uuid,dest_port, dest_uuid, getdate(), source_seq, dest_seq, SUSER_SNAME(), source_class, dest_class
                         from deleted

  /* delete implied links too */
  if not exists (select * from deleted where implied = 0)
    return
  insert into tng_change_history ( operation, class_name, object_id1, portnum1, object_id2, portnum2, object_id3,timestamp, source_seq, dest_seq, user_name, parent_src_class, child_des_class)
                         select  'd', l.class_name, l.uuid, l.source_port,l.source_uuid,l.dest_port, l.dest_uuid, getdate(), l.source_seq, l.dest_seq, SUSER_SNAME(), l.source_class, l.dest_class
                         from tng_link l, deleted d where (d.implied = 0 and l.parent_link = d.uuid)

  delete from tng_link from tng_link l, deleted d where (d.implied = 0 and l.parent_link = d.uuid)

  return
GO
/****** Object:  Trigger dbo.tng_ti_link    Script Date: 12/1/2005 11:50:43 PM ******/


CREATE trigger tng_ti_link on tng_link FOR INSERT AS
set nocount on

  if not exists ( select * from inserted )
    return
   insert into tng_change_history ( operation, class_name, object_id1,  portnum1, object_id2,  portnum2, object_id3, timestamp, user_name, parent_src_class, child_des_class )
                         select         'i',   class_name,  uuid,         0, source_uuid,       0, dest_uuid, getdate(),SUSER_SNAME(), source_class, dest_class
                         from inserted
  return
GO
/****** Object:  Trigger dbo.tng_tu_link    Script Date: 12/1/2005 11:50:45 PM ******/


CREATE trigger tng_tu_link on tng_link for update as
set nocount on
declare @uuid_bin UUID
declare @timestamp datetime      

  if not exists ( select * from inserted )
    return

  if( update(class_name) or update(uuid) or update(implied) or update(parent_link))
  begin
    select 80002    
    raiserror 80002 'Do not allow to update class name or uuid or implied or parent_link of a link object, the transaction failed. '
    rollback transaction
    return
  end

  select @timestamp=getdate()

  if( update(name)              or update(label)       or update(source_class) or update(source_uuid)   or
      update(source_repository) or update(source_port) or update(dest_class)   or update(dest_uuid)     or
      update(dest_repository)   or update(dest_port)   or update(hidden)       or update(alarmset_name) or
      update(direction) or update(unimplied)
    )
      insert into tng_change_history ( operation, class_name, object_id1,  portnum1, object_id2,  portnum2, object_id3, timestamp, user_name, parent_src_class, child_des_class )                          
                            select         'u',   class_name,  uuid,         0,  source_uuid,  0, dest_uuid, @timestamp,SUSER_SNAME(),source_class, dest_class
                            from inserted


  if( update(status_no) or update(severity) or update(alarmset_name))
  begin
    
    if(not update(severity))  /* if only status is updated*/
    begin/*05/28/96 if user not update severity, we enforce alarmset rule defined in alarmset_entry */                                
      /*But insert current Info if their alarmset_name and status_no are not defined in tng_alarmset_entry */
      
      insert into tng_status_history( class_name,  record_uuid, status_no, severity, timestamp)
                            select     i.class_name, i.uuid,   i.status_no, i.severity, @timestamp
                            from  inserted i    
                            where not exists (select * from tng_alarmset_entry a
                                        where a.alarmset_name=i.alarmset_name and
                                            a.status_no=i.status_no)



      /*Insert current Info which alarmset_name and status_no are defined in tng_alarmset_entry */
    
      insert into tng_status_history( class_name,  record_uuid, status_no, severity, timestamp)
                            select     i.class_name, i.uuid,   i.status_no, a.severity, @timestamp
                            from  inserted i     , tng_alarmset_entry a
                            where i.status_no=a.status_no    and i.alarmset_name=a.alarmset_name

    
    
      /*Above rules apply for implied links too */
    
    /*But insert current Info if their alarmset_name and status_no are not defined in tng_alarmset_entry */
      
      insert into tng_status_history( class_name,  record_uuid, status_no, severity, timestamp)
                            select     tl.class_name, tl.uuid,   i.status_no, i.severity, @timestamp
                            from  tng_link tl, inserted i     
                            where not exists (select * from tng_alarmset_entry a
                                        where a.alarmset_name=i.alarmset_name and
                                            a.status_no=i.status_no)
                             and tl.parent_link = i.uuid

      /*Insert current Info which alarmset_name and status_no are defined in tng_alarmset_entry */
    
    insert into tng_status_history( class_name,  record_uuid, status_no, severity, timestamp)
                            select     tl.class_name, tl.uuid,   i.status_no, a.severity, @timestamp
                            from  tng_link tl,inserted i,tng_alarmset_entry a
                            where i.status_no=a.status_no    
                            and i.alarmset_name=a.alarmset_name
                            and tl.parent_link = i.uuid

    end
    else /*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)
                            select     i.class_name, i.uuid,   i.status_no, i.severity, @timestamp
                            from  inserted i

    
      /* Set values for implied links too */

    insert into tng_status_history( class_name,  record_uuid, status_no, severity, timestamp)
                            select     tl.class_name, tl.uuid, i.status_no, i.severity, @timestamp
                            from  tng_link tl, inserted i    
                            where tl.parent_link = i.uuid

                  
      UPDATE tng_link SET status_no=i.status_no, severity=i.severity  
                      FROM inserted i, tng_link tl     
                      WHERE tl.parent_link = i.uuid
    
    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
      /*06/05/96 use trigger to enforce severity in alarmset_entry*/
      /* For Real Link*/
      update tng_link set severity=a.severity
        from inserted i, tng_alarmset_entry a, tng_link l     
          where l.uuid=i.uuid and i.alarmset_name=a.alarmset_name and    
                a.status_no=i.status_no and  i.severity <>a.severity

    
  /* For implied links too */
      UPDATE tng_link SET status_no=i.status_no, severity=a.severity  
                      FROM inserted i, tng_alarmset_entry a, tng_link l     
                      WHERE l.parent_link = i.uuid
             and i.alarmset_name=a.alarmset_name     
                     and a.status_no=i.status_no

      
    end

  end

/* Delete the links if unimplied field is made true from false*/
    if( update(unimplied))
        delete from tng_link from tng_link l, deleted d, inserted i where (d.unimplied = 0 and i.unimplied = 1 and l.parent_link = d.uuid and d.uuid = i.uuid)

  return
GO
CREATE NONCLUSTERED INDEX [x_tng_link_class_name] ON [dbo].[tng_link] ([class_name]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [x_tng_link_com_ans_uuid] ON [dbo].[tng_link] ([common_ancestor_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [x_tng_link_dest_seq] ON [dbo].[tng_link] ([dest_seq]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [x_tng_link_dest_uuid] ON [dbo].[tng_link] ([dest_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [x_tng_link_source_seq] ON [dbo].[tng_link] ([source_seq]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [x_tng_link_source_uuid] ON [dbo].[tng_link] ([source_uuid]) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [baseidx_tng_link] ON [dbo].[tng_link] ([uuid]) ON [PRIMARY]
GO
GRANT SELECT ON  [dbo].[tng_link] TO [uniadmin]
GRANT INSERT ON  [dbo].[tng_link] TO [uniadmin]
GRANT DELETE ON  [dbo].[tng_link] TO [uniadmin]
GRANT UPDATE ON  [dbo].[tng_link] TO [uniadmin]
GRANT SELECT ON  [dbo].[tng_link] TO [uniuser]
GRANT SELECT ON  [dbo].[tng_link] TO [wvadmin]
GRANT INSERT ON  [dbo].[tng_link] TO [wvadmin]
GRANT DELETE ON  [dbo].[tng_link] TO [wvadmin]
GRANT UPDATE ON  [dbo].[tng_link] TO [wvadmin]
GRANT SELECT ON  [dbo].[tng_link] TO [wvuser]
GO