Cascade UPDATE and Delete - SQL Server Forums

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)

Cascade UPDATE and Delete

Author

Topic

Danny4003
Starting Member

40 Posts

Posted - 2007-01-09 : 13:08:31

Can anybody explain to me...
If you could set Cascade update and Delete
then update a one field in a table.....
then why does it not update all related fields???

Please can anybody give me some insight
Thanx in advance,

Danny D.

Kristen
Test

22859 Posts

Posted - 2007-01-09 : 13:14:27

Well I think SQL Server will update the relevant column in all the related rows in the "child" tables!

So probably best you post some DDL for your tables, and the DML for the Update you are trying to do, and a description of what is going wrong and folk here can make suggestions.

Kristen

Danny4003
Starting Member

40 Posts

Posted - 2007-01-09 : 13:28:24

CREATE TABLE [TractsTbl] (
[TractID] [int] PRIMARY KEY IDENTITY(1,1)NOT NULL ,
[ProjectID] [int] NULL ,
[TractNumber] [int] NOT NULL ,
[Acreage] [numeric](12, 3) NULL ,
[Location] [varchar](50) NOT NULL ,
[County] [varchar] (50) NULL ,
[State] [int] NULL ,
[Description] [text] NULL ,
[TractImg] [int] NULL ,
[TractCount] [int] NULL ,
[Status] [int] NULL ,
[LocationType] [varchar] (50) NULL,
CONSTRAINT Status_FK1 FOREIGN KEY(Status) REFERENCES StatusTbl (StatusID),
CONSTRAINT State_FK2 FOREIGN KEY (State) REFERENCES StatesTbl (StatesID),
CONSTRAINT Project_FK1 FOREIGN KEY (ProjectID)REFERENCES ProjectTbl (ProjectID)
)

--------------------------------------------
------------------------------------------------------
--CREATE BEFORE (LOOKUP TABLE)
CREATE TABLE [StatusTbl] (
[StatusID] [int] PRIMARY KEY IDENTITY(1,1)NOT NULL ,
[Status] [varchar] (25) NOT NULL
)
INSERT INTO StatusTbl VALUES('Open')
INSERT INTO StatusTbl VALUES('Closed')
INSERT INTO StatusTbl VALUES('Limbo')
SELECT * FROM StatusTbl

--LOOKUP TABLE
CREATE TABLE [StatesTbl] (
[StatesID] [int]PRIMARY KEY IDENTITY(1,1) NOT NULL ,
[States] [varchar] (25) NOT NULL
)
INSERT INTO StatesTbl VALUES('Nebraska')
INSERT INTO StatesTbl VALUES('Alabama')
INSERT INTO StatesTbl VALUES('Texas')
INSERT INTO StatesTbl VALUES('Oklahoma')
select * from StatesTbl
--------------------------------------------

CREATE TABLE [ProjectTbl] (
[ProjectID] [int]PRIMARY KEY IDENTITY(1,1) NOT NULL ,
[ProjectName] [varchar] (50) NULL ,
[County_Parish] [varchar] (50) NULL ,
[StateID] [int] NULL ,
[County_Parish_Seat] [varchar] (50) NULL ,
[Est_Totlal_Acres] [numeric](12, 3) NULL ,
[Project_Start_Date] [datetime] NULL ,
[Project_Billing_Date] [datetime] NULL ,
[StatusID] [int] NULL ,
[AccountNumber] [varchar] (50) NULL,
CONSTRAINT State_FK1 FOREIGN KEY (StateID) REFERENCES StatesTbl (StatesID)
)

--------------------------------------------
--LOOKUP TABLE
CREATE TABLE [OwnerType] (
[OwnTypeID] [int]PRIMARY KEY IDENTITY (1,1) NOT NULL ,
[OwnType] [varchar] (50) NULL
)
INSERT INTO OwnerType Values('LandMan')
INSERT INTO OwnerType Values('Accountant')
INSERT INTO OwnerType Values('Inspector')
INSERT INTO OwnerType Values('Boss Man')
Select * from OwnerType
----------------------------------------------

CREATE TABLE [OwnersTbl] (
[OwnerID] [int]PRIMARY KEY IDENTITY(1,1) NOT NULL ,
[ProjectID][int]Null,
[OwnerType] [int] NULL ,
[TractNumber] [int] NULL ,
[Name] [varchar] (50) NULL ,
[Spouse] [varchar] (50) NULL ,
[Address] [varchar](255) NULL ,
[PhoneNumber] [char](25) NULL ,
[Comments] [text] NULL,
CONSTRAINT Project_FK2 FOREIGN KEY(ProjectID)REFERENCES ProjectTbl (ProjectID),
CONSTRAINT OwnerType_FK1 FOREIGN KEY (OwnerType)REFERENCES OwnerType (OwnTypeID)
)

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

