CREATE TABLE [dbo].[ca_software_def]
(
[sw_def_uuid] [binary] (16) NOT NULL,
[name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[sw_version_label] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[language] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS 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_softwa__versi__21F5FC7F] DEFAULT ((0)),
[sw_version_number] [char] (20) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[replication_flag] [int] NULL,
[software_type_id] [int] NOT NULL,
[source_type_id] [int] NOT NULL,
[description] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[auto_rep_version] [timestamp] NULL,
[manufacturer_uuid] [binary] (16) NULL,
[bit_support_id] [binary] (16) NULL,
[chip_set_id] [binary] (16) NULL,
[domain_uuid] [binary] (16) NULL,
[impact] [tinyint] NULL,
[severity] [tinyint] NULL,
[is_active] [tinyint] NOT NULL CONSTRAINT [DF__ca_softwa__is_ac__22EA20B8] DEFAULT ((1)),
[no_longer_available] [tinyint] NULL,
[family_sw_def_uuid] [binary] (16) NULL,
[lang_code] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[class_id] [int] NULL,
[linear_sequence_number] [int] NULL,
[filter_from_view] [tinyint] NULL CONSTRAINT [DF__ca_softwa__filte__23DE44F1] DEFAULT ((0)),
[drcs_content_type_id] [int] NULL,
[requires_review] [int] NULL
) ON [PRIMARY]
GO
CREATE trigger r_ssf_versionupdate_def
    on ca_software_def
    after update,insert
as
begin
    declare @_sigdefchanged as int;

    set @_sigdefchanged=0;
    select top 1 @_sigdefchanged=1 from inserted where source_type_id<>3;
    if @_sigdefchanged=1
    begin
        execute  p_sp_ssf_versionupdate
    end
end
GO
/****** Object:  Trigger dbo.rule_d_so_software_definition    Script Date: 12/1/2005 11:51:37 PM ******/
/*
******************************************
  trigger if a software defintion is deleted
*/
CREATE trigger rule_d_so_software_definition
     on ca_software_def
     after delete
as
begin
    declare @d_sw_def_uuid binary(16);
    

    declare cur_del cursor for
    select sw_def_uuid
    from deleted;
    
    open cur_del;
    fetch cur_del into @d_sw_def_uuid; -- get first
    while @@fetch_status = 0
    begin
  
        execute  proc_d_so_removed_object @d_sw_def_uuid;
        
        fetch cur_del into @d_sw_def_uuid; -- get next

     end; -- while
     close cur_del;
     deallocate cur_del;
    

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

/*
*********************************************************
*********************************************************
* Procedures and rules to manage Sofware Definitions
*********************************************************
*********************************************************
*/
/*
**********************************************
rule for insert of Class software definition
*/
CREATE trigger rule_i_new_so_software_def
    on ca_software_def
    after insert
as
begin
    declare @_obj_uuid binary(16);
    declare @_clsid integer;
    declare @_uri nvarchar(255);

    
    set @_clsid = 1008;

    declare cur_cl cursor for
    select sw_def_uuid, creation_user
    from inserted;
    
    open cur_cl;
    fetch cur_cl into @_obj_uuid, @_uri; -- get first
    while @@fetch_status = 0
    begin
  
        execute  proc_i_new_so_object @_obj_uuid, @_clsid, @_uri;
        
        fetch cur_cl into @_obj_uuid, @_uri; -- get next

     end; -- while
     close cur_cl;
     deallocate cur_cl;
    
end;
GO
/****** Object:  Trigger dbo.usd_trg_d_swdef_usd_rel    Script Date: 12/1/2005 11:51:56 PM ******/
CREATE trigger usd_trg_d_swdef_usd_rel
on ca_software_def
for delete as
begin
    /* Go ahead and delete */

    /* Delete relation from Software to software def object */
    update usd_rsw set version = version + 1,
    software_def = 0x00000000000000000000000000000000
    from deleted d
    where software_def = d.sw_def_uuid
