CREATE TABLE [dbo].[haritems]
(
[itemobjid] [int] NOT NULL,
[itemname] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
[itemtype] [int] NOT NULL,
[parentobjid] [int] NOT NULL,
[repositobjid] [int] NOT NULL,
[creationtime] [datetime] NOT NULL,
[creatorid] [int] NOT NULL,
[modifiedtime] [datetime] NOT NULL,
[modifierid] [int] NOT NULL,
[itemnameupper] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL CONSTRAINT [DF__haritems__itemna__556091EC] DEFAULT (' ')
) ON [PRIMARY]
GO

/****** Object:  Trigger dbo.HARITEMS_PID_TRIGGER    Script Date: 12/1/2005 11:50:35 PM ******/


/****************************************************************************************/
/*  Change Control                                                                      */
/*                                                                                      */
/*  Change Date Changes By  Version                                                     */
/*  =========== ==========  =======                                                     */
/*  17-mar-2005 mulwi01     Star 13983675 HARVEST SQLSVR TRIGGER DDL                    */
/*  12-sep-2005 hopst01     Star 14312708 HARVEST SQLSRV TRIGGER AND TABLE DDL            */
/*  21-sep-2005 mulwi01     add DELETE CASCADE to foreign keys                            */
/*  26-sep-2005 mulwi01     Star 14406130 SQLSERVER MDB EXTRA CONSTRAINT                */
/*                                                                                      */
/****************************************************************************************/

/* TRIGGERS */

/* HARITEMS - HARITEMS TRIGGER */

CREATE TRIGGER HARITEMS_PID_TRIGGER ON dbo.HARITEMS FOR DELETE
AS

IF (@@ROWCOUNT = 0) RETURN

IF EXISTS(SELECT *
          FROM
              HARITEMS AS E
            JOIN
              DELETED   AS D ON E.PARENTOBJID = D.ITEMOBJID)
  DELETE FROM HARITEMS
  FROM
      HARITEMS AS E
    JOIN
      DELETED   AS D ON E.PARENTOBJID = D.ITEMOBJID


GO

/****** Object:  Trigger dbo.HARVERSIONS_ITEMID_TRIGGER    Script Date: 12/1/2005 11:51:04 PM ******/



/* HARVERSIONS - HARITEMS  TRIGGER */

CREATE TRIGGER HARVERSIONS_ITEMID_TRIGGER ON dbo.HARITEMS FOR DELETE
AS

IF (@@ROWCOUNT = 0) RETURN

IF EXISTS(SELECT *
          FROM
              HARVERSIONS AS E
            JOIN
              DELETED   AS D ON E.ITEMOBJID = D.ITEMOBJID)
  DELETE FROM HARVERSIONS
  FROM
      HARVERSIONS AS E
    JOIN
      DELETED   AS D ON E.ITEMOBJID = D.ITEMOBJID


GO
ALTER TABLE [dbo].[haritems] ADD CONSTRAINT [XPKharitems] PRIMARY KEY CLUSTERED ([itemobjid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [haritems_itemname] ON [dbo].[haritems] ([itemname]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [HARITERMS_ITEMNAMEUPPPER] ON [dbo].[haritems] ([itemnameupper], [parentobjid], [itemobjid], [itemname], [repositobjid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [haritems_idx_type] ON [dbo].[haritems] ([itemtype], [itemobjid], [parentobjid], [itemname], [repositobjid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [HARITEMS_IND_TYPE] ON [dbo].[haritems] ([itemtype], [parentobjid]) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [p_ukey] ON [dbo].[haritems] ([parentobjid], [itemname]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [haritems_idx_002] ON [dbo].[haritems] ([parentobjid], [itemobjid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [HARITEMS_PARENTTYPE] ON [dbo].[haritems] ([parentobjid], [itemtype], [itemobjid], [modifierid], [creatorid], [itemname], [repositobjid], [creationtime], [modifiedtime]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [HARITEMS_REPID] ON [dbo].[haritems] ([repositobjid]) ON [PRIMARY]
GO
GRANT SELECT ON  [dbo].[haritems] TO [harvest_group]
GRANT INSERT ON  [dbo].[haritems] TO [harvest_group]
GRANT DELETE ON  [dbo].[haritems] TO [harvest_group]
GRANT UPDATE ON  [dbo].[haritems] TO [harvest_group]
GRANT SELECT ON  [dbo].[haritems] TO [harvest_rep]
GO