CREATE TABLE [RecTypeTbl] (
[RecTypeID] [int] IDENTITY (1, 1) PRIMARY KEY NOT NULL ,
[RecType] [varchar] (50) NULL ,
)

INSERT INTO RecTypeTbl Values('Offical Records')
INSERT INTO RecTypeTbl Values('Deed Records')
INSERT INTO RecTypeTbl Values('Probate Minutes')
INSERT INTO RecTypeTbl Values('Mortgages')
INSERT INTO RecTypeTbl Values('Conveyances')
CREATE TABLE [InstTypeTbl] (
[InstTypeID] [int] IDENTITY (1, 1) PRIMARY KEY NOT NULL ,
[InstType] [varchar] (50) NULL ,
)
INSERT INTO InstTypeTbl Values('Offical Records')
INSERT INTO InstTypeTbl Values('Deed Records')
INSERT INTO InstTypeTbl Values('Probate Minutes')
INSERT INTO InstTypeTbl Values('Mortgages')
INSERT INTO InstTypeTbl Values('Conveyances')
--DROP TABLE COnVEYANCETBL

CREATE TABLE [ConveyanceTbl] (
[ConveyanceID] [int]PRIMARY KEY IDENTITY (1,1) NOT NULL ,
[TractID] [int] NULL,
[ProjectID] [int] NULL,
[Volume] [varchar] (50) NULL ,
[Page] [varchar](25) NULL ,
[RecType] [int] NULL ,
[InstType] [int] NULL ,
[ExeDate] [varchar] (50) NULL ,
[FileDate] [varchar] (50) NULL ,
[Intent] [varchar] (50) NULL ,
[Details] [text] NULL ,
[Instrument] [varchar] (50) NULL ,
[InstCount] [int] NULL,
CONSTRAINT TractID_FK3 FOREIGN KEY (TractID)REFERENCES TractsTbl (TractID),
CONSTRAINT ProjectID_FK3 FOREIGN KEY (ProjectID)REFERENCES ProjectTbl (ProjectID),
CONSTRAINT RecType_FK1 FOREIGN KEY (RecType) REFERENCES RecTypeTbl (RecTypeID),
CONSTRAINT InstType_FK1 FOREIGN KEY (InstType)REFERENCES InstTypeTbl (InstTypeID)
)

INSERT INTO ConveyanceTbl Values('2','5','C100','99-105','1','3','12/05/1999','02/22/1998','Sale','N/A','N/A','1')
INSERT INTO ConveyanceTbl Values('3','5','D55','1-12','2','2','05/06/1998','02/22/1998','Own','N/A','N/A','6')
INSERT INTO ConveyanceTbl Values('4','4','E65','35-55','3','1','04/05/2000','04/05/1999','Keep','N/A','N/A','5')
INSERT INTO ConveyanceTbl Values('5','2','A102','30-50','4','5','05/06/2001','09/08/2000','Rent','N/A','N/A','8')
INSERT INTO ConveyanceTbl Values('6','1','B200','1-15','5','4','08/09/2001','05/06/2001','Lease','N/A','N/A','2')
INSERT INTO ConveyanceTbl Values('7','1','F566','2-10','5','4','09/08/2000','04/05/2000','Mine','N/A','N/A','3')
INSERT INTO ConveyanceTbl Values('8','1','A101','25-29','4','5','09/08/2000','04/05/1999','Mine','N/A','N/A','4')
INSERT INTO ConveyanceTbl Values('6','1','G65','25-65','3','2','08/09/2001','09/08/2000','Lease','N/A','N/A','7')
INSERT INTO ConveyanceTbl Values('3','5','I56','55-65','1','3','05/06/2001','04/05/1999','Rent','N/A','N/A','9')
INSERT INTO ConveyanceTbl Values('4','4','J300','25-30','1','4','01/05/2003','08/09/2001','Own','N/A','N/A','10')
INSERT INTO ConveyanceTbl Values('2','5','K200','1-20','3','5','02/22/1998','1/12/1998','Sale','N/A','N/A','11')

Select * from ProjectTbl

