by
Hi here is a problem I have that I need some help on ... the talbes are defined below
1) the categories table consist of a bunch of document categories
2) the categories_security table consist of which categories are restricted to which groups of people
3) the documents table consist of a bunch of documents
4) the documents_security table consist of which documents are restricted to which groups of people
What I would like to happen is this
1) when a new category is inserted it inherits the security attributes of its immediate parent category
2) when a new document is inserted it inherits the security attributes of the category it is assigned too
3) when a category is updated it passes its updated secuity attributes to its immediate children categories (who in turn should pass them to their children and then their children to their children and so on....)
4) when a category is update it passes its security attributes to the documents immediately with in it
I've attempted this from within the SPs where I insert and update docs and cats and then trying with triggers on the security tables to attempt something like recursion Its not working.
Does anyone know how to accomplish this task?
Thanks.
----------------- BEGIN TABLEs DEFN --------------------
CREATE TABLE [dbo].[carsdb_document_management_categories] (
[autoid] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryID] [nvarchar] (50) NOT NULL ,
[Category] [nvarchar] (100) NULL ,
[ParentID] [nvarchar] (50) NULL ,
[Leaf] [bit] NULL ,
[posterid] [nvarchar] (50) NULL ,
[disabled] [bit] NULL ,
[sortorder] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[carsdb_document_management_categories_security] (
[autoid] [int] IDENTITY (1, 1) NOT NULL ,
[catgid] [nvarchar] (50) NOT NULL ,
[categoryid] [nvarchar] (50) NULL ,
[groupid] [nvarchar] (50) NULL ,
[posterid] [nvarchar] (50) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[carsdb_document_management_document_security] (
[autoid] [int] IDENTITY (1, 1) NOT NULL ,
[datgid] [nvarchar] (50) NOT NULL ,
[documentid] [nvarchar] (50) NULL ,
[groupid] [nvarchar] (50) NULL ,
[posterid] [nvarchar] (50) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[carsdb_document_management_documents] (
[autoid] [int] IDENTITY (1, 1) NOT NULL ,
[documentid] [nvarchar] (50) NOT NULL ,
[posterid] [nvarchar] (50) NULL ,
[documenttitle] [nvarchar] (150) NULL ,
[filename] [nvarchar] (255) NULL ,
[description] [nvarchar] (4000) NULL ,
[keywords] [nvarchar] (1000) NULL ,
[highlight] [bit] NULL ,
[inline] [bit] NULL ,
[filelocation] [nvarchar] (255) NULL ,
[filesize] [float] NULL ,
[filecontenttype] [nvarchar] (50) NULL ,
[disabled] [bit] NULL ,
[categoryid] [nvarchar] (50) NULL ,
[da*tered] [datetime] NULL ,
[sponsoredby] [nvarchar] (500) NULL
) ON [PRIMARY]
GO
----------------- END TABLEs DEFN --------------------