Challenge Problem in writing a Trigger

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Challenge Problem in writing a Trigger

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-04-18 : 07:12:31

Mohamed Hasan writes "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"

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-04-18 : 21:40:06

~~NO CURSORS~~
Having said that, the reason your trigger is inserting into only one table is because that is all you've written it to do:

WHILE @@FETCH_STATUS = 0
BEGIN
insert into categories_Properties(CategoryID,PropertyID) values (@CatID,@PropID)

you need to add another line to this for each table you intend to insert into:

WHILE @@FETCH_STATUS = 0
BEGIN
insert into categories_Properties(CategoryID,PropertyID) values (@CatID,@PropID)
insert into <whateverothertableyouwant>(CategoryID,PropertyID) values (@CatID,@PropID)

!!BUT!! you would be better off ditching the trigger with the cursor and writing this set-based. There are about a bajillion examples of the right way to do this right in this forum. Just search for 'insert trigger'...
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-04-18 : 21:49:39

try this link: http://www.seventhnight.com/treestructs.asp

oh by the way, lose the cursor

--------------------
keeping it simple...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-18 : 22:41:17

This seems to work:


CREATE TRIGGER [inherete_property] ON [dbo].[Categories_Properties] 
FOR INSERT
AS

declare @tb table (id int, lev int)
declare @lev int
	,@propertyid int

select	top 1
	@lev = 0
	,@propertyID = PropertyID
From	inserted


insert @tb (id, lev)
Select c.CategoryID, @lev
from	Categories c
JOIN	inserted i ON c.ParentID = i.CategoryID

--loop once for each level of Parent/CategoryIDs
while @@Rowcount > 0
Begin
	Select @lev = @lev + 1

	insert @tb
	Select CategoryID, @lev
	from	Categories c
	JOIN	@tb t ON c.ParentID = t.id
			and lev < @lev

	--avoid circular references by not re-adding the same CategoryID
	left join @tb excl
		ON c.CategoryID = excl.id
	where	excl.id is NULL
End

insert Categories_Properties (CategoryID, PropertyID)
Select id, @PropertyID
From	@tb a
--EDIT:
Where not exists (select * from Categories where CategoryID = a.id and PropertyID = @PropertyID)


GO

Here is my sample data:


set nocount on
insert Categories (
	[CategoryName],
	[ParentID],
	[MainCategory],
	[HavePhoto]) 
select	'Category1'
	,null
	,1
	,0 union all
select	'Category2'
	,1
	,0
	,0 union all
select	'Category3'
	,2
	,0
	,0 union all
select	'Category4'
	,3
	,0
	,0 union all
select	'Category5'
	,4
	,0
	,0

insert [Properties] ([PropertyName])
values ('Property1')

GO

insert Categories_Properties (CategoryID, PropertyID)
select 1,1

Select * from Categories_Properties
select * from Categories

Be One with the Optimizer
TG

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-04-19 : 08:08:13

TG's deal will work,
but the loop table is another way of saying "cursor." The only time you want to use a loop or cursor (wierd,one-off things aside) is when you need to evaluate each row individually and change data for each row. This is just an insert/update kind of thing that you are doing, and as such should be done without loops or cursors.
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-19 : 08:24:09

For an indefinate number of nested hierarchal relationships, this is one of the most efficient ways to accomplish it. There is just one loop itteration per nesting level, so even a 10 levels deep, that's pretty quick. Mohamed's original cursor was looping through every individual record and just at the 1st nested level. So Andy, if you've got a better way, by all means let me know.

Be One with the Optimizer
TG

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-04-19 : 17:08:20

Hey TG,
Yup, you are right on with the single loop/multi nest thing, and you are also correct that this is very efficient (especially compared to the cursor deal). Didn't mean to ruffle any feathers, just meant to point out to Mohamed that a loop and a cursor are similar. After re-reading my post, I can see that I didn't do a good job of typing out what I meant to say!

Matter of fact, now that I re-read the original post, I missed the @propid select (top 1) alltogether! DOH!
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-19 : 17:34:10

No feathers ruffled, Andy. The funny thing is Mohamed is probably long gone and you and I are the only ones worrying about his trigger :)

Be One with the Optimizer
TG

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-04-19 : 18:39:30

That's what makes this so much fun! You know, I keep looking at this, and for some reason one or two brain cells in the back of my head say this can be done without the loop.
Lord only knows why!
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources