CREATE TABLE [dbo].[harstate]
(
[stateobjid] [int] NOT NULL,
[statename] [char] (128) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
[envobjid] [int] NOT NULL,
[stateorder] [int] NOT NULL,
[viewobjid] [int] NOT NULL,
[snapshot] [char] (1) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
[creationtime] [datetime] NOT NULL,
[creatorid] [int] NOT NULL,
[modifiedtime] [datetime] NOT NULL,
[modifierid] [int] NOT NULL,
[note] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[locationx] [int] NOT NULL CONSTRAINT [DF__harstate__locati__2FFA0313] DEFAULT ((0)),
[locationy] [int] NOT NULL CONSTRAINT [DF__harstate__locati__30EE274C] DEFAULT ((0)),
[pmstatusindex] [int] NOT NULL CONSTRAINT [DF__harstate__pmstat__31E24B85] DEFAULT ((0))
) ON [PRIMARY]
GO

/****** Object:  Trigger dbo.HARSTATEPROC_STATEID_TRIGGER    Script Date: 12/1/2005 11:51:03 PM ******/


/* HARSTATEPROCESS - HARSTATE  TRIGGER */

CREATE TRIGGER HARSTATEPROC_STATEID_TRIGGER ON dbo.HARSTATE FOR DELETE
AS

IF (@@ROWCOUNT = 0) RETURN

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


GO
ALTER TABLE [dbo].[harstate] ADD CONSTRAINT [XPKharstate] PRIMARY KEY CLUSTERED ([stateobjid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [HARSTATE_ENVOBJID] ON [dbo].[harstate] ([envobjid]) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [HARSTATE_LIST] ON [dbo].[harstate] ([envobjid], [stateobjid], [creatorid], [modifierid], [viewobjid], [statename], [stateorder], [snapshot], [locationx], [locationy], [creationtime], [modifiedtime]) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [harstate_ind] ON [dbo].[harstate] ([statename], [envobjid]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[harstate] ADD CONSTRAINT [HARSTATE_ENVID_FK] FOREIGN KEY ([envobjid]) REFERENCES [dbo].[harenvironment] ([envobjid]) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[harstate] ADD CONSTRAINT [harstate_pmstatusindex_fk] FOREIGN KEY ([pmstatusindex]) REFERENCES [dbo].[harpmstatus] ([pmstatusindex])
GO
ALTER TABLE [dbo].[harstate] ADD CONSTRAINT [HARSTATE_STATUS_FK] FOREIGN KEY ([pmstatusindex]) REFERENCES [dbo].[harpmstatus] ([pmstatusindex])
GO
ALTER TABLE [dbo].[harstate] ADD CONSTRAINT [harstate_viewid_fk] FOREIGN KEY ([viewobjid]) REFERENCES [dbo].[harview] ([viewobjid])
GO
GRANT SELECT ON  [dbo].[harstate] TO [harvest_group]
GRANT INSERT ON  [dbo].[harstate] TO [harvest_group]
GRANT DELETE ON  [dbo].[harstate] TO [harvest_group]
GRANT UPDATE ON  [dbo].[harstate] TO [harvest_group]
GRANT SELECT ON  [dbo].[harstate] TO [harvest_rep]
GO