uninstall SQL: cascade

Showing posts with label cascade. Show all posts

Monday, March 19, 2012

Cascading Triggers

Hi,
I have 3 Tables with After Update Triggers.
They all cascade like City -> Province -> State (as a simplified example)
If I Update a bit (A) in City, a Trigger Sets a bit (B) in Province.
If I Update the bit B in Province, a Trigger Sets a Bit (C) in State.
This works.
However:
If I Set A, a trigger on table City sets B BUT a trigger on table Province
DOESN'T in turn Set C in table State.
So: Individually the Triggers work, but they don't Cascade their action.
Any advise?
TIA,
MichaelHi Michael,
I think I'm having the same problem. I'm hoping to take action via a
trigger on child records when the parent is deleted, but the child table's
trigger doesn't seem to fire. Hopefully someone has a suggestion.
Rgds,
Bill
"Michael Maes" <michael@.merlot.com> wrote in message
news:5BEB7F99-288B-48A8-98DE-F2FBD22AFE75@.microsoft.com...
> Hi,
> I have 3 Tables with After Update Triggers.
> They all cascade like City -> Province -> State (as a simplified example)
> If I Update a bit (A) in City, a Trigger Sets a bit (B) in Province.
> If I Update the bit B in Province, a Trigger Sets a Bit (C) in State.
> This works.
> However:
> If I Set A, a trigger on table City sets B BUT a trigger on table Province
> DOESN'T in turn Set C in table State.
> So: Individually the Triggers work, but they don't Cascade their action.
> Any advise?
> TIA,
>
> Michael
>|||There is a server option "Allow triggers to be fired which fire other
triggers (nested triggers)". Here is the description copied from BO:
1. Expand a server group.
2. Right-click a server, and then click Properties.
3. Click the Server Settings tab.
4. Under Server behavior, select or clear the Allow triggers to be
fired which fire other triggers (nested triggers) check box.
For more information, see Books Online, article "Using Nested Triggers"|||Hi Sergei,
Thanks for your advise!
I just found an article on this:
http://www.sqlservercentral.com/col.../triggers_1.asp
Nested and Recursive Triggers
Nested triggers are triggers that fire due to actions of other triggers.
For instance, I delete a row from TableA. A trigger on TableA fires to
delete rows from TableB. Because I'm deleting rows from TableB, a trigger
fires on TableB to record the deletes. This is an example of a nested
trigger. As we've talked about, SQL Server 7.0 doesn't support cascading
updates and deletes based on foreign key relationships. Therefore, if we
want to relate our data we can't use DRI and must resort to triggers or some
application oversight. Let's say we've got a cascade delete to fire down 3
or 4 tables. Nested triggers are our answer. Our delete on a particular
table fires a trigger which deletes rows for another table, which fires a
trigger, so on and so forth. SQL Server 7 and 2000 support up to 32 levels
of nested triggers.
Now the big question is, does my SQL Server allow nested triggers? That's
an easy question to answer. It's on by default, but in Query Analyzer we ca
n
issue the following command:
EXEC sp_configure 'nested triggers'
If your run_value is set to 0, your server isn't allowing nested triggers.
If it's set to 1, nested triggers may fire. This is a server wide setting.
Now, to change your setting, once again use the sp_configure command:
To turn off nested triggers:
EXEC sp_configure 'nested triggers', 0
RECONFIGURE
To turn on nested triggers:
EXEC sp_configure 'nested triggers', 1
RECONFIGURE
"Sergei Almazov" wrote:

> There is a server option "Allow triggers to be fired which fire other
> triggers (nested triggers)". Here is the description copied from BO:
> 1. Expand a server group.
> 2. Right-click a server, and then click Properties.
> 3. Click the Server Settings tab.
> 4. Under Server behavior, select or clear the Allow triggers to be
> fired which fire other triggers (nested triggers) check box.
> For more information, see Books Online, article "Using Nested Triggers"
>|||I wonder why the defualt setting is off for this. (Protecting us from
potentially faulty programming perhaps?) ;-) Also, seems like it would be
handy to be able to turn it on per table or database, rather than at the
server level.
In my case where I'm just trying to perform some actions first with the
information in detail records that will be deleted, would it be better to
turn off the cascade delete for the related table and handle deleting of the
detail records in the delete trigger of the master table, or is it better to
allow nested triggers?
"Sergei Almazov" <almazik@.ukr.net> wrote in message
news:1127473954.892200.319820@.g43g2000cwa.googlegroups.com...
> There is a server option "Allow triggers to be fired which fire other
> triggers (nested triggers)". Here is the description copied from BO:
> 1. Expand a server group.
> 2. Right-click a server, and then click Properties.
> 3. Click the Server Settings tab.
> 4. Under Server behavior, select or clear the Allow triggers to be
> fired which fire other triggers (nested triggers) check box.
> For more information, see Books Online, article "Using Nested Triggers"
>|||I wonder why the defualt setting is off for this. (Protecting us from
potentially faulty programming perhaps?) ;-) Also, seems like it would be
handy to be able to turn it on per table or database, rather than at the
server level.
In my case where I'm just trying to perform some actions first with the
information in detail records that will be deleted, would it be better to
turn off the cascade delete for the related table and handle deleting of the
detail records in the delete trigger of the master table, or is it better to
allow nested triggers?
"Sergei Almazov" <almazik@.ukr.net> wrote in message
news:1127473954.892200.319820@.g43g2000cwa.googlegroups.com...
> There is a server option "Allow triggers to be fired which fire other
> triggers (nested triggers)". Here is the description copied from BO:
> 1. Expand a server group.
> 2. Right-click a server, and then click Properties.
> 3. Click the Server Settings tab.
> 4. Under Server behavior, select or clear the Allow triggers to be
> fired which fire other triggers (nested triggers) check box.
> For more information, see Books Online, article "Using Nested Triggers"
>|||Hi Bill,
What scares me is that someone or something else can disable your nested
triggers because it's DataServer-Wide :-(((
"Bill Hicks" wrote:

> I wonder why the defualt setting is off for this. (Protecting us from
> potentially faulty programming perhaps?) ;-) Also, seems like it would be
> handy to be able to turn it on per table or database, rather than at the
> server level.
> In my case where I'm just trying to perform some actions first with the
> information in detail records that will be deleted, would it be better to
> turn off the cascade delete for the related table and handle deleting of t
he
> detail records in the delete trigger of the master table, or is it better
to
> allow nested triggers?
> "Sergei Almazov" <almazik@.ukr.net> wrote in message
> news:1127473954.892200.319820@.g43g2000cwa.googlegroups.com...
>
>

Sunday, March 11, 2012

Cascading Deletes

When I setup a relationship in Access I can specify that Primary Key deletes cascade down to the Forgien Key. So when I delete an Order Header it cleans up all the items in the Order Details table for me automatically.

Can I get this same functionality in SQL Server 7 without having to write triggers or are triggers the only way?

thanks
dogNo, trigger is not the only way: you could create a procedure that does this for you or, when you create the table or add the constraint specify the on delete option to cascade (see BOL, create table).|||What!?!?!?

SQL Server has cascading deletes! The easiest way to manage them is through the Relationships tab of the Properties dialog box in the Enterprise Manager table design form.

Triggers are NOT necessary for standard cascading.|||Cascade? Like a waterfall?

Has anyone scanned the landscape for a merry-go-round?

:D

Wholly disconnected ramblings bart man...

Seriously...be careful with cascading...should be no need...

I never liked messing with keys...

damn surrogates...

To me, if a key changes, then it's a new entity...or the key is defined improperly...

You lose all history...|||Well, you certainly aren't alone in your aversion to cascading relationships, but I've never had a problem with them.

Disconneted ramblings...

...many...non-sequiturs...

Wish I had a key for elipsis so I didn't have to hit the period key three times...

Must...complete...sentence.... damn!|||Seriously...be careful with cascading...should be no need...

