-
Challenging Trigger
Dear all, i have a challenging problem in writing a trigger here,,
here is the database script
-----------------------SCRIPT------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Categories]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Categories]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Categories_Properties]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Categories_Properties]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Properties]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Properties]
GO
CREATE TABLE [dbo].[Categories] (
[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ParentID] [int] NULL ,
[CategoryDescription] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MainCategory] [bit] NOT NULL ,
[HavePhoto] [bit] NOT NULL ,
[MaxPhotos] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[Categories_Properties] (
[Categories_PropertiesID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryID] [int] NOT NULL ,
[PropertyID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Properties] (
[PropertyID] [int] IDENTITY (1, 1) NOT NULL ,
[PropertyName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
------------------------END SCRIPT--------------------------
Categories and Categories_Properties are joined by CategoryID
Properties and Categories_Properties are joined by PropertyID
there is a relation between the table Categories and itself, the PrimaryKey is CategoryID, the ForiegnKey is ParentID
the problem is i need a trigger to fire when adding a record in Categories_Properties to add this property to all the children for this Category, for eexample if the categories tree
Autos >> Cars >> FIAT
when adding the Property BRAND to to Autos, this Property had to be inserted to Cars and FIAT too, i wrote some trigger but it adds this property to just the direct children, just to cars,
How to update it to include all the children in all levels till the leaf ones??
here is what i wrote
-----------------------Trigger-------------------------
CREATE TRIGGER [inherete_property] ON [dbo].[Categories_Properties]
FOR INSERT
AS
declare @CatID integer,@PropID integer
--set @CatID=select CategoryID from inserted
set @PropID=(select top 1(PropertyID) from inserted)
DECLARE cat_cursor CURSOR FOR
select CategoryID from Categories where ParentID in (select CategoryID from inserted)
open cat_cursor
fetch next from cat_cursor
into @CatID
WHILE @@FETCH_STATUS = 0
BEGIN
insert into categories_Properties(CategoryID,PropertyID) values (@CatID,@PropID)
FETCH NEXT FROM cat_cursor
INTO @CatID
END
CLOSE cat_cursor
DEALLOCATE cat_cursor
-----------------------End Trigger---------------------
thank you all
-
Have a look at BOL and check the topic Expanding Hierarchies. It shows a technique for solving your problem.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|