creating ParentChild tables using ONLY scripts?

[alfredjp]

forgive my ignorance, but could anyone help me how to create a "parent-child" table using nothing but SQL Server Scripts? and be able to enforce Referential Integrity by an "automatic" delete

the problem is that my script below doesn't seem to work...

CREATE TABLE ORGANIZATION (
       ORGANIZATION_ID      int NOT NULL,
       ORGANIZATION_PARENT_ID int NOT NULL,
       ORGANIZATION_NAME    nvarchar(50) NOT NULL,
       ORGANIZATION_LEVEL   smallint NOT NULL,
       PRIMARY KEY (ORGANIZATION_ID), 
       FOREIGN KEY (ORGANIZATION_PARENT_ID)
          REFERENCES ORGANIZATION (ORGANIZATION_ID)
            ON DELETE CASCADE
            ON UPDATE NO ACTION

)
go

am i doing something wrong? either technically, or is there a flaw in my understading?

 

[zhavic]

How many levels has your parent-child table ?
I mean that ORGANIZATION has its GILD-ORGANIZATION, but this GILD-ORGANIZATION
can has its own GILD-ORGANIZATION, etc.
So it looks like this:

Organization_1
Organization_11
Organization_12
Organization_121
Organization_13
Organization_2
Organization_21
Organization_211
Organization_2111
...

I think It can't be done with 'REFERENCES' statement because
server it recognize as cyclic or myltiple cascade paths.

I post there some examples, but there may be better solution.

Create trigger like this, it make recursion and go throught all depended rows, but nesting can have only 32 levels
Also you must set RECURSIVE_TRIGGERS option to ON for your database

/* delete trigger */
CREATE TRIGGER org_delete ON ORGANIZATION
	FOR DELETE
	AS

	/* all recursions must have 'end condition' and this is it ( if there are now rows to delete, than nothing do */
	IF ( SELECT COUNT(*) 
		 FROM deleted
		 INNER JOIN ORGANIZATION ON ORGANIZATION.ORGANIZATION_PARENT_ID = deleted.ORGANIZATION_ID
	   ) > 0

		DELETE ORGANIZATION
			FROM deleted
			WHERE ORGANIZATION.ORGANIZATION_PARENT_ID = deleted.ORGANIZATION_ID

If you can't set RECURSIVE_TRIGGERS option to ON
just create tha same two triggers with different names,
for example 'org_delete1' and 'org_delete2'

----------------------------------------------------

Update trigger depends on what you are want to update
This example updates ORGANIZATION_LEVEL for all children organizations and childrens children organiztions etc.

CREATE TRIGGER org_update ON ORGANIZATION
	FOR update
	AS

	/* all recursions must have 'end condition' and this is it ( if there are now rows to update, than nothing do */
	IF ( SELECT COUNT(*) 
		 FROM inserted
		 INNER JOIN ORGANIZATION ON ORGANIZATION.ORGANIZATION_PARENT_ID = inserted.ORGANIZATION_ID
					/* must update only that rows, that has different values, */
					/* because if not, than recursion will go to 'infinite' loop ( but stops at 32 level ) */
					AND ORGANIZATION.ORGANIZATION_LEVEL != inserted.ORGANIZATION_LEVEL
	   ) > 0

		UPDATE ORGANIZATION SET ORGANIZATION_LEVEL = inserted.ORGANIZATION_LEVEL
			FROM inserted
			WHERE ORGANIZATION.ORGANIZATION_PARENT_ID = inserted.ORGANIZATION_ID
				AND ORGANIZATION.ORGANIZATION_LEVEL != inserted.ORGANIZATION_LEVEL

-------------------------------------------------------

OR use something like this ( it gets all rows that depends on that is deleted and than delete it )
This is slower, more dificult, but has no limitation on 32 levels

/* for example - trigger for delete */
CREATE TRIGGER org_delete ON ORGANIZATION
	FOR DELETE
	AS

	/* create temporary table */
	CREATE TABLE #referenced_rows ( ORGANIZATION_ID int )

    /* ID of rows, that are actualy being deleted */
	INSERT INTO #referenced_rows 
		SELECT ORGANIZATION_ID FROM deleted

	/* find all depended rows */
	WHILE @@ROWCOUNT > 0
	BEGIN
	   INSERT INTO #referenced_rows
	       SELECT ORGANIZATION.ORGANIZATION_ID
	          FROM ORGANIZATION
	          INNER JOIN #referenced_rows ON #referenced_rows.ORGANIZATION_ID = ORGANIZATION.ORGANIZATION_PARENT_ID
	          WHERE ORGANIZATION.ORGANIZATION_ID NOT IN ( SELECT ORGANIZATION_ID FROM #referenced_rows )
	END
	
	DELETE FROM ORGANIZATION
		WHERE ORGANIZATION_ID IN ( SELECT ORGANIZATION_ID FROM #referenced_rows )

----------------------------------------
For update there must be some changes, because it depends of
what you are want to update

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.