CREATE TABLE [dbo].[HARSTATE]
(
[STATEOBJID] [int] NOT NULL,
[STATENAME] [varchar] (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_CI_AS NOT NULL,
[CREATIONTIME] [datetime] NOT NULL CONSTRAINT [DF__HARSTATE__CREATI__690797E6] DEFAULT (getutcdate()),
[CREATORID] [int] NOT NULL,
[MODIFIEDTIME] [datetime] NOT NULL CONSTRAINT [DF__HARSTATE__MODIFI__69FBBC1F] DEFAULT (getutcdate()),
[MODIFIERID] [int] NOT NULL,
[NOTE] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LOCATIONX] [int] NOT NULL CONSTRAINT [DF__HARSTATE__LOCATI__6AEFE058] DEFAULT ((0)),
[LOCATIONY] [int] NOT NULL CONSTRAINT [DF__HARSTATE__LOCATI__6BE40491] DEFAULT ((0)),
[PMSTATUSINDEX] [int] NOT NULL CONSTRAINT [DF__HARSTATE__PMSTAT__6CD828CA] DEFAULT ((0))
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO

   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 [HARSTATE_PK] 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_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
EXEC sp_addextendedproperty N'Caption', N'The HARSTATE table stores state information. ', 'SCHEMA', N'dbo', 'TABLE', N'HARSTATE', NULL, NULL
GO
EXEC sp_addextendedproperty N'Caption', N'Time object was created', 'SCHEMA', N'dbo', 'TABLE', N'HARSTATE', 'COLUMN', N'CREATIONTIME'
GO
EXEC sp_addextendedproperty N'Caption', N'Object ID of creator', 'SCHEMA', N'dbo', 'TABLE', N'HARSTATE', 'COLUMN', N'CREATORID'
GO
EXEC sp_addextendedproperty N'Caption', N'Object ID of project', 'SCHEMA', N'dbo', 'TABLE', N'HARSTATE', 'COLUMN', N'ENVOBJID'
GO
EXEC sp_addextendedproperty N'Caption', N'X coordinate of state in life cycle diagram', 'SCHEMA', N'dbo', 'TABLE', N'HARSTATE', 'COLUMN', N'LOCATIONX'
GO
EXEC sp_addextendedproperty N'Caption', N'Y coordinate of state in life cycle diagram', 'SCHEMA', N'dbo', 'TABLE', N'HARSTATE', 'COLUMN', N'LOCATIONY'
GO
EXEC sp_addextendedproperty N'Caption', N'Time object was modified', 'SCHEMA', N'dbo', 'TABLE', N'HARSTATE', 'COLUMN', N'MODIFIEDTIME'
GO
EXEC sp_addextendedproperty N'Caption', N'Object ID of modifier', 'SCHEMA', N'dbo', 'TABLE', N'HARSTATE', 'COLUMN', N'MODIFIERID'
GO
EXEC sp_addextendedproperty N'Caption', N'User-defined note', 'SCHEMA', N'dbo', 'TABLE', N'HARSTATE', 'COLUMN', N'NOTE'
GO
EXEC sp_addextendedproperty N'Caption', N'Snapshot view (Y/N)', 'SCHEMA', N'dbo', 'TABLE', N'HARSTATE', 'COLUMN', N'SNAPSHOT'
GO
EXEC sp_addextendedproperty N'Caption', N'Name of state', 'SCHEMA', N'dbo', 'TABLE', N'HARSTATE', 'COLUMN', N'STATENAME'
GO
EXEC sp_addextendedproperty N'Caption', N'Object ID of state', 'SCHEMA', N'dbo', 'TABLE', N'HARSTATE', 'COLUMN', N'STATEOBJID'
GO
EXEC sp_addextendedproperty N'Caption', N'Order of states in the project', 'SCHEMA', N'dbo', 'TABLE', N'HARSTATE', 'COLUMN', N'STATEORDER'
GO
EXEC sp_addextendedproperty N'Caption', N'Object ID of view', 'SCHEMA', N'dbo', 'TABLE', N'HARSTATE', 'COLUMN', N'VIEWOBJID'
GO