Cascade Delete And Update In SQL 7.0
Is it possible to cascade update and delete while DRI remains there ? That
is without deleteing refrential integrity. If anyone have example of cascade delete and Update using Pubs or Northwind Example , i'll be really obliged.
View Complete Forum Thread with Replies
Related Forum Messages:
Update And Delete Rule - Cascade
Hi, I have a database which saves data about bus links. I want to provide a information to passenger about price of their journay. The price depends on three factors: starting busstop, ending busstop and type of ticket (full, part - for students and old people, ...). So I created a table with three foreign key constraints (two for busstops and one for type). When the busstop is deleted or type of ticket I want all data connected with it to be deleted automatically. I wanted to use cascade deleting. But I receive a following exception: Introducing FOREIGN KEY constraint 'FK_TicketPrices_BusStops1' on table 'TicketPrices' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. How can I achieve my task? Why should it cause cycles or multiple cascade paths?
View Replies !
Remove Cascade Update And Delete From Table
hello, once upon a time when i created my db (originally in access then used the conversion tool, which i now know is wrong!) i thought it would be an amazing idea to have cascading updates and deletes, however it turns out now this is exactly not what i want! if i leave them in then it throws errors when i delete records out of my stock table as related records are in the order_line table here is the code (well i think so, im not the best at sqlserver as you probably can tell already) that im using if anyone can help or point me in the right direction that would be great, thanks USE [nashdfDB1]GO/****** Object: Table [dbo].[tbl_stock] Script Date: 07/13/2007 02:52:14 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[tbl_stock]( [Stock_ID] [int] IDENTITY(1,1) NOT NULL, [cat_id] [int] NOT NULL CONSTRAINT [DF__tbl_stock__cat_i__15502E78] DEFAULT ((0)), [sub_cat_id] [int] NULL CONSTRAINT [DF__tbl_stock__sub_c__164452B1] DEFAULT ((0)), [location] [int] NULL CONSTRAINT [DF__tbl_stock__locat__173876EA] DEFAULT ((0)), [n_or_sh] [varchar](50) NULL, [title] [varchar](255) NULL, [description] [varchar](255) NULL, [size] [varchar](50) NULL, [colour] [varchar](50) NULL, [cost_price] [decimal](9, 2) NULL CONSTRAINT [DF__tbl_stock__cost___182C9B23] DEFAULT ((0)), [selling_price] [decimal](9, 2) NULL CONSTRAINT [DF__tbl_stock__selli__1920BF5C] DEFAULT ((0)), [qty] [varchar](50) NULL, [date] [datetime] NULL CONSTRAINT [DF__tbl_stock__date__1A14E395] DEFAULT (getdate()), [condition] [varchar](255) NULL, [notes] [varchar](255) NULL, [visible] [bit] NULL CONSTRAINT [DF__tbl_stock__visib__1B0907CE] DEFAULT ((1)), [picture1] [varchar](50) NULL, [picture1_thumb] [varchar](50) NULL, [picture2] [varchar](50) NULL, [picture2_thumb] [varchar](50) NULL, [picture3] [varchar](50) NULL, [picture3_thumb] [varchar](50) NULL, [picture4] [varchar](50) NULL, [picture4_thumb] [varchar](50) NULL, [display_price] [varchar](50) NULL, [created_by] [varchar](50) NULL, [buying_in_recipt] [varchar](255) NULL, CONSTRAINT [tbl_stock$PrimaryKey] PRIMARY KEY CLUSTERED ( [Stock_ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] GO SET ANSI_PADDING OFF Regards Jez
View Replies !
On DELETE On UPDATE Cascade Syntax Error
Hello I need to be able to regularly, update or delete data from my parent table and subsequent child tables from A to Z, each table contains data. However, I have having problems. I have already created the tables with primary keys on each table and foreign keys linking each table to the next. I tried to delete a row from the parent table and was given this error: DELETE FROM [dbo].[DomNam]WHERE [DomNam]=N' football ' Error: Query(1/1) DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_DomNam'. The conflict occurred in database 'DomDB', table 'Dom_CatA', column 'DomNam'. I tried to insert an alter table query: ALTER TABLE dbo.DomNamADD CONSTRAINT FK_Dom_ID REFERENCES dbo.Dom_CatA (Dom_ID) ON DELETE CASCADE ON UPDATE CASCADE But on Execute I saw this error: Error] Incorrect syntax for definition of the 'TABLE' constraint What is wrong with the above syntax? Or would it be better if I used a trigger instead because I already have foreign keys set within the tables?If so please give an example of the syntax for the trigger I would need to update and cascade data from all tables. I would be grateful for any advice. Thanks.
View Replies !
How To Alter The Table With Delete/update Cascade Without Recreating The Table
I have contract table which has built in foreign key constrains. How can I alter this table for delete/ update cascade without recreating the table so whenever studentId/ contactId is modified, the change is effected to the contract table. Thanks ************************************************** ****** Contract table DDL is create table contract( contractNum int identity(1,1) primary key, contractDate smalldatetime not null, tuition money not null, studentId char(4) not null foreign key references student (studentId), contactId int not null foreign key references contact (contactId) );
View Replies !
Copy And Delete Table With &"Foreign Key References(...,...) On Delete Cascade?
Hello: Need some serious help with this one... Background: Am working on completing an ORM that can not only handles CRUD actions -- but that can also updates the structure of a table transparently when the class defs change. Reason for this is that I can't get the SQL scripts that would work for updating a software on SqlServer to be portable to other DBMS systems. Doing it by code, rather than SQL batch has a chance of making cross-platform, updateable, software... Anyway, because it needs to be cross-DBMS capable, the constraints are that the system used must work for the lowest common denominator....ie, a 'recipe' of steps that will work on all DBMS's. The Problem: There might be simpler ways to do this with SqlServer (all ears :-) - just in case I can't make it cross platform right now) but, with simplistic DBMS's (SqlLite, etc) there is no way to ALTER table once formed: one has to COPY the Table to a new TMP name, adding a Column in the process, then delete the original, then rename the TMP to the original name. This appears possible in SqlServer too --...as long as there are no CASCADE operations. Truncate table doesn't seem to be the solution, nor drop, as they all seem to trigger a Cascade delete in the Foreign Table. So -- please correct me if I am wrong here -- it appears that the operations would be along the lines of: a) Remove the Foreign Key references b) Copy the table structure, and make a new temp table, adding the column c) Copy the data over d) Add the FK relations, that used to be in the first table, to the new table e) Delete the original f) Done? The questions are: a) How does one alter a table to REMOVE the Foreign Key References part, if it has no 'name'. b) Anyone know of a good clean way to get, and save these constraints to reapply them to the new table. Hopefully with some cross platform ADO.NET solution? GetSchema etc appears to me to be very dbms dependant? c) ANY and all tips on things I might run into later that I have not mentioned, are also greatly appreciated. Thanks! Sky
View Replies !
Can't Cascade Delete
I have a Sql Server 2005 table with 3 fields, an ID field (primary key), a parent ID field, and Name. The parent ID references the ID field (foreign to primary - many to one) within the same table so that records can reference their parent. I would like to place a cascade delete on the ID field so that when the primary ID is removed it will automatically remove all those records with a parent ID that match. Sql server does not allow me to establish this cascade delete.I was considering a trigger instead but only know how tio use the AFTER paramter and not an alternative. Thanks
View Replies !
"... On Delete Cascade ..."
Guys, could anyone tell me if MSSQL Server 7 has 'on delete cascade' option when creating a foreign key constraint or something similar to it. I'd really like MSSQL to remove all dependent records (child records) automatically from one table when I'm deleting a parent record from another table. I know that I can do it via trigger, but the FK constraint should be removed or disabled. I would really appreciate your help. Thank you very much.
View Replies !
Cascade Delete
hi, I know that I can use trigger to delete any related records in other table, for example Ihave 3 tables (A,B,C) The primary key of A is a FK in B, and C table, so when I delete any record from Table A, with triggers I can delete related records in B and C tables. My Question is what if I delete a record from C, how can I prevent that from happening . I only need to allow deletion from the parent table. Thanks for your help Ali
View Replies !
Cascade Delete
hey all, Is there anyway to cascade delete from a table with foreign key constraint? I mean if i delete a row from the parent table, all the dependent table rows should also be deleted.. thank you in advance.. i can write it through stored procedure..but..any other means..
View Replies !
Cascade Delete
I am a C# programmer also acting as the dba for a SQL Server project. I have a table called Folder that has child records in a table called FolderItems. When a Folder is deleted I want the FolderItems to be deleted. I know how to accomplish via code, but I would prefer to have SQL Server perform the delete just in case a folder is deleted via SQL Management Studio or other method. Is a trigger the way to go about this? I know how to create triggers For Delete, but I just wondering is this the best method to create a Cascade delete or does SQL have any built in "Cascade" delete features. I am using SQL Server 2005 if this matters. Thank you, Corby Nichols Flying Elephant Software
View Replies !
ON DELETE CASCADE
Helle people. The question is: I have two tables: Directories and SubDirectories. SubDirectories table has foreign key for DirectoryId. Now if I define this key with ON DELETE CASCADE attribute then in case of I delete a Directory record, all SubDirectory records will also be deleted. But if I have only one table Directories that have field named ParentId and in this field I save an Id of parent direcory. Can I define a parentId field as a foreign key to the same table and define it with ON DELETE CASCADE? Will it work too? Thanks
View Replies !
How Can I Use Cascade Delete For The For The Following Situation.
Hi, I have the following tables: Categories {Category_ID, Column2, ...} Articles { Article_ID, Category_FK, Column3, ...} Discussions {Discussion_ID, Article_FK, Column3, ...} Now, all what I have is just category_ID value (Let us say 3), how can I do cascade delete to delete category's record that its ID = 3 and delete all articles and all discussions that found in that category?
View Replies !
Foreign Key On Delete Cascade
Hello,If I have table the following table structure:Table Customer ( CustomerId Numeric(10,0) Not Null, ... )Table CustomerOrders ( CustomerOrderId As Numeric(10,0) Not Null,CustomerId Numeric(10,0) Not Null,CustomerRefId Numeric (10,0) NotNull, ... )Now if I try to apply the following Foreign Keys, listed below, thefollowing error is displayed:"May cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION orON UPDATE NO ACTION, or modify other FOREIGN KEY constraints."ALTER TABLE CustomerOrdersADD CONSTRAINT FK_CustomerOrders_A FOREIGN KEY (CustomerId)REFERENCES Customer (CustomerId) ON DELETE CASCADEGOALTER TABLE CustomerOrdersADD CONSTRAINT FK_CustomerOrders_B FOREIGN KEY (CustomerRefId )REFERENCES CustomerRef (CustomerRefId ) ON DELETE CASCADEIn ORACLE this works fine, I consider this to be a bug in MSS 2k. Doesanybody know if microsoft is planning on changing this or if it works in"Yukon"?Thanks,Rob PanoshAdvanced Software Designs
View Replies !
Cascade Delete Question
I am attempting to clarify how the cascade delete works. For example I have a Parent table and two child tables used for lookup that have the ID_FK = ID_PK from the Parent table. tblUsers --------------- ID_PK Fname Lname ... tblUser_Phone ---------------- ID_FK Phone ... tbUser_Email ---------------- ID_FK Email ... If I needed to delete all records from tblUser_Phone would this delete my USER entry from the parent table tblUsers if I have cascade delete enabled? Thanks in advance.
View Replies !
Cascade Delete,any Limits
Hi , I have 4 GB db with 6 tables, table A parent of table B with cascade update,delete table B parent of C,D,E,F with cascade update,delete Today I deleted range of values(7000 rows) from table A, but NOT ALL data deleted from B,C,D,E,F Is any limitation for cascade delete Db set to simple recovery, no errors deleting data
View Replies !
Cascade Delete In Mssql 7.0?
Hello: One of my developers asked if there is a way in mssql 7.0 to have a cascade delete. Since triggers are fired after an action, I guess the only way to do this would be to write a stored procedure where the child(children) table rows are deleted first folowed by those of the parent. Does any one know of a way to perform a cascade delete other than by writing your own stored proc? Any information you can provide will be greatly appreciated. THanks. David Spaisman
View Replies !
Cascade Delete Problem..
Hi, I have a Configuration Table tblConfig with 45 fields- PK_Config_Id ConfigCreatedOn ConfigEditedOn ConfigStartDate ConfigEndDate ConfigFor €¦. €¦. €¦. And an Employee table tblEmployee PK_Emp_Id EmpName €¦. €¦. €¦. I would create a Configuration and then apply this config to some Employees €” So, I created a table tblEmployee_Config with 2 fields FK_Config_Id FK_Emp_Id But I MAY need to change(Edit) few of the properties(fields) of the Configuration record associated to an emp. Therefore I need all the fields of tblConfig table along with employee key in tblEmployee_Config So, I modified tblEmployee_Config to €” FK_Emp_Id FK_Config_Id ConfigCreatedOn ConfigEditedOn ConfigStartDate ConfigEndDate ConfigFor €¦. €¦. €¦. i.e. putting all the fields of the tblConfig into tblEmployee_Config along with emp key But instead of having 2 almost similar tables (tblConfig and tblEmployee_Config), I merged both the tables into 1 table having Configurations as well as Employees associated to Configs as€” PK_Config_Id FK_Emp_Id FK_Config_Id ConfigCreatedOn ConfigEditedOn ConfigStartDate ConfigEndDate ConfigFor €¦. €¦. €¦. Where FK_Emp_Id and FK_Config_Id would be NULL for Configurations. Like---- PK_Config_Id FK_Emp_Id FK_Config_Id AllowBrks WorkHrs 1 NULL NULL Yes 9 2 100 1 Yes 9 3 101 1 No 8 Here record 1 is a Configuration and records 2 and 3 are Emps records that are associated to Config 1. Now my question is €“ 1. Is it correct if I put a relation like Primary key Foreign Key tblEmployee_Config tblEmployee_Config PK_Config_Id FK_Config_Id 2. Can I perform cascade delete on the same table tblEmployee_Config such that€“ When record 1 is deleted, records 2 and 3 should also get deleted because they refer to the first record?
View Replies !
Relationships: Cascade Delete
Hi, I Use SQL Server 2005 Express edition. I have a few tables, which are inter-related. For e.g. I have a project table (ProjectID, Name, ...) I also have a Project invoice table, in which ProjectID is referred as Foreign key. Now, I want all the rows in Project invoice (child) table table to be deleted (for a particular ProjetID), if I delete the coresponding ProjectID in Projects (parent) table. If I use the 'on delete set null' or 'on delete cascade' constraint, only the ProjectID (in child table) is nullified whereas rest of the columns have data. Is there any way out, where the whole ROW (not just that field) is deleted? Or, is it that this can be achieved only by some stored procedure / external program? Thanks!
View Replies !
Cascade Delete Problem
Hi all, I've been reading for a couple of weeks but this is my first question so please be gentle with me. I'm pretty new to SQL Server, though I've worked with Access for years. I've got four tables (I'm simplifying) and I need to set up cascade deletes between them. I understand how to create foreign keys and all that, but SQL Server is telling me it can't create my keys because it will create multiple cascade paths. I understand that too, I just need to find a way around it. Here are the tables... dbo.JOBS Job_Number dbo.COLUMNS JOBS_Job_Number Column_Number dbo.ROWS JOBS_Job_Number Row_Number dbo.GRID_DATA JOBS_Job_Number Column_Number Row_Number Data Currently I have keys set to cascade delete between JOBS>COLUMNS and JOBS>ROWS so that when the user deletes a job, the columns and rows for that job are deleted as well. No problem there. If the user deletes a column, I have to delete all the grid data for that column and job. Same with rows. So I tried to establish cascade deletes between COLUMNS>GRID_DATA and ROWS>GRID_DATA and that's where I got in trouble. I assume the reason is that if the user deletes a job, it's going to delete the columns and rows, and I've got multiple cascade paths coming from COLUMNS and ROWS to GRID_DATA. I get it. The question is, what can I do about it? I don't have a cascade delete between JOBS>GRID_DATA, although I do want that behavior, but I was assuming it just would flow through COLUMNS or ROWS. I haven't bothered to add that key as I'm guessing that would just make the situation worse. Anyone got any advice for me on this one? Thanks! Ron Moses ConEst Software Systems
View Replies !
SQL Server 2005 No Cascade On Delete
I am developing an application with ASP .NET 2.0 using SQL Server 2005. I have a very detailed set of relational tables I would like to use to store my data, however I would like to use the built in Membership functionality of .NET 2.0.What I've done is created MembershipProvider that on user creation links the aspnet_Users table to my custome User table.UserFK guid aspNetId int userIdI then use my own userId to link into other data which uses their PKs to link to other tables, etc, etc like any good relational DB should.My question is when I delete a user using the ASP .NET interface I get a contraint error because my User table references the aspnet_Userts.UserId. I initially wanted to toss Cascade On Delete on my tables which would take care of my problem. I was shocked to find out Sql Server 2005 doesnt support Cascades.. and its suggested that Triggers be used instead (ill durned if im gonna make triggers for all my tables)My question is, what is the best practice in deleting from tabled linked with PK/FK relationships? There MUST be a good way to handle this.thanks
View Replies !
MS ACCESS - ON DELETE CASCADE Problem
I cannot execute my sql to create a table with ON DELETE CASCADE option.Here is my sql:CREATE TABLE Employees (Name Text(10) not null, Age number,CONSTRAINT pkEmployeesPRIMARY KEY (Name)); <--- This is ok!CREATE TABLE CanTake (Name Text(10) not null, Make Text(10) not null,CONSTRAINT pkCanTakePRIMARY KEY (Name, Make),CONSTRAINT fkCanTakeFOREIGN KEY (Name) REFERENCES EmployeesON UPDATE CASCADE <--- MS-ACCESS says 'syntax error'ON DELETE CASCADE);When I didn't include ON UPDATE CASCADE ON DELETE CASCADE in sql, it works(but that's not what I want). Why? Could anyone suggest me?Thank you.--Message posted via http://www.sqlmonster.com
View Replies !
On Delete Cascade && Hierarchical Table
for MS SQL 2000 I am trying to do a hierarchical table and i want to add a ON DELETE CASCADE CREATE TABLE [dbo].[Users]( [id_Users] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, [id_UsersSup] [int] NULL, [Users] [nvarchar] (100) NOT NULL ) ON [PRIMARY] ALTER TABLE [dbo].[Users] ADD CONSTRAINT [FK_Users_Sup] FOREIGN KEY ( [id_UsersSup] ) REFERENCES [Users] ( [id_Users] ) ON DELETE CASCADE but MS SQL refuse to create the foreign key even if there is 4 levels under the deleted id_Users I want to delete all the rows on all levels under thank you for helping
View Replies !
Find All Cascade Delete Constraints
I've been handed a database with over 100 tables and told to find everywhere a cascade delete constraint exists. I could just go through every table by hand and check, but I think there must be an easier way, perhaps an sql query on the master db. Any thoughts?
View Replies !
Cascade Delete Trigger On Same Table
I have 4 colums in a table Project, Sections,Tasks,Subtasks Each subtask will haven a row. I need to write a trigger when I delete a task it needs to delete all the subtasks relating to it. When I delete a section it needs to delete all the tasks and subasks relating to it. similarly for project. This trigger for task-subtask works. CREATE TRIGGER "[Deletetasktrigger]" ON [Tbl] FOR DELETE AS SET NOCOUNT ON /* * CASCADE DELETES TO '[Tbl B]' */ DELETE [tbl] FROM deleted, [Tbl] WHERE deleted.[task] = [Tbl].[task] THis works fine. But when I do it for sections I get this error. "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)" .. Help Please!!!!!!
View Replies !
Cascade Delete From Child To Parent
Hi All, I have the following db design: a Primary table say CityTable CityId Pk CityName SectorTable SectorID CityID FK to CityTable SectorName MuncipalTable MunicipallD CityID FK to CityTable MuncipalName CantonmentTable CantonmentlD CityID FK to CityTable CantonmentName The CityId can spread across multiple tables. I would like to delete the record from CityTable if all references of CityId have been removed. For e.g CityTable CityId: 1, 2 CityName: Pune, Mumbai SectorTable SectorID: 1a, 2a, 3a CityID: 1, 2, 1 SectorName: Sec1, Sec2, Sec3 MuncipalTable MunicipallD: 1c, 2c, 3c CityID: 2, 1, 1 MuncipalName: abc, def, hij So If I delete SectorID 1a, the CityTable should not delete its record since SectorID 2a and MuncipalID 2c and 3c are still referencing it. But If delete all the records from SectorTable and MuncipalTable referencing CityID 1 then the record from CityTable should get deleted. How can I achieve this using a SP? Please help. Thanks & regards Sunil
View Replies !
Performance Impact When Using On Delete Cascade
I want to use "on delete cascade" in one of my tables but I'm worried though whether this can affect the performance when having millions of records. To explain more I'm working on a social networking website and I have two tables UserAccounts, in which I only keep the username and password and a few related fields, and Profiles in which I keep the profile data for users, I want to be sure that I won't have any records in the Profiles table without corresponding records in the UserAccounts table. Please see the DDL below to understand more the structure of the tables: CREATE TABLE UserAccounts ( UserID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, UserName VARCHAR(20) NOT NULL, Password VARCHAR(20) NOT NULL, --other fields (e.g. last login .. etc) ) CREATE TABLE Profiles ( UserID INT NOT NULL REFERENCES UserAccount(UserID), -- other fields (e.g. birthdate, nationality .. etc) ) Any suggestions are highly appreciated...
View Replies !
Foreign Key Cascade Delete Failure
I have a SQL Mobile DB that I am having problems with deletes cascading via foreign keys to delete all child records. The DB is on a WIndows CE5 device that is running a C#.net application. The are three tables in my DB that relate to this issue (Tests, TestRawInfo, and TestRawData). The Tests table is the main table. TestRawInfo is a child table of Tests and has a foreign key defined that references the Tests primary key (the relationship is 1:1 with the Tests table). TestRawData is a child table of TestRawInfo and has a foreign key defined that references the TestRawInfo primary key (The relationship is 1:many with the TestRawInfo table). All foreign keys are defined with a Cascade on delete. When I delete one or more records from the Tests table I expect the delete to cascade so that all child records are also deleted. Not all the data gets deleted from the TestRawData table, this results in orphan records. I only see a failure however the next time I attempt to compact the database. Interestingly I can reproduce the problem by opening my Mobile DB in SQL 2005 on my Desktop and deleting data from the Tests table. If however, I add additional records to these tables through SQL 2005 before attempting to delete, the delete works as expected. Do you have any ideas on what is going on here? Has any one else reported a similar issue? My current work around is to delete data directly from child tables and not rely on the foreign keys to cascade the deletes.
View Replies !
Cascade Delete Can Not Apply To Two Foreign Keys
I have a table that contains two foreign keys of two different tables. I want to build a relationship so that when either primary keys deleted in the two tables, the record in the table should be deleted. But, SQL Server does not allow me to save the relationship, it complains that the circling delete might exist. I do not know why, how can I solve this? Table A: ID ProductID <foreign key> CustomerID <foreign key> Table Product ProductID <primary key> Table Customer CustomerID <primary key> I want to cascade delete the record in Table A when either the ProductID is deleted from Product table or the CustomerID is deleted from Customer table.
View Replies !
Problem With Delete Cascade For Self-referencing Tables
Hi, I have a self-referencing table, something like Emlplyee-Manager. I want to implement the cascading delete in this table. So when I delete a manager than all the employees should be deleted at ANY level below the manager. I do not have DRI for the Foreignkey (manager)!!! My problem is that the trigger fires only one time (for the "sons") and not for the "grandsons" and below. I've used the following flags: - nested triggers (in "SQL Server Properies", "Server settings" tabsheet) - recursive triggers (database "Properties", "Options" tabsheet) I've combined this 2 flags: 1. nested flag reset and recursive flag reset 2. nested flag reset and recursive flag set or 3. nested flag set and recursive flag reset 4. nested flag set and recursive flag set The results are the following: -for case 1, 2 and 3 the same results: the trigger fires just for the first level (the sons are deleted but the grandsons remains there orphan) -for case 4 I have the following error message: "Maximum stored procedure nesting level exceeded (limit 32)" so I can't delete anything at all. Any idea why the trigger doesn't fire for "grandsons" and below? Could be that this above mentioned flags doesn't works for self-referencing tables ? Thanks in advance
View Replies !
Cascade Delete Contraints - Accessible Through TSQL?
Hi all, I was wondering if there is an easy way to loop through all contraints in a database and programmatically set the cascade delete to ON. I have a database with hundreds of contraints, so individually setting cascade delete on them is not optimal. Thanks for any info in advance! I think that the constraints are simply held in one of the system datatables, is there anyway to simply update that table?
View Replies !
SQL Server 2005 - Can't Get CASCADE DELETE To Work
I'm using SQL Server 2005 (product version = 9.00.1406.00, product level = RTM, and edition = Developer Edition). I have a db with a number of tables; I created a Foreign Key in one table and added a Foreign Key w/ ON DELETE CASCADE to it -- all using Microsoft SQL Server Management Studio. When I delete the record in the table with the foreign key, the record in the other table does not get deleted. I tried doing this with a simple SQL script in Microsoft SQL Server Management Studio and in a simple .Net / ADO (C#-based) program. Only the record in the table that I'm specifically deleting is deleted. Here's the table that is referenced by the foreign key (I told Server Management Studio to write out the script): USE [CHNOPSDb] GO /****** Object: Table [dbo].[tblDeviceContainer] Script Date: 08/13/2007 16:47:03 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblDeviceContainer]( [ID] [int] IDENTITY(1,1) NOT NULL, [DeviceContainerTypeID] [int] NOT NULL, CONSTRAINT [PK_tblDeviceContainer] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] Here's the table that has the foreign key to the above table (again, I told Management Studio to write out the script): USE [CHNOPSDb] GO /****** Object: Table [dbo].[tblNode] Script Date: 08/13/2007 16:46:40 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tblNode]( [ID] [int] IDENTITY(1,1) NOT NULL, [NodeName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [NodeTypeID] [int] NOT NULL, [UnitID] [int] NOT NULL, [pDeviceContainerID] [int] NOT NULL, [NodeIndex] [int] NULL, CONSTRAINT [PK_Node] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO USE [CHNOPSDb] GO ALTER TABLE [dbo].[tblNode] WITH CHECK ADD CONSTRAINT [FK_tblNode_tblDeviceContainer] FOREIGN KEY([pDeviceContainerID]) REFERENCES [dbo].[tblDeviceContainer] ([ID]) ON DELETE CASCADE I then perform a delete using the following: Use CHNOPSDb; delete from tblNode where ID = 1; It deletes the tblNode record but doesn't delete the tblDeviceContainer record that is referenced by tblNode. Any help? Thanks, Bill
View Replies !
Trigger Not Firing On Cascade Delete Table
I have three tables: BulkMemberHeader - which has a cascade delete on BulkMemberDetail of any related records BulkMemberDetail €“ which has a DELETE trigger which gets the member ID from deleted and deletes the member record from the member table Member This issue: > When I delete a record from BulkMemberDetail the trigger fires and deletes the record from the Member table as it should > If I delete a record from the BulkMemberHeader, all corresponding records in BulkMemberDetail are deleted, but the trigger to delete the record in the Member table does not seem to fire Is it a limitation on SQLServer 2000 that does not allow triggers to fire in a scenario like this? Any suggestions or comments would be great. Thanks, Mike
View Replies !
Conflict Between (cascade) DELETE Trigger And Foreign Key Constrain
I'm trying to create relational database with some triggers in SQL Server 7.0, but it doesn't work as expected. Let's say that I have 'Office' database with two tables, 'Users' and 'UserRights' (userRights table should have much more rights, but that's not relevant for this problem): CREATE TABLE [Users] ( [FS_Username] [nvarchar] (8) NOT NULL , [FS_Password] [nvarchar] (32) NOT NULL , CONSTRAINT [PK_Users] PRIMARY KEY NONCLUSTERED ( [FS_Username] ) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [UserRights] ( [FS_Username] [nvarchar] (8) NOT NULL , [FI_UserType] [int] NOT NULL CONSTRAINT [DF_UserRights_FI_UserType] DEFAULT (1), [FI_AllowLogin] [int] NOT NULL CONSTRAINT [DF_UserRights_FI_AllowLogin] DEFAULT (1), CONSTRAINT [PK_UserRights] PRIMARY KEY NONCLUSTERED ( [FS_Username] ) ON [PRIMARY] , CONSTRAINT [FK_UserRights_Users] FOREIGN KEY ( [FS_Username] ) REFERENCES [Users] ( [FS_Username] ) ) ON [PRIMARY] GO Foreign Key CONSTRAIN above is created by adding both tables to the diagram and defining relationship between these two tables FS_Username field, where 'Enable relationship for INSERT and UPDATE' option is turned ON. You can easily see this if you create diagram youself and insert these two tables in it. Next to this, I created two triggers that should handle inserting/deleting rows in UserRights table as consequence of inserting/deleting rows in Users table: CREATE TRIGGER InsertUserRights ON Users FOR INSERT AS BEGIN INSERT INTO UserRights (FS_Username) (SELECT FS_Username FROM Inserted) END CREATE TRIGGER DeleteUserRights ON Users FOR DELETE AS BEGIN DELETE UserRights WHERE FS_Username IN (SELECT FS_Username FROM Users) END Now, when (manually) I insert row in Users table, UserRights table gets updated accordingly. HOWEVER, when I try to delete one or more entries from Users table, I get error report. For example, if you try to execute following two commands: Insert Into Users (FS_Username, FS_Password) VALUES ('John', 's') Delete from Users ... first command will succede, but second one will fail with message: DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_UserRights_Users'. The conflict occurred in database 'Office', table 'UserRights', column 'FS_Username'. The statement has been terminated. Does anyone know how to resolve this problem without loosing constrains and triggers ? (If I turn off 'Enable relationship for INSERT and UPDATE' option for relationship, things will work fine, but than I can make inconsistent data in UserRights table). tnx a lot, Dejan
View Replies !
Does SQL Server 7 Support Cascade Delete Referenctial Action?
Hi, every guru, I am new to SQL server 7. Recently I need to design a 400-or-so table database. I need an automatic method to delete some relative data in all those table. Can anybody tell me: Does SQL server 7 support cascade delete referenctial action? Any clue will be highly appreciated. Thanks in advance. Alan
View Replies !
Overriding ForeignKey Constraints Under Certain Conditions To Perform CASCADE DELETE
Hi, We have a DB with a heirarchy of tables each connected via Guids and controlled for Delete Operations by Foreign Key Constraints. However, under certain privileged conditions we would like CERTAIN users to be able to perform a Cascade Delete all the way down. Thus by example we have tables A,B,C,D - Typically once a record is inserted into Table A then we can add multiple records in Table B referencing that new record in Table A...and so on through records in Table C referencing Table B...records in Table D referencing table C. Now when we try and delete that ONE Record in Table A we 'could' implement CASCADE DELETES which would delete all those records in Table B,C,D. We decided that was too dangerous to implement as Whooosh....all records could be deleted by a non-privileged user deleteing that ONE records in Table A, so we changed the Cascade DELETE to NO ACTION. However, there are times (hah...particularly in Testing !) when we would like a privilegd user to be able to DO that task...i.e. perform a CASCADE delete. Has anybody got any suggestions on how this would be best, easily and securely implemented. I have had a brief look at INSTEAD OF Triggers but am not sure about the pitfalls of using this... ANy help appreciated, Cheers, Desmond.
View Replies !
VS2005 - Using Detailsview To Update, Insert And Delete Rows From SQL 2005 Database. Delete And Insert Work But Update Does Not - No Errors Returned
Using VS 2005 DetailsView to insert, delete, and update rows in SQL 2005 database. insert and delete work but update does not. I recieve no errors and the detailsView comes back unchanged (as well as table row is unchanged). I am trying to use as little code behind as possible. However I do have ItemUpdating routines that seem to work (i.e Checking table for new login duplicates and encrypting passwords). The following is the source code generated by VS2005:<%@ Page Language="VB" AutoEventWireup="false" CodeFile="frmDbRegionMgrNew.aspx.vb" Inherits="frmDbRegionMgrNew" Title="Region Manager DB Update" Theme="detailsVeiwTheme" %><%@ Import Namespace="System.Data" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"><title>Untitled Page</title> </head><body><form id="form1" runat="server"><div><asp:Label ID="Label1" runat="server" Font-Bold="True" Font-Size="Large" ForeColor="#004000"Style="z-index: 101; left: 14px; position: absolute; top: 10px" Width="199px">Data Base Maintenance </asp:Label><asp:HyperLink ID="HyperLink1" runat="server" BackColor="ActiveBorder" BorderColor="ActiveBorder"BorderStyle="Outset" Font-Bold="True" Font-Size="X-Small" ForeColor="#004000"Height="31px" NavigateUrl="DataBaseMaint.aspx" Style="z-index: 133; left: 524px;position: absolute; top: 7px" Width="96px">DB Main Menu</asp:HyperLink><br /><br /><br /><table style="width: 654px"><tr><td style="width: 120px"><asp:Label ID="Label2" runat="server" Font-Bold="True" Font-Size="Large" ForeColor="#004000"Text="Regional Manager" Width="189px"></asp:Label></td><td style="width: 100px"></td><td style="width: 203px"></td></tr><tr><td style="width: 120px" valign="top"><asp:Label ID="Label3" runat="server" Font-Bold="True" Font-Size="Small" ForeColor="#004000"Style="z-index: 128; left: 2px; position: absolute; top: 115px" Width="128px">Select Greenhouse -></asp:Label></td><td style="width: 100px" valign="top"><asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource1"DataTextField="Name" DataValueField="GrnHseID"></asp:DropDownList><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MetrolinadataConnectionString %>"SelectCommand="SELECT [GrnHseID], [Name] FROM [Greenhouse]"></asp:SqlDataSource></td><td style="width: 203px"> <asp:DetailsView ID="DetailsView1" runat="server" AllowPaging="True" AutoGenerateRows="False"DataKeyNames="RegionMgrID" DataSourceID="SqlDataSource2" Height="50px" Width="125px"><Fields><asp:TemplateField HeaderText="RegionMgrID" InsertVisible="False" SortExpression="RegionMgrID"><EditItemTemplate><asp:Label ID="Label1" runat="server" Text='<%# Eval("RegionMgrID") %>'></asp:Label></EditItemTemplate><ItemTemplate><asp:Label ID="Label1" runat="server" Text='<%# Bind("RegionMgrID") %>'></asp:Label></ItemTemplate></asp:TemplateField><asp:TemplateField HeaderText="GrnHseID" SortExpression="GrnHseID"><EditItemTemplate><asp:Label ID="Label2" runat="server" Text='<%# Eval("GrnHseID") %>'></asp:Label></EditItemTemplate><InsertItemTemplate><asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("GrnHseID") %>'></asp:TextBox></InsertItemTemplate><ItemTemplate><asp:Label ID="Label2" runat="server" Text='<%# Bind("GrnHseID") %>'></asp:Label></ItemTemplate></asp:TemplateField><asp:BoundField DataField="DispLvl" HeaderText="DispLvl" SortExpression="DispLvl" /><asp:BoundField DataField="FName" HeaderText="FName" SortExpression="FName" /><asp:BoundField DataField="Minit" HeaderText="Minit" SortExpression="Minit" /><asp:BoundField DataField="LName" HeaderText="LName" SortExpression="LName" /><asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone" /><asp:BoundField DataField="Ext" HeaderText="Ext" SortExpression="Ext" /><asp:BoundField DataField="Cell" HeaderText="Cell" SortExpression="Cell" /><asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" /><asp:BoundField DataField="Login" HeaderText="Login" SortExpression="Login" /><asp:BoundField DataField="Password" HeaderText="Password" SortExpression="Password" /><asp:BoundField DataField="AccessLvl" HeaderText="AccessLvl" SortExpression="AccessLvl" /><asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="True" /></Fields></asp:DetailsView><asp:SqlDataSource ID="SqlDataSource2" runat="server" ConflictDetection="CompareAllValues"ConnectionString="<%$ ConnectionStrings:MetrolinadataConnectionString %>" DeleteCommand="DELETE FROM [RegionMgr] WHERE [RegionMgrID] = @original_RegionMgrID AND [GrnHseID] = @original_GrnHseID AND [DispLvl] = @original_DispLvl AND [FName] = @original_FName AND [Minit] = @original_Minit AND [LName] = @original_LName AND [Phone] = @original_Phone AND [Ext] = @original_Ext AND [Cell] = @original_Cell AND = @original_Email AND [Login] = @original_Login AND [Password] = @original_Password AND [AccessLvl] = @original_AccessLvl"InsertCommand="INSERT INTO [RegionMgr] ([GrnHseID], [DispLvl], [FName], [Minit], [LName], [Phone], [Ext], [Cell], , [Login], [Password], [AccessLvl]) VALUES (@GrnHseID, @DispLvl, @FName, @Minit, @LName, @Phone, @Ext, @Cell, @Email, @Login, @Password, @AccessLvl)"OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [RegionMgr] WHERE ([GrnHseID] = @GrnHseID)"UpdateCommand="UPDATE [RegionMgr] SET [GrnHseID] = @GrnHseID, [DispLvl] = @DispLvl, [FName] = @FName, [Minit] = @Minit, [LName] = @LName, [Phone] = @Phone, [Ext] = @Ext, [Cell] = @Cell, = @Email, [Login] = @Login, [Password] = @Password, [AccessLvl] = @AccessLvl WHERE [RegionMgrID] = @original_RegionMgrID AND [GrnHseID] = @original_GrnHseID AND [DispLvl] = @original_DispLvl AND [FName] = @original_FName AND [Minit] = @original_Minit AND [LName] = @original_LName AND [Phone] = @original_Phone AND [Ext] = @original_Ext AND [Cell] = @original_Cell AND = @original_Email AND [Login] = @original_Login AND [Password] = @original_Password AND [AccessLvl] = @original_AccessLvl"><DeleteParameters><asp:Parameter Name="original_RegionMgrID" Type="Int32" /><asp:Parameter Name="original_GrnHseID" Type="Int32" /><asp:Parameter Name="original_DispLvl" Type="Int32" /><asp:Parameter Name="original_FName" Type="String" /><asp:Parameter Name="original_Minit" Type="String" /><asp:Parameter Name="original_LName" Type="String" /><asp:Parameter Name="original_Phone" Type="String" /><asp:Parameter Name="original_Ext" Type="String" /><asp:Parameter Name="original_Cell" Type="String" /><asp:Parameter Name="original_Email" Type="String" /><asp:Parameter Name="original_Login" Type="String" /><asp:Parameter Name="original_Password" Type="String" /><asp:Parameter Name="original_AccessLvl" Type="Int32" /></DeleteParameters><UpdateParameters><asp:Parameter Name="GrnHseID" Type="Int32" /><asp:Parameter Name="DispLvl" Type="Int32" /><asp:Parameter Name="FName" Type="String" /><asp:Parameter Name="Minit" Type="String" /><asp:Parameter Name="LName" Type="String" /><asp:Parameter Name="Phone" Type="String" /><asp:Parameter Name="Ext" Type="String" /><asp:Parameter Name="Cell" Type="String" /><asp:Parameter Name="Email" Type="String" /><asp:Parameter Name="Login" Type="String" /><asp:Parameter Name="Password" Type="String" /><asp:Parameter Name="AccessLvl" Type="Int32" /><asp:Parameter Name="original_RegionMgrID" Type="Int32" /><asp:Parameter Name="original_GrnHseID" Type="Int32" /><asp:Parameter Name="original_DispLvl" Type="Int32" /><asp:Parameter Name="original_FName" Type="String" /><asp:Parameter Name="original_Minit" Type="String" /><asp:Parameter Name="original_LName" Type="String" /><asp:Parameter Name="original_Phone" Type="String" /><asp:Parameter Name="original_Ext" Type="String" /><asp:Parameter Name="original_Cell" Type="String" /><asp:Parameter Name="original_Email" Type="String" /><asp:Parameter Name="original_Login" Type="String" /><asp:Parameter Name="original_Password" Type="String" /><asp:Parameter Name="original_AccessLvl" Type="Int32" /></UpdateParameters><SelectParameters><asp:ControlParameter ControlID="DropDownList1" Name="GrnHseID" PropertyName="SelectedValue"Type="Int32" /></SelectParameters><InsertParameters><asp:Parameter Name="GrnHseID" Type="Int32" /><asp:Parameter Name="DispLvl" Type="Int32" /><asp:Parameter Name="FName" Type="String" /><asp:Parameter Name="Minit" Type="String" /><asp:Parameter Name="LName" Type="String" /><asp:Parameter Name="Phone" Type="String" /><asp:Parameter Name="Ext" Type="String" /><asp:Parameter Name="Cell" Type="String" /><asp:Parameter Name="Email" Type="String" /><asp:Parameter Name="Login" Type="String" /><asp:Parameter Name="Password" Type="String" /><asp:Parameter Name="AccessLvl" Type="Int32" /></InsertParameters></asp:SqlDataSource> </td></tr><tr><td style="width: 120px"></td><td style="width: 100px"></td><td style="width: 203px"></td></tr></table></div></form></body></html>Appreciate any and all help!Dave
View Replies !
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.
View Replies !
On Update Cascade
Hello all, I am new to SQL and I was hoping someone could explain something to me about 'on update cascade'. I understand what 'on update cascade' does (i think) - it updates the child table when the parent table is updated. What i do not understand is that the 'on update cascade' works on the primary key of the parent table, but i was on the understanding that the primary key doesn't change often if at all so why would the 'on update cascade' be of use? Sorry for my ignorance and i realise i must be missing something simple but would be grateful for any help. Thanks
View Replies !
Cascade Update / Foreign Key
Hi!For the sake of simplicity, I have three tables, Employee, Department andWorkEmployee >---- Department / /^ ^WorkThe Work table have two columns, empno and depno and consists that theemployee 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 cascadealter table work add constraint fk_work_employee foreign key (empno)references employee (empno)on update cascadealter table work add constraint fk_work_department foreign key (depno)references department (depno)on update cascadeMy problem is the last command. SQL Server responds:Server: Msg 1785, Level 16, State 1, Line 1Introducing FOREIGN KEY constraint 'fk_work_department' on table 'work' maycause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ONUPDATE NO ACTION, or modify other FOREIGN KEY constraints.But I want the depno in the work table to be updated when a department.depnochanges a value.Does anyone have a suggestion on how to overcome this problem?Thanks in advanceBest regards,Gunnar VøyenliEDB-konsulent asNORWAY
View Replies !
Cascade Update/deletes
Hi guys, A simple question of good practices: is it better to have a cascade update/delete on a table? Or is it better to do it "by hand" in a SP? Thanks a lot.
View Replies !
Trigger Cascade Update
i run this cmd to execute a cascade update to another database but did not work. USE [testdb1] GO /****** Object: Trigger [dbo].[tgr_Upddb2] Script Date: 07/29/2007 08:34:47 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER TRIGGER [dbo].[tgr_Upddb2] ON [dbo].[Table1] FOR UPDATE AS BEGIN SET NOCOUNT ON UPDATE U SET u.fk_fld = i.pk_fld FROM inserted AS I JOIN testdb2.dbo.table2 AS U ON U.fk_fld = I.pk_fld END heres the db structure
View Replies !
Problem With Cascade Update
i want to reffer A1,A2 column from a Table A to B1 column of Table B. when i use the on update cascade option for the columns A1 & A2 i get an error for the 2nd one. sql: alter table A add constraint fk_key1 foreign key (a1) references b(b1) on update cascade; alter table A add constraint fk_key2 foreign key (a2) references b(b1) on update cascade; after executing the sql i get the following error for the 2nd sql: Introducing FOREIGN KEY constraint 'FK_key2' on table 'A' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. if i dont use cascade option then i dont get any error. but i need to synchronize the data. now can anyone help me how to add the cascade option working for the 2nd column of Table A without seperating the column in a different table? thnx S ]-[ /- | ]-[ /- N
View Replies !
|
TRACKER
