CREATE TABLE [dbo].[HARSTATEPROCESS]
(
[STATEOBJID] [int] NOT NULL,
[PROCESSOBJID] [int] NOT NULL,
[PROCESSNAME] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
[PROCESSTYPE] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PROCESSORDER] [int] NOT NULL,
[POSTCOUNT] [int] NOT NULL CONSTRAINT [DF__HARSTATEP__POSTC__73852659] DEFAULT ((0)),
[PRECOUNT] [int] NOT NULL CONSTRAINT [DF__HARSTATEP__PRECO__74794A92] DEFAULT ((0))
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO

   CREATE TRIGGER HARNOTIFY_SPID_FK ON dbo.HARSTATEPROCESS FOR DELETE AS IF
   (@@ROWCOUNT = 0)
   RETURN IF EXISTS(SELECT *
   FROM HARNOTIFY AS E JOIN DELETED   AS D ON E.STATEOBJID  = D.STATEOBJID              
   AND E.PROCESSOBJID = D.PROCESSOBJID) DELETE
   FROM HARNOTIFY
   FROM HARNOTIFY AS E JOIN DELETED   AS D ON E.STATEOBJID  = D.STATEOBJID              
   AND E.PROCESSOBJID = D.PROCESSOBJID  

GO

   CREATE TRIGGER HARNOTIFYLIST_SPID_FK ON dbo.HARSTATEPROCESS FOR DELETE AS IF
   (@@ROWCOUNT = 0)
   RETURN IF EXISTS(SELECT *
   FROM HARNOTIFYLIST AS E JOIN DELETED   AS D ON E.STATEOBJID  = D.STATEOBJID              
   AND E.PROCESSOBJID = D.PROCESSOBJID) DELETE
   FROM HARNOTIFYLIST
   FROM HARNOTIFYLIST AS E JOIN DELETED   AS D ON E.STATEOBJID  = D.STATEOBJID              
   AND E.PROCESSOBJID = D.PROCESSOBJID  

GO

   CREATE TRIGGER HARUDP_SPID_TRIGGER ON dbo.HARSTATEPROCESS FOR DELETE AS IF
   (@@ROWCOUNT = 0)
   RETURN IF EXISTS(SELECT *
   FROM HARUDP AS E           JOIN DELETED AS D ON E.STATEOBJID = D.STATEOBJID                           
   AND E.PROCESSOBJID = D.PROCESSOBJID)    DELETE
   FROM HARUDP    
   FROM HARUDP AS E         JOIN DELETED AS D ON E.STATEOBJID = D.STATEOBJID                          
   AND E.PROCESSOBJID = D.PROCESSOBJID  

GO
ALTER TABLE [dbo].[HARSTATEPROCESS] ADD CONSTRAINT [HARSTATEPROCESS_PK] PRIMARY KEY CLUSTERED ([STATEOBJID], [PROCESSOBJID]) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [HARSTATEPROC_POBJID] ON [dbo].[HARSTATEPROCESS] ([PROCESSOBJID], [PROCESSNAME]) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [HARSTATEPROC_IND] ON [dbo].[HARSTATEPROCESS] ([STATEOBJID], [PROCESSNAME]) ON [PRIMARY]
GO
EXEC sp_addextendedproperty N'Caption', N'The HARSTATEPROCESS table stores the definition of each process state. ', 'SCHEMA', N'dbo', 'TABLE', N'HARSTATEPROCESS', NULL, NULL
GO
EXEC sp_addextendedproperty N'Caption', N'Number of post-linked processes', 'SCHEMA', N'dbo', 'TABLE', N'HARSTATEPROCESS', 'COLUMN', N'POSTCOUNT'
GO
EXEC sp_addextendedproperty N'Caption', N'Number of pre-linked processes', 'SCHEMA', N'dbo', 'TABLE', N'HARSTATEPROCESS', 'COLUMN', N'PRECOUNT'
GO
EXEC sp_addextendedproperty N'Caption', N'Name of the process', 'SCHEMA', N'dbo', 'TABLE', N'HARSTATEPROCESS', 'COLUMN', N'PROCESSNAME'
GO
EXEC sp_addextendedproperty N'Caption', N'Object ID of process', 'SCHEMA', N'dbo', 'TABLE', N'HARSTATEPROCESS', 'COLUMN', N'PROCESSOBJID'
GO
EXEC sp_addextendedproperty N'Caption', N'Process order in state', 'SCHEMA', N'dbo', 'TABLE', N'HARSTATEPROCESS', 'COLUMN', N'PROCESSORDER'
GO
EXEC sp_addextendedproperty N'Caption', N'Type of process', 'SCHEMA', N'dbo', 'TABLE', N'HARSTATEPROCESS', 'COLUMN', N'PROCESSTYPE'
GO
EXEC sp_addextendedproperty N'Caption', N'Object ID of state', 'SCHEMA', N'dbo', 'TABLE', N'HARSTATEPROCESS', 'COLUMN', N'STATEOBJID'
GO