Results 1 to 2 of 2

Thread: Challenging Trigger

  1. #1
    Join Date
    Apr 2005
    Posts
    1

    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

  2. #2
    Join Date
    Nov 2002
    Location
    DE
    Posts
    246
    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
  •