/*
INSERT INTO ProjectTbl Values('TestProj1','Jasper','3','Jasper Seat','123.545','12/12/1992','12/25/2000','3','123456789')
INSERT INTO ProjectTbl Values('TestProj2','Alabama','2','Alabama Seat','321.454','05/05/2002','05/25/2002','2','321654987')
INSERT INTO ProjectTbl Values('TestProj3','Oklahoma','4','Okla Seat','546.321','02/02/2003','02/25/2003','1','789456123')
INSERT INTO ProjectTbl Values('TestProj4','Corn Husker','1','Neb Seat','458.213','09/20/2004','09/30/2004','3','956874612')
*/
Select * from TractsTbl Order By TractImg ASC
/*
INSERT INTO TractsTbl Values('5','123458','273.161','Oklahoma','Oklahoma','4','This Tract has 50% of mineral to Tot.Acres','14','2','1','N/A')
INSERT INTO TractsTbl Values('5','231654','273.161','Oklahoma','Oklahoma','4','This Tract has the other 50% to ToT.Acres','12','2','1','N/A')
INSERT INTO TractsTbl Values('4','898745','458.213','Nebraska','Corn Husker','1','100% belongs to this Tract','10','1','3','N/A')
INSERT INTO TractsTbl Values('2','658987','321.454','Alabama','Alabama','2','100% belongs to this Tract','2','1','2','N/A')
INSERT INTO TractsTbl Values('1','545698','41.181','Texas','Jasper','3','75% belongs to this Tract','6','3','3','N/A')
INSERT INTO TractsTbl Values('1','658985','41.181','Texas','Jasper','3','75% belongs to this Tract','8','3','3','N/A')
INSERT INTO TractsTbl Values('1','478956','41.181','Texas','Jasper','3','75% belongs to this Tract','4','3','3','N/A')
*/
/*
INSERT INTO OwnersTbl Values('5','1','123458','Dave Trahan','N/A','312B Knollwood Dr. Lafayette,La 70506','337-346-5566','This is the LandMan for this Tract')
INSERT INTO OwnersTbl Values('5','4','231654','Jarred Landry','N/A','154 Circle Rd. Dallas,TX 54668','504-258-9689','This is the Boss of the LandMan for this Tract')
INSERT INTO OwnersTbl Values('4','4','898745','Danny Dubroc','Constance Dubroc','1065 Ellen Rd. Ville Platte,La 70586','337-290-0880','This is the Boss of 100% for this Tract')
INSERT INTO OwnersTbl Values('2','4','658987','JoJo Lodrigue','N/A','254 Ballon Dr. Port Barre,La 70508','337-564-5456','This Boss owns 100% of this Tract')
INSERT INTO OwnersTbl Values('1','1','545698','Todd Newbanks','Jackie Newbanks','215 Lexman Dr. Opelousas,La 70570','337-564-2525','This LandMan owns 75% of this Tract')
INSERT INTO OwnersTbl Values('1','2','658985','Jack Harbour','N/A','222 Jake Dr. Jasper,TX 54667','504-856-8080','This Accountant owns 75% of this Tract')
INSERT INTO OwnersTbl Values('1','3','478956','Nick Orwell','Melissa Orwell','54 Tom Green,TX 54665','504-956-5454','This Inspector owns the last 75% of this Tract')
*/

Select * from StatusTbl
Select * from StatesTbl
Select * from OwnerType
Select * from OwnersTbl
Select * from TractsTbl
Select * from ProjectTbl
Select * from ConveyanceTbl
Select TractsTbl.TractNumber,ConveyanceTbl.Volume,ConveyanceTbl.Page,TractsTbl.Acreage,TractsTbl.Location,StatesTbl.States,OwnersTbl.Comments,ProjectTbl.ProjectName,OwnersTbl.[Name],OwnerType.OwnType,
OwnersTbl.Spouse, OwnersTbl.Address, OwnersTbl.PhoneNumber,ProjectTbl.County_Parish from TractsTbl
INNER JOIN ProjectTbl ON (TractsTbl.ProjectID = ProjectTbl.ProjectID)
INNER JOIN OwnersTbl ON (OwnersTbl.ProjectID = ProjectTbl.ProjectID) AND (OwnersTbl.TractNumber = TractsTbl.TractNumber)
INNER JOIN StatesTbl ON (TractsTbl.State = StatesTbl.StatesID)
INNER JOIN OwnerType ON (OwnerType.OwnTypeID = OwnersTbl.OwnerType)
INNER JOIN ConveyanceTbl ON (ConveyanceTbl.TractID = TractsTbl.TractID)
ORDER BY CASE WHEN ISNUMERIC(Volume) <> 0 THEN RIGHT(REPLICATE(' ', 5) + Volume, 5) ELSE Volume END

Okay what I want to see happen is when you update the TractNumber in the TractsTbl that the TractNumber should
be updated in the other Tables. Or should the TractID be Updated in All related Tables...

Thanx in advanced

Danny

Kristen
Test

22859 Posts

Posted - 2007-01-09 : 13:36:33

The only thing that I can see which is in the running here is:


CREATE TABLE [ConveyanceTbl] (
...
[TractID] [int] NULL,
...
CONSTRAINT TractID_FK3 FOREIGN KEY (TractID)REFERENCES TractsTbl (TractID), 
...

so only TractID is the subject of an FKey, and not TractNumber, so TractNumber is not going to be updated to match (the fact that it shares a column name doesn't autoMagically cause it to be updated, it has to be part of an FKey constraint for that to happen

Your FKey statements also do NOT have any CASCADE action specified; the default is for an error to be raised, rather than for the change to be cascaded, so you'll need to make a change there too.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources