CREATE TABLE [dbo].[ca_category_def]
(
[category_uuid] [binary] (16) NOT NULL,
[category_name] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
[creation_user] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[creation_date] [int] NULL,
[last_update_user] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[last_update_date] [int] NULL,
[version_number] [int] NULL CONSTRAINT [DF__ca_catego__versi__60924D76] DEFAULT ('0'),
[category_type_code] [tinyint] NULL,
[parent_category_uuid] [binary] (16) NULL,
[source_type_id] [int] NULL CONSTRAINT [DF__ca_catego__sourc__618671AF] DEFAULT ('1'),
[domain_uuid] [binary] (16) NULL,
[auto_rep_version] [timestamp] NULL
) ON [PRIMARY]

GO
CREATE trigger  r_d_links_category_def
     on ca_category_def
     instead of delete
as
  
begin
    
        delete ca_category_member from ca_category_member cm, deleted d WHERE  cm.category_uuid = d.category_uuid ;        
        delete ca_category_def from ca_category_def  cf, deleted d where cf.category_uuid= d.category_uuid ;
        
end;
GO
/****** Object:  Trigger dbo.rule_d_so_sw_category    Script Date: 12/1/2005 11:50:53 PM ******/
CREATE trigger  rule_d_so_sw_category
     on ca_category_def
     after delete
as
  declare @ddis_hw_uuid binary(16);
begin
     
    declare cur_cl cursor for
        select category_uuid from deleted;

    open cur_cl;
    fetch cur_cl into @ddis_hw_uuid; -- get first
    while @@fetch_status = 0
    begin
    
        execute  proc_d_so_removed_object @ddis_hw_uuid;
        fetch cur_cl into @ddis_hw_uuid;

     end; -- while
     close cur_cl;
     deallocate cur_cl;

end;
GO
/****************************************************************************************/
/*                                                                                      */
/* Star 14843483 DSM: Alter rule rule_i_new_so_sw_category for ca_category_def */
/*                                                                                      */
/****************************************************************************************/

/* lock objects...  */

/* Start of lines added to convert to online lock */


/* Start of locks for dependent tables */

/* End of lines added to convert to online lock */

/* ********************** 10437 begin  ******************* */

/**
***********************************************
***********************************************
  bug fix : inserting multiple categories
  Sample : UPM does a query like

    INSERT INTO ca_category_def
    (category_uuid, ...)
    SELECT t.category_uuid, ..
    FROM #ca_category_def t
    WHERE t.category_uuid NOT IN ( SELECT category_uuid FROM ca_category_def ) ;


***********************************************
***********************************************
*/

/*
**********************************************
rule for insert of Class software category
*/
CREATE  trigger rule_i_new_so_sw_category
    on ca_category_def
    after insert
as
begin
      declare @_obj_uuid   binary(16);
    declare @_clsid      integer;
    declare @_uri        nvarchar(255);

    select @_clsid = 1009;

    declare curCatIns cursor for
        select category_uuid, creation_user from inserted;

    open curCatIns;
    fetch curCatIns into @_obj_uuid, @_uri; -- get first
    while @@fetch_status = 0
    begin
        execute  proc_i_new_so_object @_obj_uuid, @_clsid, @_uri;
        fetch curCatIns into @_obj_uuid, @_uri; -- get next

     end; -- while
     close curCatIns;
     deallocate curCatIns;


end;
GO
ALTER TABLE [dbo].[ca_category_def] ADD CONSTRAINT [XPKca_category_def] PRIMARY KEY NONCLUSTERED ([category_uuid]) ON [PRIMARY]
GO
GRANT SELECT ON  [dbo].[ca_category_def] TO [aiadmin]
GRANT SELECT ON  [dbo].[ca_category_def] TO [ca_itrm_group]
GRANT INSERT ON  [dbo].[ca_category_def] TO [ca_itrm_group]
GRANT DELETE ON  [dbo].[ca_category_def] TO [ca_itrm_group]
GRANT UPDATE ON  [dbo].[ca_category_def] TO [ca_itrm_group]
GRANT SELECT ON  [dbo].[ca_category_def] TO [ca_itrm_group_ams]
GRANT SELECT ON  [dbo].[ca_category_def] TO [regadmin]
GRANT SELECT ON  [dbo].[ca_category_def] TO [swcmadmin]
GRANT INSERT ON  [dbo].[ca_category_def] TO [swcmadmin]
GRANT DELETE ON  [dbo].[ca_category_def] TO [swcmadmin]
GRANT UPDATE ON  [dbo].[ca_category_def] TO [swcmadmin]
GRANT SELECT ON  [dbo].[ca_category_def] TO [upmadmin_group]
GRANT INSERT ON  [dbo].[ca_category_def] TO [upmadmin_group]
GRANT DELETE ON  [dbo].[ca_category_def] TO [upmadmin_group]
GRANT UPDATE ON  [dbo].[ca_category_def] TO [upmadmin_group]
GRANT SELECT ON  [dbo].[ca_category_def] TO [upmuser_group]
GRANT INSERT ON  [dbo].[ca_category_def] TO [upmuser_group]
GRANT DELETE ON  [dbo].[ca_category_def] TO [upmuser_group]
GRANT UPDATE ON  [dbo].[ca_category_def] TO [upmuser_group]
GO