pretty dogmatic Mr. Kaiser, what's your solution to my previous example, if in fact I don't care about history? Say I have OrderNumber as the Primary key in the OrderHeader table and OrderNumber as a Foreign key in the OrderDetails table, how is this a misconfigured key arrangement?|||Wow...dogmatic...

Cool...

You want to cascade...knock yourself out...|||SQL Server has cascading deletes! The easiest way to manage them is through the Relationships tab of the Properties dialog box in the Enterprise Manager table design form.
That seems to be the logical place for it, however the only options I have are:
Check existing data on creation
Enable relationship for INSERT and UPDATE
Enable relationship for replication

maybe a version difference :confused:|||You want to cascade...knock yourself out...
That's your solution?

WOW......
COOL DUDE.......
THANKS FOR THE MOST RIGHTOUS EXPLAINATION.....................
ATS AWESOME................|||Enable relationship for INSERT and UPDATE

Seems the SQL Server developers were too lazy to say "Enable relationship for INSERT, UPDATE and DELETE. My bad, I read the help screen and found that DELETE is included with this option, however, it doesn't give me the desired result. It actually disables Primary key deletion if Forgein key dependants exist.

Again, do I need to write triggers to accomplish my goal here :confused:

I want all Forgein keys associated with a Primary key to be deleted when I delete the PK record :D|||See attached screenshot.|||Yup, must be an update that I don't have in my v7 version, guess I'll find out what it means to be trigger happy. Thanks blindman.|||'bout time to upgrade, isn't it?|||cascade update\delete is "New" to sql 2000 v :eek:

and you dont have to create database devices anymore.. :D

Thursday, March 8, 2012

Cascade updates

I ran a bulk update on a SQL database for a range between dateswhere there
were trigger and possibly foreign keys. the parent updated fine but it
changed multiple child records although they were not relatedHi
Can you show us DDL + sample data + your query?
"Dangerfield" <Dangerfield@.discussions.microsoft.com> wrote in message
news:2E57BC94-A3B0-46CA-9349-C2DEB7C79F46@.microsoft.com...
> I ran a bulk update on a SQL database for a range between dateswhere there
> were trigger and possibly foreign keys. the parent updated fine but it
> changed multiple child records although they were not related

Cascade Update and Delete Related Fields..

I am building tables using the database diagram with enterprise manager. One of the options available is to "Cascade Update and Delete Related Fields". I think this means that if I change a PK Identifier in a table all related FK's will be updated to the new value and if I delete the PK all related FK records will be deleted. Is this correct? Is this a good idea to do something like this?

I have always handled what needs to be changed using SP's following business rules. A friend has been preaching the use of the "Cascade Update and Delete Related Fields" functionality provided with the database.

Please let me know what you think.

ThanksAs you already mention, I think this is a matter of opinion. The advantage for cascading is that you don't have to do any checking and programming to avoid data inconstency. Disadvantage is that you don't have any control about the updates/deletes of the related records. For the manual programming through sp's it's offcourse the other way around.
In other words: you have to see in every situation what's the best solution.

cascade update / foreign key

Hi!
For the sake of simplicity, I have three tables, Employee, Department and
Work

Employee >-- Department
\ /
\ /
^ ^
Work

The Work table have two columns, empno and depno and consists that the
employee has worked on another department.

Here is my scripts:
create table employee (empno int not null primary key, depno int not null)
create table department (depno int not null primary key)
create table work (empno int not null, depno int not null)

alter table employee add constraint fk_employee_department foreign key
(depno)
references department (depno)
on update cascade

alter table work add constraint fk_work_employee foreign key (empno)
references employee (empno)
on update cascade

alter table work add constraint fk_work_department foreign key (depno)
references department (depno)
on update cascade

My problem is the last command. SQL Server responds:
Server: Msg 1785, Level 16, State 1, Line 1
Introducing FOREIGN KEY constraint 'fk_work_department' on table 'work' may
cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON
UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

But I want the depno in the work table to be updated when a department.depno
changes a value.

Does anyone have a suggestion on how to overcome this problem?

Thanks in advance

Best regards,
Gunnar Vyenli
EDB-konsulent as
NORWAYHi,

SQL statement that you had provided is generating circular refrence in
updates
Workaround is to use triger instead of foreign key integrity.

Comment the one of foriegn key sql and use triger.

Note this trigger are not tested. but is only for your reference and
other work around for problem.

/*alter table work add constraint fk_work_employee foreign key (empno)
references employee (empno)
on update cascade
go
*/

create trigger trgUpdateEmpNoInDept
on employee
for update
as
set nocount on

declare @.NewEmpno int , @.OldEmpno int

select @.NewEmpno = inserted.empno from inserted
select @.OldEmpno = deleted.empno from deleted

update work set work.empno = @.NewEmpno
where work.empno = @.OldEmpno
if @.@.rowcount=0 or @.@.error<>0
begin
Rollback tran
end

set nocount off
go

create trigger trgCheckEmpExists
on Work
For Update
as

set nocount on

declare @.NewEmpno int
select @.NewEmpno = inserted.empno from inserted

If Not Exists (Select * from employee where empno = @.NewEmpno )
Begin
RAISERROR ('Empno do not exists', 16, 1)
Rollback Tran
End
set nocount off

Thanks & Regards, Amit

"Gunnar Vyenli" <gv@.edbkonsulent.no> wrote in message news:<3f6a24cb$1@.news.broadpark.no>...
> Hi!
> For the sake of simplicity, I have three tables, Employee, Department and
> Work
> Employee >-- Department
> \ /
> \ /
> ^ ^
> Work
> The Work table have two columns, empno and depno and consists that the
> employee has worked on another department.
> Here is my scripts:
> create table employee (empno int not null primary key, depno int not null)
> create table department (depno int not null primary key)
> create table work (empno int not null, depno int not null)
> alter table employee add constraint fk_employee_department foreign key
> (depno)
> references department (depno)
> on update cascade
> alter table work add constraint fk_work_employee foreign key (empno)
> references employee (empno)
> on update cascade
> alter table work add constraint fk_work_department foreign key (depno)
> references department (depno)
> on update cascade
> My problem is the last command. SQL Server responds:
> Server: Msg 1785, Level 16, State 1, Line 1
> Introducing FOREIGN KEY constraint 'fk_work_department' on table 'work' may
> cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON
> UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
> But I want the depno in the work table to be updated when a department.depno
> changes a value.
> Does anyone have a suggestion on how to overcome this problem?
> Thanks in advance
> Best regards,
> Gunnar Vyenli
> EDB-konsulent as
> NORWAY|||Thank you very much!
-Gunnar

"AMIT" <amitb@.zenithinfotech.com> wrote in message
news:f3093a66.0309182236.2313bc10@.posting.google.c om...
> Hi,
>
> SQL statement that you had provided is generating circular refrence in
> updates
> Workaround is to use triger instead of foreign key integrity.
> Comment the one of foriegn key sql and use triger.
> Note this trigger are not tested. but is only for your reference and
> other work around for problem.
> /*alter table work add constraint fk_work_employee foreign key (empno)
> references employee (empno)
> on update cascade
> go
> */
>
> create trigger trgUpdateEmpNoInDept
> on employee
> for update
> as
> set nocount on
> declare @.NewEmpno int , @.OldEmpno int
> select @.NewEmpno = inserted.empno from inserted
> select @.OldEmpno = deleted.empno from deleted
> update work set work.empno = @.NewEmpno
> where work.empno = @.OldEmpno
> if @.@.rowcount=0 or @.@.error<>0
> begin
> Rollback tran
> end
> set nocount off
> go
> create trigger trgCheckEmpExists
> on Work
> For Update
> as
> set nocount on
> declare @.NewEmpno int
> select @.NewEmpno = inserted.empno from inserted
> If Not Exists (Select * from employee where empno = @.NewEmpno )
> Begin
> RAISERROR ('Empno do not exists', 16, 1)
> Rollback Tran
> End
> set nocount off
> go
>
> Thanks & Regards, Amit
> "Gunnar Vyenli" <gv@.edbkonsulent.no> wrote in message
news:<3f6a24cb$1@.news.broadpark.no>...
> > Hi!
> > For the sake of simplicity, I have three tables, Employee, Department
and
> > Work
> > Employee >-- Department
> > \ /
> > \ /
> > ^ ^
> > Work
> > The Work table have two columns, empno and depno and consists that the
> > employee has worked on another department.
> > Here is my scripts:
> > create table employee (empno int not null primary key, depno int not
null)
> > create table department (depno int not null primary key)
> > create table work (empno int not null, depno int not null)
> > alter table employee add constraint fk_employee_department foreign key
> > (depno)
> > references department (depno)
> > on update cascade
> > alter table work add constraint fk_work_employee foreign key (empno)
> > references employee (empno)
> > on update cascade
> > alter table work add constraint fk_work_department foreign key (depno)
> > references department (depno)
> > on update cascade
> > My problem is the last command. SQL Server responds:
> > Server: Msg 1785, Level 16, State 1, Line 1
> > Introducing FOREIGN KEY constraint 'fk_work_department' on table 'work'
may
> > cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or
ON
> > UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
> > But I want the depno in the work table to be updated when a
department.depno
> > changes a value.
> > Does anyone have a suggestion on how to overcome this problem?
> > Thanks in advance
> > Best regards,
> > Gunnar Vyenli
> > EDB-konsulent as
> > NORWAY

Cascade Update

Does anyone know how to do a cascade update in SQL 2005 using the studio manager?

Basically, I have a project table with a status column. If the status is set to 2, then I need to update another table that references the project id.

You can create a foreign key with cascading. For example you have 2 tables: the referenced table tbl1 with projectid as primary key, and the referencing table tbl2 with projectid column:

Alter Table tbl2 Add
Constraint fk_tbl2_projectid
Foreign Key (projectid)
References tbl1(projectid)
On Update Cascade

You can get more information from these links:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_3ied.asp

And

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_04_92ib.asp

Cascade set to null on delete.

Hi all,

I am using SQL Server 2000 and am trying to perform a basic delete query on a table called ClientType. The only child table of this is called Client. The relationship between the tables has a cascade action of cascade update and when I try to perform the delete operation, I get the error "DELETE statement conflicted with COLUMN REFERENCE constraint". The foreign key field accepts nulls and has a defauilt value of null. Now, am I being completely dense or shouldn't the cascade update set the foreign key values to null?

Regards,

Stephen.

Could you please send over the DDL for the table and the constraints, thanks.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de|||

Here you go:
...

CREATE TABLE [dbo].[ClientType] (
[ClientTypeId] [int] IDENTITY (1, 1) NOT NULL ,
[ObjectVersion] [int] NULL ,
[Type] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Client] (
[ClientId] [int] IDENTITY (1, 1) NOT NULL ,
[ObjectVersion] [int] NULL ,
[ClientTypeId] [int] NULL ,
[ReferenceNumber] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Password] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Forename] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Initial] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Surname] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateOfBirth] [datetime] NULL ,
[DateRegistered] [datetime] NULL ,
[Address1] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address2] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address3] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[County] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Postcode] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Telephone1] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Telephone2] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Telephone3] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EMail] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[URL] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Position] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Overview] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InternalOverview] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comments] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CV] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HoldingCompanyName] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[ClientType] WITH NOCHECK ADD
CONSTRAINT [PK_ClientType] PRIMARY KEY CLUSTERED
(
[ClientTypeId]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Client] WITH NOCHECK ADD
CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED
(
[ClientId]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ClientType] ADD
CONSTRAINT [DF_ClientType_ObjectVersion] DEFAULT (0) FOR [ObjectVersion]
GO

ALTER TABLE [dbo].[Client] ADD
CONSTRAINT [DF_Client_ObjectVersion] DEFAULT (0) FOR [ObjectVersion],
CONSTRAINT [DF_Client_ClientTypeId] DEFAULT (null) FOR [ClientTypeId]
GO

ALTER TABLE [dbo].[Client] ADD
CONSTRAINT [FK_Client_ClientType] FOREIGN KEY
(
[ClientTypeId]
) REFERENCES [dbo].[ClientType] (
[ClientTypeId]
) ON UPDATE CASCADE
GO

...

Regards,

Stephen.

|||OK, you just defined the Cascade on the update, if you delete a row and the child table contains rows for that parent row and you did not define a cascade delete on the parent table, this error message will come up.

You wil have to add: ON DELETE CASCADE

HTH; Jens Suessmeyer.

http://www.sqlserver2005.de|||

Thanks for the response. I do understand that adding cascade delete would get rid of the error but will that not result in the child rows being deleted? I only want the foreign keys to be set to null, not have the entire related record dropped from the table.

Regards,

Stephen.

|||OK, then you will have to use the ON DELETE SET NULL.

HTH; Jens Suessmeyer.

http://www.sqlserver2005.de|||OK. I gave that a go, but I just get an error "incorrect syntax near the keyword 'SET'".|||

Sorry, I re-read your first post and saw that you are using SQL Server 2k. SET NULL is a new feature for SQL 2k5. In SQL Server 2000 you probably would use no constraint in that case and do the work with triggers, in that case an update trigger.

Sorry for confusing you :-)

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

|||

OK. I'll look into that.

Thanks for your help. Its much appreciated.

Regards,

Stephen.

Cascade parameter and sharepoint 2007

Hi,

I have an issue with my report when deploy to sharepoint 2007. Basically my report have 3 parameters, start date date picker, end date date picker and event dropdown. The data displayed in the event dropdown will depends on the start date and end date specified by user.

The problem is the event dropdown some how get "cached". I have to manually click on apply in sharepoint report veiwer for the event list to be updated. This doesn't happend in Visual Studio and also report server but only happend when I deloyed to sharepoint thus I suspect it's the sharepoint issue.

Anyone encounter similar problem before?

Ok. Seems like it's related to the date picker used by sharepoint 2007. If I manual change the date by editing the date text box, the post back works. But if i change the date using the popup calendar, the post back doesn't work. Can someone from Microsoft confirm that there are issues with the calendar control?

Cascade Inserts/Clone a tree