end
GO
ALTER TABLE [dbo].[ca_software_def] ADD CONSTRAINT [XPKca_software_def] PRIMARY KEY CLUSTERED ([sw_def_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_software_def_idx_07] ON [dbo].[ca_software_def] ([drcs_content_type_id]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_software_def_ix04] ON [dbo].[ca_software_def] ([impact]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_software_def_ix06] ON [dbo].[ca_software_def] ([linear_sequence_number]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_software_def_idx_03] ON [dbo].[ca_software_def] ([manufacturer_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_software_def_idx_01] ON [dbo].[ca_software_def] ([name]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_software_def_ix05] ON [dbo].[ca_software_def] ([severity]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_software_def_idx_02] ON [dbo].[ca_software_def] ([software_type_id]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ca_software_def] ADD CONSTRAINT [$ca_so_r000004cf00000000] FOREIGN KEY ([manufacturer_uuid]) REFERENCES [dbo].[ca_company] ([company_uuid])
GO
ALTER TABLE [dbo].[ca_software_def] ADD CONSTRAINT [$ca_so_r000004d800000000] FOREIGN KEY ([source_type_id]) REFERENCES [dbo].[ca_source_type] ([source_type_id])
GO
ALTER TABLE [dbo].[ca_software_def] ADD CONSTRAINT [$ca_so_r000004e200000000] FOREIGN KEY ([software_type_id]) REFERENCES [dbo].[ca_software_type] ([software_type_id])
GO
ALTER TABLE [dbo].[ca_software_def] ADD CONSTRAINT [$ca_so_r000004eb00000000] FOREIGN KEY ([family_sw_def_uuid]) REFERENCES [dbo].[ca_software_def] ([sw_def_uuid])
GO
ALTER TABLE [dbo].[ca_software_def] ADD CONSTRAINT [$ca_so_r0000064300000000] FOREIGN KEY ([class_id]) REFERENCES [dbo].[ca_class_def] ([class_id])
GO
ALTER TABLE [dbo].[ca_software_def] ADD CONSTRAINT [$ca_so_r0000065b00000000] FOREIGN KEY ([lang_code]) REFERENCES [dbo].[ca_language] ([lang_code])
GO
ALTER TABLE [dbo].[ca_software_def] ADD CONSTRAINT [$ca_so_r00001c7500000000] FOREIGN KEY ([bit_support_id]) REFERENCES [dbo].[bit_support] ([bit_support_id])
GO
ALTER TABLE [dbo].[ca_software_def] ADD CONSTRAINT [$ca_so_r00001c7f00000000] FOREIGN KEY ([chip_set_id]) REFERENCES [dbo].[chip_set] ([chip_set_id])
GO
ALTER TABLE [dbo].[ca_software_def] ADD CONSTRAINT [FK_ca_sw_def_drcs_cont_type] FOREIGN KEY ([drcs_content_type_id]) REFERENCES [dbo].[ca_drcs_content_type] ([drcs_content_type_id])
GO
GRANT SELECT ON  [dbo].[ca_software_def] TO [aiadmin]
GRANT SELECT ON  [dbo].[ca_software_def] TO [ams_group]
GRANT SELECT ON  [dbo].[ca_software_def] TO [amsgroup]
GRANT SELECT ON  [dbo].[ca_software_def] TO [ca_itrm_group]
GRANT INSERT ON  [dbo].[ca_software_def] TO [ca_itrm_group]
GRANT DELETE ON  [dbo].[ca_software_def] TO [ca_itrm_group]
GRANT UPDATE ON  [dbo].[ca_software_def] TO [ca_itrm_group]
GRANT SELECT ON  [dbo].[ca_software_def] TO [ca_itrm_group_ams]
GRANT SELECT ON  [dbo].[ca_software_def] TO [regadmin]
GRANT SELECT ON  [dbo].[ca_software_def] TO [swcmadmin]
GRANT INSERT ON  [dbo].[ca_software_def] TO [swcmadmin]
GRANT DELETE ON  [dbo].[ca_software_def] TO [swcmadmin]
GRANT UPDATE ON  [dbo].[ca_software_def] TO [swcmadmin]
GRANT SELECT ON  [dbo].[ca_software_def] TO [uapmadmin]
GRANT INSERT ON  [dbo].[ca_software_def] TO [uapmadmin]
GRANT DELETE ON  [dbo].[ca_software_def] TO [uapmadmin]
GRANT UPDATE ON  [dbo].[ca_software_def] TO [uapmadmin]
GRANT SELECT ON  [dbo].[ca_software_def] TO [uapmadmin_group]
GRANT INSERT ON  [dbo].[ca_software_def] TO [uapmadmin_group]
GRANT DELETE ON  [dbo].[ca_software_def] TO [uapmadmin_group]
GRANT UPDATE ON  [dbo].[ca_software_def] TO [uapmadmin_group]
GRANT SELECT ON  [dbo].[ca_software_def] TO [uapmbatch]
GRANT INSERT ON  [dbo].[ca_software_def] TO [uapmbatch]
GRANT DELETE ON  [dbo].[ca_software_def] TO [uapmbatch]
GRANT UPDATE ON  [dbo].[ca_software_def] TO [uapmbatch]
GRANT SELECT ON  [dbo].[ca_software_def] TO [uapmbatch_group]
GRANT INSERT ON  [dbo].[ca_software_def] TO [uapmbatch_group]
GRANT DELETE ON  [dbo].[ca_software_def] TO [uapmbatch_group]
GRANT UPDATE ON  [dbo].[ca_software_def] TO [uapmbatch_group]
GRANT SELECT ON  [dbo].[ca_software_def] TO [uapmreporting]
GRANT SELECT ON  [dbo].[ca_software_def] TO [uapmreporting_group]
GRANT SELECT ON  [dbo].[ca_software_def] TO [upmadmin_group]
GRANT INSERT ON  [dbo].[ca_software_def] TO [upmadmin_group]
GRANT DELETE ON  [dbo].[ca_software_def] TO [upmadmin_group]
GRANT UPDATE ON  [dbo].[ca_software_def] TO [upmadmin_group]
GRANT SELECT ON  [dbo].[ca_software_def] TO [upmuser_group]
GRANT INSERT ON  [dbo].[ca_software_def] TO [upmuser_group]
GRANT DELETE ON  [dbo].[ca_software_def] TO [upmuser_group]
GRANT UPDATE ON  [dbo].[ca_software_def] TO [upmuser_group]
GO