I am creating a small project management application using SQL Server. There
are three main tables (prjProjects, prjTasks, prjDetails). A project can
have 0 to many tasks and a tasks can have 0 to many details.
Many of our projects are very similar in that they would consist of
basically the same task and detail lists/records. I would like to be able to
"clone" a project using a stored procedure. I will be accessing the tables
from multiple front ends such as MS Access, ColdFusion, and ASP. A stored
procedure would allow me to create a new, cloned project from any of these
front ends.
I am not sure where to start with this. I have searched Google and news
groups without finding much useful information. Do I need to create a cursor
to loop through records and perform inserts or is there a more efficient
method? The tables, significant fields, and records are created below. There
are additional fields for descriptions, dates, etc but if I get the basic
framework, I should be able to include the other fields. I would like to
duplicate the "TITLE" field values in the new records.
Hope I have provided enough info that someone can point me in the right
direction.
Duane Hookom
MS Access MVP
CREATE TABLE [dbo].[prjProjects]
(
[PR_ID] [int] IDENTITY (1, 1) NOT NULL ,
[PR_TITLE] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[prjTasks] (
[TS_ID] [int] IDENTITY (1, 1) NOT NULL ,
[TS_TITLE] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TS_PR_ID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[prjDetails]
(
[DT_ID] [int] IDENTITY (1, 1) NOT NULL ,
[DT_TITLE] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DT_TS_ID] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[prjProjects] WITH NOCHECK ADD
CONSTRAINT [PK_prjProjects] PRIMARY KEY CLUSTERED
(
[PR_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[prjTasks] WITH NOCHECK ADD
CONSTRAINT [PK_prjTasks] PRIMARY KEY CLUSTERED
(
[TS_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[prjDetails] WITH NOCHECK ADD
CONSTRAINT [PK_prjDetails] PRIMARY KEY CLUSTERED
(
[DT_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[prjTasks] ADD
CONSTRAINT [FK_prjTasks_prjProjects] FOREIGN KEY
(
[TS_PR_ID]
) REFERENCES [dbo].[prjProjects] (
[PR_ID]
)
GO
ALTER TABLE [dbo].[prjDetails] ADD
CONSTRAINT [FK_prjDetails_prjTasks] FOREIGN KEY
(
[DT_TS_ID]
) REFERENCES [dbo].[prjTasks] (
[TS_ID]
)
GO
INSERT INTO prjProjects (PR_TITLE) Values('Project to Clone')
GO
INSERT INTO prjTasks (TS_TITLE, TS_PR_ID) Values('Task One', 1)
GO
INSERT INTO prjTasks (TS_TITLE, TS_PR_ID) Values('Task Two', 1)
GO
INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 1 of Task
One',1)
GO
INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 2 of Task
One',1)
GO
INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 3 of Task
One',1)
GO
INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 1 of Task
Two',2)
GO
INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 2 of Task
Two',2)
GO"Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
news:uSbpn1rmGHA.4716@.TK2MSFTNGP04.phx.gbl...
>I am creating a small project management application using SQL Server.
>There are three main tables (prjProjects, prjTasks, prjDetails). A project
>can have 0 to many tasks and a tasks can have 0 to many details.
> Many of our projects are very similar in that they would consist of
> basically the same task and detail lists/records. I would like to be able
> to "clone" a project using a stored procedure. I will be accessing the
> tables from multiple front ends such as MS Access, ColdFusion, and ASP. A
> stored procedure would allow me to create a new, cloned project from any
> of these front ends.
> I am not sure where to start with this. I have searched Google and news
> groups without finding much useful information. Do I need to create a
> cursor to loop through records and perform inserts or is there a more
> efficient method? The tables, significant fields, and records are created
> below. There are additional fields for descriptions, dates, etc but if I
> get the basic framework, I should be able to include the other fields. I
> would like to duplicate the "TITLE" field values in the new records.
> Hope I have provided enough info that someone can point me in the right
> direction.
> --
> Duane Hookom
> MS Access MVP
>
First you should be to add some important constraints. These are my
assumptions of course but the keys will be important here.
ALTER TABLE dbo.prjProjects ALTER COLUMN PR_TITLE NVARCHAR(50) NOT NULL;
ALTER TABLE dbo.prjTasks ALTER COLUMN TS_TITLE NVARCHAR(50) NOT NULL;
ALTER TABLE dbo.prjProjects ADD CONSTRAINT AK1_prjProjects
UNIQUE (PR_TITLE);
ALTER TABLE dbo.prjTasks ADD CONSTRAINT AK1_prjTasks
UNIQUE (TS_TITLE, TS_PR_ID);
ALTER TABLE dbo.prjDetails ADD CONSTRAINT AK1_prjDetails
UNIQUE (DT_TITLE, DT_TS_ID);
Now create a proc something like this (error handling omitted for brevity):
CREATE PROC dbo.usp_project_copy
(
@.pr_id INT,
@.pr_title NVARCHAR(50),
@.new_pr_id INT OUTPUT
)
AS
BEGIN
INSERT INTO dbo.prjProjects (PR_TITLE)
VALUES (@.pr_title) ;
SET @.new_pr_id = SCOPE_IDENTITY();
INSERT INTO dbo.prjTasks (TS_TITLE, TS_PR_ID)
SELECT TS_TITLE, @.new_pr_id
FROM dbo.prjTasks
WHERE TS_PR_ID = @.pr_id ;
INSERT INTO dbo.prjDetails (DT_TITLE, DT_TS_ID)
SELECT D1.DT_TITLE, T2.TS_ID
FROM dbo.prjDetails AS D1
JOIN dbo.prjTasks AS T1
ON D1.DT_TS_ID = T1.TS_ID
AND T1.TS_PR_ID = @.pr_id
JOIN dbo.prjTasks AS T2
ON T1.TS_TITLE = T2.TS_TITLE
AND T2.TS_PR_ID = @.new_pr_id
AND T1.TS_TITLE = T2.TS_TITLE ;
RETURN
END
GO
DECLARE @.pr_id INT
EXEC dbo.usp_project_copy
@.pr_id = 1,
@.pr_title = 'New Project',
@.new_pr_id = @.pr_id OUTPUT ;
SELECT *
FROM prjProjects
WHERE PR_ID = @.pr_id ;
Thanks for including the DDL. Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||You could create a script file, very similar to the DDL/DML you offered,
adding additional elements (as David indicated), and then execute the script
file using OSQL (for SQL 2000) or SQLcmd (for SQL 2005)
If you were to use any variables in the script file, remember that when a GO
is encountered, the variable goes out of scope, so you would have to
re-create and initialize it again.
If you want to automate the process, the script file can be executed from a
SQL Agent Job, or even from a stored procedure.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
news:uSbpn1rmGHA.4716@.TK2MSFTNGP04.phx.gbl...
>I am creating a small project management application using SQL Server.
>There are three main tables (prjProjects, prjTasks, prjDetails). A project
>can have 0 to many tasks and a tasks can have 0 to many details.
> Many of our projects are very similar in that they would consist of
> basically the same task and detail lists/records. I would like to be able
> to "clone" a project using a stored procedure. I will be accessing the
> tables from multiple front ends such as MS Access, ColdFusion, and ASP. A
> stored procedure would allow me to create a new, cloned project from any
> of these front ends.
> I am not sure where to start with this. I have searched Google and news
> groups without finding much useful information. Do I need to create a
> cursor to loop through records and perform inserts or is there a more
> efficient method? The tables, significant fields, and records are created
> below. There are additional fields for descriptions, dates, etc but if I
> get the basic framework, I should be able to include the other fields. I
> would like to duplicate the "TITLE" field values in the new records.
> Hope I have provided enough info that someone can point me in the right
> direction.
> --
> Duane Hookom
> MS Access MVP
>
> CREATE TABLE [dbo].[prjProjects]
> (
> [PR_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [PR_TITLE] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[prjTasks] (
> [TS_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [TS_TITLE] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TS_PR_ID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[prjDetails]
> (
> [DT_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [DT_TITLE] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [DT_TS_ID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[prjProjects] WITH NOCHECK ADD
> CONSTRAINT [PK_prjProjects] PRIMARY KEY CLUSTERED
> (
> [PR_ID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[prjTasks] WITH NOCHECK ADD
> CONSTRAINT [PK_prjTasks] PRIMARY KEY CLUSTERED
> (
> [TS_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[prjDetails] WITH NOCHECK ADD
> CONSTRAINT [PK_prjDetails] PRIMARY KEY CLUSTERED
> (
> [DT_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[prjTasks] ADD
> CONSTRAINT [FK_prjTasks_prjProjects] FOREIGN KEY
> (
> [TS_PR_ID]
> ) REFERENCES [dbo].[prjProjects] (
> [PR_ID]
> )
> GO
> ALTER TABLE [dbo].[prjDetails] ADD
> CONSTRAINT [FK_prjDetails_prjTasks] FOREIGN KEY
> (
> [DT_TS_ID]
> ) REFERENCES [dbo].[prjTasks] (
> [TS_ID]
> )
> GO
> INSERT INTO prjProjects (PR_TITLE) Values('Project to Clone')
> GO
> INSERT INTO prjTasks (TS_TITLE, TS_PR_ID) Values('Task One', 1)
> GO
> INSERT INTO prjTasks (TS_TITLE, TS_PR_ID) Values('Task Two', 1)
> GO
> INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 1 of Task
> One',1)
> GO
> INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 2 of Task
> One',1)
> GO
>
> INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 3 of Task
> One',1)
> GO
> INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 1 of Task
> Two',2)
> GO
> INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 2 of Task
> Two',2)
> GO
>|||David and Arnie,
Thanks much. This should provide a good starting point for me. All of my
projects, tasks, and details have start and expected dates associated so I
will need to include them. I'm fairly sure I can get this on my own. If not,
I'll be back.
Duane Hookom
MS Access MVP
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:OHES$RsmGHA.3796@.TK2MSFTNGP05.phx.gbl...
> "Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
> news:uSbpn1rmGHA.4716@.TK2MSFTNGP04.phx.gbl...
> First you should be to add some important constraints. These are my
> assumptions of course but the keys will be important here.
> ALTER TABLE dbo.prjProjects ALTER COLUMN PR_TITLE NVARCHAR(50) NOT NULL;
> ALTER TABLE dbo.prjTasks ALTER COLUMN TS_TITLE NVARCHAR(50) NOT NULL;
> ALTER TABLE dbo.prjProjects ADD CONSTRAINT AK1_prjProjects
> UNIQUE (PR_TITLE);
> ALTER TABLE dbo.prjTasks ADD CONSTRAINT AK1_prjTasks
> UNIQUE (TS_TITLE, TS_PR_ID);
> ALTER TABLE dbo.prjDetails ADD CONSTRAINT AK1_prjDetails
> UNIQUE (DT_TITLE, DT_TS_ID);
>
> Now create a proc something like this (error handling omitted for
> brevity):
> CREATE PROC dbo.usp_project_copy
> (
> @.pr_id INT,
> @.pr_title NVARCHAR(50),
> @.new_pr_id INT OUTPUT
> )
> AS
> BEGIN
> INSERT INTO dbo.prjProjects (PR_TITLE)
> VALUES (@.pr_title) ;
> SET @.new_pr_id = SCOPE_IDENTITY();
> INSERT INTO dbo.prjTasks (TS_TITLE, TS_PR_ID)
> SELECT TS_TITLE, @.new_pr_id
> FROM dbo.prjTasks
> WHERE TS_PR_ID = @.pr_id ;
> INSERT INTO dbo.prjDetails (DT_TITLE, DT_TS_ID)
> SELECT D1.DT_TITLE, T2.TS_ID
> FROM dbo.prjDetails AS D1
> JOIN dbo.prjTasks AS T1
> ON D1.DT_TS_ID = T1.TS_ID
> AND T1.TS_PR_ID = @.pr_id
> JOIN dbo.prjTasks AS T2
> ON T1.TS_TITLE = T2.TS_TITLE
> AND T2.TS_PR_ID = @.new_pr_id
> AND T1.TS_TITLE = T2.TS_TITLE ;
> RETURN
> END
> GO
> DECLARE @.pr_id INT
> EXEC dbo.usp_project_copy
> @.pr_id = 1,
> @.pr_title = 'New Project',
> @.new_pr_id = @.pr_id OUTPUT ;
> SELECT *
> FROM prjProjects
> WHERE PR_ID = @.pr_id ;
> Thanks for including the DDL. Hope this helps.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||As an additional piece of information.
I would have a stored procedure creates the script file. By so doing, then I
could pass in the changable pieces of data, the stored procedure could
re-create all variables after encountering a GO in the script, and the end
result is a comprehensive script file that does not require me to do a
'search and destroy' (I mean search are replace) with the chance of missing
some location where a value needed to be replaced.
I've done it with two different processes. One, just use PRINT statements
line by line, then copy the generated output into a new QA window and save
(or execute the script). Or, two, using OSQL, output the generated script to
a file. I find the first choice gives me greater control on formating,
readibility, etc.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:etEkjismGHA.4164@.TK2MSFTNGP03.phx.gbl...
> You could create a script file, very similar to the DDL/DML you offered,
> adding additional elements (as David indicated), and then execute the
> script file using OSQL (for SQL 2000) or SQLcmd (for SQL 2005)
> If you were to use any variables in the script file, remember that when a
> GO is encountered, the variable goes out of scope, so you would have to
> re-create and initialize it again.
> If you want to automate the process, the script file can be executed from
> a SQL Agent Job, or even from a stored procedure.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
> news:uSbpn1rmGHA.4716@.TK2MSFTNGP04.phx.gbl...
>

Cascade deletes in SQL Server 7.0?

Is it possible to have data deleted(automaticly) from tables related to the one im deleting from..

Example:

Table A have a one to many relation to 6 other tables.

I will delete a member from Table A, will the data conserning the member in the other 6 tables be automaticly removed?

Best wishes.

(If not, how should I write the sp to handle this task?)For SQL Server 7.0 you would have to implement TRIGGERS to perform cascade deletes. However in SQL Server 2000 there is cascade deletes, defined by 'Cascading Referential Integrity Constraints'|||Okay!

Thanks for responding - Scre@.m

Cascade deletes in SQL 2005

I have a logins table, a loginroles table (intermediate), and a roles table

When I delete a login from logins I need to delete the roles for that login from loginroles. I know I have to use cascade deletes, but I cannot find the option in sql 2005.

Any help would be appreciated.

Thank You,
Jason

It is there even in Express edition, you should see enable DRI(declarative referential integrity) at the top of Management Studio. BTW SQL Server 2005 also comes with the other two ANSI SQL definition DRI SET NULL and SET DEFAULT. Try the links below for more from Microsoft and my interaction with a user about SQL Server Express. Post again if you still need help.

http://msdn2.microsoft.com/en-us/library/ms186973.aspx

http://forums.asp.net/thread/1116493.aspx

|||

Can you just walk me through how to find it? I am looking around but do not see it, What should I click on? Table, PK, FK? Right click? Properties? Contraints? I look but I do not see it...

|||

You are looking for enable relationship dialog box at the top of Management studio try the link below for all the options. Post again if you still need help.

http://msdn2.microsoft.com/en-us/library/ms177288.aspx

cascade deletes and updates

Hi,
I was wondering whether cascaded deletes or updates would be
replicated to the subscriber. The subscriber tables are just plain
tables without any constraints. But the publisher tables have FK
relationships. On the publishers database, if I deleted a row from the
parent table resulting in the removal of the child rows, would the
child rows also get removed in the subscriber tables? Thanks
- Komp
If the child row table is replicated then it'll work as you propose.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||By default they are replicated. If you don't want this behavior right click
on your table in EM, select Design Table, and then select the properties
icon (looks like a hand pointing to something) and click on relationships.
Ensure that Enforce Relationship for Inserts and Updates is selected and
check cascading Update Related Fields.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<k0mput3r@.gmail.com> wrote in message
news:1140023314.624365.105580@.o13g2000cwo.googlegr oups.com...
> Hi,
> I was wondering whether cascaded deletes or updates would be
> replicated to the subscriber. The subscriber tables are just plain
> tables without any constraints. But the publisher tables have FK
> relationships. On the publishers database, if I deleted a row from the
> parent table resulting in the removal of the child rows, would the
> child rows also get removed in the subscriber tables? Thanks
> - Komp
>
|||Also ensure that the enforce relationship for replication is enabled as
well.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:e9R8BllMGHA.2036@.TK2MSFTNGP14.phx.gbl...
> By default they are replicated. If you don't want this behavior right
> click on your table in EM, select Design Table, and then select the
> properties icon (looks like a hand pointing to something) and click on
> relationships. Ensure that Enforce Relationship for Inserts and Updates is
> selected and check cascading Update Related Fields.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> <k0mput3r@.gmail.com> wrote in message
> news:1140023314.624365.105580@.o13g2000cwo.googlegr oups.com...
>

Cascade Deletes

I cannot understand why I receive the following error
mesage when trying to Create a cascading delete
constraint.
Introducing FOREIGN KEY
constraint 'FK_DEALATTR_RELATION__DEAL' on
table 'DealAttribute' may cause cycles or multiple
cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
NO ACTION, or modify other FOREIGN KEY constraints.
Deal Table
CREATE TABLE [Deal] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[Reference] [char] (20),
[CptyID] [int] NULL
............
............
............
............
CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
(
[DealID],
[Generation])
**********************************************************
******
Deal Attribute Table
CREATE TABLE [DealAttribute] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[AttributeID] [int] NOT NULL ,
............
............
............
CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY CLUSTERED
(
[DealID],
[Generation],
[AttributeID])
I then want to add a constraint on the DealAttribute
table that references DealID and Generation in the Deal
table. I want the constraint to cascade Delete/Update
i.e. when I delete a record from the deal table for the
corresponding record to be removed from the DealAttribute
table. But I get the above error. Below is the SQL I
use to create the Constraint.
if exists (select 1
from sysobjects
where id = object_id
('FK_DEALATTR_RELATION__DEAL')
and type = 'FK')
alter table DealAttribute
drop constraint FK_DEALATTR_RELATION__DEAL
go
alter table DealAttribute
add constraint FK_DEALATTR_RELATION__DEAL foreign key
(DealID, Generation)
references Deal (DealID, Generation)
on update cascade on delete cascade
go
I dont see how it is cyclic.
Please help.
Thanks
JamieJamie
Read the error message which says what is exactly the problem
You have tried to create a constraint that refernces to the table with two
columns(DealID, Generation)
The below script will work for you
CREATE TABLE Parent
(
[ID] INT NOT NULL PRIMARY KEY,
[NAME]CHAR(1) NOT NULL
)
INSERT INTO Parent VALUES (1,'A')
INSERT INTO Parent VALUES (2,'B')
INSERT INTO Parent VALUES (3,'C')
CREATE TABLE Child
(
[ID] INT NOT NULL PRIMARY KEY,
GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])ON DELETE CASCADE ON
UPDATE CASCADE,
[NAME]CHAR(2) NOT NULL
)
INSERT INTO Child VALUES (1,1,'AA')
INSERT INTO Child VALUES (2,1,'AA')
INSERT INTO Child VALUES (3,2,'BB')
INSERT INTO Child VALUES (4,2,'BB')
INSERT INTO Child VALUES (5,2,'BB')
INSERT INTO Child VALUES (6,3,'CC')
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:2670701c4628c$11b4edb0$a401280a@.phx.gbl...
> I cannot understand why I receive the following error
> mesage when trying to Create a cascading delete
> constraint.
> Introducing FOREIGN KEY
> constraint 'FK_DEALATTR_RELATION__DEAL' on
> table 'DealAttribute' may cause cycles or multiple
> cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
> NO ACTION, or modify other FOREIGN KEY constraints.
> Deal Table
> CREATE TABLE [Deal] (
> [DealID] [int] NOT NULL ,
> [Generation] [int] NOT NULL ,
> [Reference] [char] (20),
> [CptyID] [int] NULL
> ............
> ............
> ............
> ............
> CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
> (
> [DealID],
> [Generation])
> **********************************************************
> ******
> Deal Attribute Table
> CREATE TABLE [DealAttribute] (
> [DealID] [int] NOT NULL ,
> [Generation] [int] NOT NULL ,
> [AttributeID] [int] NOT NULL ,
> ............
> ............
> ............
> CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY CLUSTERED
> (
> [DealID],
> [Generation],
> [AttributeID])
> I then want to add a constraint on the DealAttribute
> table that references DealID and Generation in the Deal
> table. I want the constraint to cascade Delete/Update
> i.e. when I delete a record from the deal table for the
> corresponding record to be removed from the DealAttribute
> table. But I get the above error. Below is the SQL I
> use to create the Constraint.
> if exists (select 1
> from sysobjects
> where id = object_id
> ('FK_DEALATTR_RELATION__DEAL')
> and type = 'FK')
> alter table DealAttribute
> drop constraint FK_DEALATTR_RELATION__DEAL
> go
> alter table DealAttribute
> add constraint FK_DEALATTR_RELATION__DEAL foreign key
> (DealID, Generation)
> references Deal (DealID, Generation)
> on update cascade on delete cascade
> go
> I dont see how it is cyclic.
> Please help.
> Thanks
> Jamie|||Uri,
I reference the two columns because the combination of
the two make a unique key and are the PK for both
tables. Am I missing something really obvious here?
Thanks
Jamie
>--Original Message--
>Jamie
>Read the error message which says what is exactly the
problem
>You have tried to create a constraint that refernces to
the table with two
>columns(DealID, Generation)
>The below script will work for you
>CREATE TABLE Parent
>(
> [ID] INT NOT NULL PRIMARY KEY,
> [NAME]CHAR(1) NOT NULL
>)
>INSERT INTO Parent VALUES (1,'A')
>INSERT INTO Parent VALUES (2,'B')
>INSERT INTO Parent VALUES (3,'C')
>CREATE TABLE Child
>(
> [ID] INT NOT NULL PRIMARY KEY,
> GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])ON
DELETE CASCADE ON
>UPDATE CASCADE,
> [NAME]CHAR(2) NOT NULL
>)
>INSERT INTO Child VALUES (1,1,'AA')
>INSERT INTO Child VALUES (2,1,'AA')
>INSERT INTO Child VALUES (3,2,'BB')
>INSERT INTO Child VALUES (4,2,'BB')
>INSERT INTO Child VALUES (5,2,'BB')
>INSERT INTO Child VALUES (6,3,'CC')
>
>
>
>"Jamie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2670701c4628c$11b4edb0$a401280a@.phx.gbl...
>> I cannot understand why I receive the following error
>> mesage when trying to Create a cascading delete
>> constraint.
>> Introducing FOREIGN KEY
>> constraint 'FK_DEALATTR_RELATION__DEAL' on
>> table 'DealAttribute' may cause cycles or multiple
>> cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
>> NO ACTION, or modify other FOREIGN KEY constraints.
>> Deal Table
>> CREATE TABLE [Deal] (
>> [DealID] [int] NOT NULL ,
>> [Generation] [int] NOT NULL ,
>> [Reference] [char] (20),
>> [CptyID] [int] NULL
>> ............
>> ............
>> ............
>> ............
>> CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
>> (
>> [DealID],
>> [Generation])
>>
**********************************************************
>> ******
>> Deal Attribute Table
>> CREATE TABLE [DealAttribute] (
>> [DealID] [int] NOT NULL ,
>> [Generation] [int] NOT NULL ,
>> [AttributeID] [int] NOT NULL ,
>> ............
>> ............
>> ............
>> CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY
CLUSTERED
>> (
>> [DealID],
>> [Generation],
>> [AttributeID])
>> I then want to add a constraint on the DealAttribute
>> table that references DealID and Generation in the Deal
>> table. I want the constraint to cascade Delete/Update
>> i.e. when I delete a record from the deal table for the
>> corresponding record to be removed from the
DealAttribute
>> table. But I get the above error. Below is the SQL I
>> use to create the Constraint.
>> if exists (select 1
>> from sysobjects
>> where id = object_id
>> ('FK_DEALATTR_RELATION__DEAL')
>> and type = 'FK')
>> alter table DealAttribute
>> drop constraint FK_DEALATTR_RELATION__DEAL
>> go
>> alter table DealAttribute
>> add constraint FK_DEALATTR_RELATION__DEAL foreign
key
>> (DealID, Generation)
>> references Deal (DealID, Generation)
>> on update cascade on delete cascade
>> go
>> I dont see how it is cyclic.
>> Please help.
>> Thanks
>> Jamie
>
>.
>|||Jamie
Look at this helps you.
CREATE TABLE Test
(
col1 INT NOT NULL REFERNCES Table (col1),
col2 INT NOT NULL REFERNCES Table1 (col2),
Primary key (col,col2)
)
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:267be01c46290$e4e593c0$a501280a@.phx.gbl...
> Uri,
> I reference the two columns because the combination of
> the two make a unique key and are the PK for both
> tables. Am I missing something really obvious here?
> Thanks
> Jamie
> >--Original Message--
> >Jamie
> >Read the error message which says what is exactly the
> problem
> >
> >You have tried to create a constraint that refernces to
> the table with two
> >columns(DealID, Generation)
> >The below script will work for you
> >
> >CREATE TABLE Parent
> >(
> > [ID] INT NOT NULL PRIMARY KEY,
> > [NAME]CHAR(1) NOT NULL
> >)
> >INSERT INTO Parent VALUES (1,'A')
> >INSERT INTO Parent VALUES (2,'B')
> >INSERT INTO Parent VALUES (3,'C')
> >
> >CREATE TABLE Child
> >(
> > [ID] INT NOT NULL PRIMARY KEY,
> > GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])ON
> DELETE CASCADE ON
> >UPDATE CASCADE,
> > [NAME]CHAR(2) NOT NULL
> >)
> >
> >INSERT INTO Child VALUES (1,1,'AA')
> >INSERT INTO Child VALUES (2,1,'AA')
> >INSERT INTO Child VALUES (3,2,'BB')
> >INSERT INTO Child VALUES (4,2,'BB')
> >INSERT INTO Child VALUES (5,2,'BB')
> >INSERT INTO Child VALUES (6,3,'CC')
> >
> >
> >
> >
> >
> >
> >"Jamie" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2670701c4628c$11b4edb0$a401280a@.phx.gbl...
> >> I cannot understand why I receive the following error
> >> mesage when trying to Create a cascading delete
> >> constraint.
> >>
> >> Introducing FOREIGN KEY
> >> constraint 'FK_DEALATTR_RELATION__DEAL' on
> >> table 'DealAttribute' may cause cycles or multiple
> >> cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
> >> NO ACTION, or modify other FOREIGN KEY constraints.
> >>
> >> Deal Table
> >>
> >> CREATE TABLE [Deal] (
> >> [DealID] [int] NOT NULL ,
> >> [Generation] [int] NOT NULL ,
> >> [Reference] [char] (20),
> >> [CptyID] [int] NULL
> >> ............
> >> ............
> >> ............
> >> ............
> >>
> >> CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
> >> (
> >> [DealID],
> >> [Generation])
> >>
> >>
> **********************************************************
> >> ******
> >>
> >> Deal Attribute Table
> >>
> >> CREATE TABLE [DealAttribute] (
> >> [DealID] [int] NOT NULL ,
> >> [Generation] [int] NOT NULL ,
> >> [AttributeID] [int] NOT NULL ,
> >> ............
> >> ............
> >> ............
> >>
> >> CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY
> CLUSTERED
> >> (
> >> [DealID],
> >> [Generation],
> >> [AttributeID])
> >>
> >> I then want to add a constraint on the DealAttribute
> >> table that references DealID and Generation in the Deal
> >> table. I want the constraint to cascade Delete/Update
> >> i.e. when I delete a record from the deal table for the
> >> corresponding record to be removed from the
> DealAttribute
> >> table. But I get the above error. Below is the SQL I
> >> use to create the Constraint.
> >>
> >> if exists (select 1
> >> from sysobjects
> >> where id = object_id
> >> ('FK_DEALATTR_RELATION__DEAL')
> >> and type = 'FK')
> >> alter table DealAttribute
> >> drop constraint FK_DEALATTR_RELATION__DEAL
> >> go
> >>
> >> alter table DealAttribute
> >> add constraint FK_DEALATTR_RELATION__DEAL foreign
> key
> >> (DealID, Generation)
> >> references Deal (DealID, Generation)
> >> on update cascade on delete cascade
> >> go
> >>
> >> I dont see how it is cyclic.
> >>
> >> Please help.
> >> Thanks
> >> Jamie
> >
> >
> >.
> >|||Uri,
This is no good. Because there are multiple DealIDs with
different Generations in the DealAttribute table.
So if you deleted DealID from Deal table all DealID's
would go in DealAttribute table. Regardless of
generation. The two columns are a compund key. Is it
not possible to have a cascade delete with a compound key?
Cheers
Jamie...
>--Original Message--
>Jamie
>Look at this helps you.
>
>CREATE TABLE Test
>(
> col1 INT NOT NULL REFERNCES Table (col1),
> col2 INT NOT NULL REFERNCES Table1 (col2),
> Primary key (col,col2)
>)
>
>"Jamie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:267be01c46290$e4e593c0$a501280a@.phx.gbl...
>> Uri,
>> I reference the two columns because the combination of
>> the two make a unique key and are the PK for both
>> tables. Am I missing something really obvious here?
>> Thanks
>> Jamie
>> >--Original Message--
>> >Jamie
>> >Read the error message which says what is exactly the
>> problem
>> >
>> >You have tried to create a constraint that refernces
to
>> the table with two
>> >columns(DealID, Generation)
>> >The below script will work for you
>> >
>> >CREATE TABLE Parent
>> >(
>> > [ID] INT NOT NULL PRIMARY KEY,
>> > [NAME]CHAR(1) NOT NULL
>> >)
>> >INSERT INTO Parent VALUES (1,'A')
>> >INSERT INTO Parent VALUES (2,'B')
>> >INSERT INTO Parent VALUES (3,'C')
>> >
>> >CREATE TABLE Child
>> >(
>> > [ID] INT NOT NULL PRIMARY KEY,
>> > GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])
ON
>> DELETE CASCADE ON
>> >UPDATE CASCADE,
>> > [NAME]CHAR(2) NOT NULL
>> >)
>> >
>> >INSERT INTO Child VALUES (1,1,'AA')
>> >INSERT INTO Child VALUES (2,1,'AA')
>> >INSERT INTO Child VALUES (3,2,'BB')
>> >INSERT INTO Child VALUES (4,2,'BB')
>> >INSERT INTO Child VALUES (5,2,'BB')
>> >INSERT INTO Child VALUES (6,3,'CC')
>> >
>> >
>> >
>> >
>> >
>> >
>> >"Jamie" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:2670701c4628c$11b4edb0$a401280a@.phx.gbl...
>> >> I cannot understand why I receive the following
error
>> >> mesage when trying to Create a cascading delete
>> >> constraint.
>> >>
>> >> Introducing FOREIGN KEY
>> >> constraint 'FK_DEALATTR_RELATION__DEAL' on
>> >> table 'DealAttribute' may cause cycles or multiple
>> >> cascade paths. Specify ON DELETE NO ACTION or ON
UPDATE
>> >> NO ACTION, or modify other FOREIGN KEY constraints.
>> >>
>> >> Deal Table
>> >>
>> >> CREATE TABLE [Deal] (
>> >> [DealID] [int] NOT NULL ,
>> >> [Generation] [int] NOT NULL ,
>> >> [Reference] [char] (20),
>> >> [CptyID] [int] NULL
>> >> ............
>> >> ............
>> >> ............
>> >> ............
>> >>
>> >> CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
>> >> (
>> >> [DealID],
>> >> [Generation])
>> >>
>> >>
**********************************************************
>> >> ******
>> >>
>> >> Deal Attribute Table
>> >>
>> >> CREATE TABLE [DealAttribute] (
>> >> [DealID] [int] NOT NULL ,
>> >> [Generation] [int] NOT NULL ,
>> >> [AttributeID] [int] NOT NULL ,
>> >> ............
>> >> ............
>> >> ............
>> >>
>> >> CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY
>> CLUSTERED
>> >> (
>> >> [DealID],
>> >> [Generation],
>> >> [AttributeID])
>> >>
>> >> I then want to add a constraint on the DealAttribute
>> >> table that references DealID and Generation in the
Deal
>> >> table. I want the constraint to cascade
Delete/Update
>> >> i.e. when I delete a record from the deal table for
the
>> >> corresponding record to be removed from the
>> DealAttribute
>> >> table. But I get the above error. Below is the
SQL I
>> >> use to create the Constraint.
>> >>
>> >> if exists (select 1
>> >> from sysobjects
>> >> where id = object_id
>> >> ('FK_DEALATTR_RELATION__DEAL')
>> >> and type = 'FK')
>> >> alter table DealAttribute
>> >> drop constraint FK_DEALATTR_RELATION__DEAL
>> >> go
>> >>
>> >> alter table DealAttribute
>> >> add constraint FK_DEALATTR_RELATION__DEAL foreign
>> key
>> >> (DealID, Generation)
>> >> references Deal (DealID, Generation)
>> >> on update cascade on delete cascade
>> >> go
>> >>
>> >> I dont see how it is cyclic.
>> >>
>> >> Please help.
>> >> Thanks
>> >> Jamie
>> >
>> >
>> >.
>> >
>
>.
>|||Hi,
I started explaining what was wrong with what you were attempting to do, when I realised I was writting utter rubbish. Check to see that you SQL Server is up to date, patch wise.
I ran the following SQL, which is basically yours, and the tables and FK were created without problem. I also poped a couple of rows in the table, and the cascade worked. My SQL Server version is 8.00.818.
Al
CREATE TABLE [Deal] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[Reference] [char] (20),
[CptyID] [int] NULL,
CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
([DealID], [Generation])
)
GO
CREATE TABLE [DealAttribute] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[AttributeID] [int] NOT NULL ,
CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY CLUSTERED
([DealID], [Generation], [AttributeID])
)
alter table DealAttribute
add constraint FK_DEALATTR_RELATION__DEAL
foreign key (DealID, Generation)
references Deal (DealID, Generation)
on update cascade on delete cascade
go|||On Mon, 5 Jul 2004 06:40:02 -0700, Jamie wrote:
>Uri,
>This is no good. Because there are multiple DealIDs with
>different Generations in the DealAttribute table.
>So if you deleted DealID from Deal table all DealID's
>would go in DealAttribute table. Regardless of
>generation. The two columns are a compund key. Is it
>not possible to have a cascade delete with a compound key?
>Cheers
>Jamie...
Hi Jamie,
That is possible. There must be another problem.
After reading your post, I had the idea that something was missing. Al's
post confirmed this.
I think that there is already an FK relation with some cascading option
between Deal and DealAttribute. It might even be an indirect relation
(e.g. from Deal to XYZ and from XYZ to DealAttribute). You might want to
check into that.
If you're sure that this is not a case, we need a way to reproduce your
problem. If you can post some CREATE TABLE and ALTER TABLE statements that
will reproduce your problem in an empty database (you can find out for
yourself by creating a play database, running the script in that database,
then dropping the play database again), we can investigate this further.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Cascade Deletes

Cascade delete relationship slower?

I have several related tables that have the cascade delete option selected i
n
their relationships. There is one parent table that all the child tables
relate to (sometimes many layers deep). In order to allow deleting of a
parent record I have created a stored procedure that goes through the
hierarchy and deletes the related records in the child tables in the correct
order as to not break referential integrity.
My problem is that this stored procedure is incredibly slow. This seems
strange because for each row you delete in the parent, only a handful of
child rows have to be deleted.
1) Is it possible that the cascade delete option could be slowing things dow
n?
2) Since I am taking care of the cascading of deletes on my own in the
stored proc, should I remove that option from the relationships?
3) What other factors could be contributing to very slow delete performance?Do you have indexes on the FK's in each of the child tables?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:6162CFA5-5032-4F82-86E2-092B3B76F1EE@.microsoft.com...
I have several related tables that have the cascade delete option selected
in
their relationships. There is one parent table that all the child tables
relate to (sometimes many layers deep). In order to allow deleting of a
parent record I have created a stored procedure that goes through the
hierarchy and deletes the related records in the child tables in the correct
order as to not break referential integrity.
My problem is that this stored procedure is incredibly slow. This seems
strange because for each row you delete in the parent, only a handful of
child rows have to be deleted.
1) Is it possible that the cascade delete option could be slowing things
down?
2) Since I am taking care of the cascading of deletes on my own in the
stored proc, should I remove that option from the relationships?
3) What other factors could be contributing to very slow delete performance?|||Could you post the DDL for the procedure?
Wild guess: the performance problem is a result of locking. How quick is a
delete from the parent table using just the CASCADEs?
--
"Dan" wrote:

> I have several related tables that have the cascade delete option selected
in
> their relationships. There is one parent table that all the child tables
> relate to (sometimes many layers deep). In order to allow deleting of a
> parent record I have created a stored procedure that goes through the
> hierarchy and deletes the related records in the child tables in the corre
ct
> order as to not break referential integrity.
> My problem is that this stored procedure is incredibly slow. This seems
> strange because for each row you delete in the parent, only a handful of
> child rows have to be deleted.
> 1) Is it possible that the cascade delete option could be slowing things d
own?
> 2) Since I am taking care of the cascading of deletes on my own in the
> stored proc, should I remove that option from the relationships?
> 3) What other factors could be contributing to very slow delete performanc
e?
>
>|||
> 1) Is it possible that the cascade delete option could be slowing things
> down?
Cascade delete option? If you are using that option then this deletion
should just magically occur, whap bam boom. No need for a stored procedure.
I don't believe it will make any difference, as long as there are no rows to
delete. At this point the check for rows to cause NO ACTION will be the
exact same as the check for rows to CASCADE the action to. To optimize it,
think of the select statement you would run to see if there were rows:
select *
from childTable
where key in (parentTableKeyValue1, parentTableKeyValue1, ...,
parentTableKeyValueN)
As Tom mentions, you will probably need to index the columns in the foreign
keys, for this reason (I am just feeling wordy tonight!) Of course, if you
are deleting large number of rows at a time, then indexes might not help.

> 2) Since I am taking care of the cascading of deletes on my own in the
> stored proc, should I remove that option from the relationships?
Well, I don't know if it is hurting performance, but it certainly isn't a
good use of your time to do it both ways. If cascade deletes won't work for
you, then yes, you should remove it.

> 3) What other factors could be contributing to very slow delete
> performance?
Poor disk subsystem, not enough ram, too many concurrent users, gremlins,
etc. Seriously this is a big question. You need to optimize the queries
and look for table scans or cluster index scans (ordered table scans) to
determine if you are getting good plans. Then hit perfmon and see if the
system is performing.
I think that indexing the foreign keys is probably the correct answer, but
it is a very open question.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:6162CFA5-5032-4F82-86E2-092B3B76F1EE@.microsoft.com...
>I have several related tables that have the cascade delete option selected
>in
> their relationships. There is one parent table that all the child tables
> relate to (sometimes many layers deep). In order to allow deleting of a
> parent record I have created a stored procedure that goes through the
> hierarchy and deletes the related records in the child tables in the
> correct
> order as to not break referential integrity.
> My problem is that this stored procedure is incredibly slow. This seems
> strange because for each row you delete in the parent, only a handful of
> child rows have to be deleted.
> 1) Is it possible that the cascade delete option could be slowing things
> down?
> 2) Since I am taking care of the cascading of deletes on my own in the
> stored proc, should I remove that option from the relationships?
> 3) What other factors could be contributing to very slow delete
> performance?
>
>

Monday, March 19, 2012

Cascading Triggers

Sunday, March 11, 2012

Cascading Deletes

Thursday, March 8, 2012

Cascade updates

Cascade Update and Delete Related Fields..

cascade update / foreign key

Cascade Update

Cascade set to null on delete.

Cascade parameter and sharepoint 2007

Cascade Inserts/Clone a tree

Cascade deletes in SQL Server 7.0?

Cascade deletes in SQL 2005

cascade deletes and updates

Cascade Deletes

Cascade Deletes

Cascade Deletes

Cascade delete relationship slower?

uninstall SQL

Blog Archive

About Me