We are developing a new oltp application using SQL Server 2000 and are debating whether to use "cascade delets" or not. In earlier apps, we had issues using cascade deletes like someone deleted parent by mistake and all child records got deleted OR SQL Server crashed in middle of cascade delete and records were lost or performance became an issue when there were huge # of child records to be deleted,etc.
Are there any recommendations for/against using Cascade deletes and when to use and when NOT to use cascade deletes ?
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
Where Trace_EnterId is the parent ID and BubbledTraceEnter_Id is the child ID. My test case has nested trace lines that go down 5 levels, but when I delete the top trace line, only the child directly under it is deleted leaving the other 3 as orphans. Here's the trigger:
ALTER TRIGGER [dbo].[DTrig_xTrace] ON [dbo].[ix_Trace] FOR Delete AS BEGIN SET NOCOUNT ON; DELETE FROM ix_Trace FROM ix_Trace INNER JOIN deleted as dt ON ix_Trace.Trace_BubbledTraceEnter_Id = dt.Trace_EnterId
I need some suggestions concerning the issue cascading deletes in a self-referencing table, i.e. a table with a foreign key pointing at the primary key in itself. Although SQL Server still does not support cascading deletes the declarative way there are some other ways to handle this situation. One common way is to use trigger coding with the simple structure CREATE TRIGGER DelCascadeTrig ON self_ref_tab FOR DELETE AS DECLARE @C_FK xxxxxx SELECT @C_FK = C_FK FROM DELETED BEGIN DELETE self_ref_tab FROM self_ref_tab, deleted WHERE self-_ref_tabC_FK = deleted.C_PK END
where C_PK is the primary key column and C_FK the foreign key column. The problem is that this simple pattern does not work with self-referencing tables, because the removal of dependent rows deeper layer (n-2, n-3 etc. if the originating delete request is level n and the first level of dependant deletes handled by the trigger code is n-1) would require the delete trigger to fire more than once for the same delete operation. As far as I know, in ver. 6.x triggers are executed only once per SQL statement and in this case the n-2, n-3 etc. level rows would have been left as "orphans".
In ver. 7.0 I suppose this should work fine because of the new recursive trigger execution possibility (trigger will fire up to 32 recursive times per SQL statement), but in the meanwhile (i.e. my case util we have upgraded all our servers) the delete logic for a cascading, self-referencing relationship must be handled completely within one execution of the trigger.
My question is now: does anyone know anything about any common algorithm or trigger code example solving this problem.
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?
I haven't used cascading deletes in the past but we're starting a new database and it seems like a good way to go to keep data clean. Or at least it did seem like a good way until I ran some tests. I have 3 tables.
In this structure, the peopleemails table is simply an association table between the email and people table. I have setup up relationships in a diagram so that when a person is deleted, it cascades to peopleemails and removes the entry there. I also had a cascade set up hoping that when an entry was deleted from peopleemails, it would remove it from the email table but this is not happening. The relationship between email and peopleemail is primary key table email.emailid and foreign key table peopleemail.emailid. Is there a way to get this to work to remove the email address if a peopleemail entry is removed? Thanks.
I want to use one stored procedure to delete rows in three different tables - how do I write this for Sql Server 2000 please? PROCEDURE dbo.DeleteSubmission @C_ID intASDELETE *FROM tblCompaniesWHERE C_ID = @C_ID DELETE *FROM tblStoresWHERE CS_ID = @C_ID DELETE *FROM tblDistributorsWHERE CD_ID = @C_ID RETURN
I'm hoping someone has seen this before because I have no idea what could be causing it. I have an SQL 2005 database with multiple tables and several triggers on the various tables all set to run after insert and update. My program inserts a record into the "items" via a SP that returns the index of the newly added row. The program then inserts a row into another table that is related to items. When the row is inserted into the second table it gets an error that it cannot insert the record because of a foreign key restraint. Checking the items table shows the record that was just inserted in there is now deleted. The items record is only deleted when I have my trigger on that table enabled. Here is the text of the trigger: GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER TRIGGER [dbo].[TestTrigger]ON [dbo].[items]AFTER INSERT AS BEGIN SET NOCOUNT ON; INSERT INTO tblHistory(table_name, record_id, is_insert) VALUES ('items', 123, 1) END tblHistory's field types are (varchar(50), BigInt, bit). As you can see there is nothing in the trigger to cause the items record to be deleted, so I have no idea what it could be? Anyone ever see this before? Thanks in advance!
I have new client we changed the file retention period on for log ship on SQL 2000. Now the files are not being deleted. I looked at all the sp's but could not finde the one that actually deletes the log file that was copied over. I could just rebuild log ship but this is on a prod box, was trying to figure out what and when the files get deleted, and by what sp
I keep data in four seperate locations, each with different data retention. the problem is, i keep the publisher "thin" - only 24 hrs of data) the data in the relevant tables is about 250000 a day) and these deletes are blocked at the subscribers by commenting out the body of the repl proc. this does however cause a burst in bandwith & locks the subscribers, even though no actual delete takes place. does anyone know if you can actuaklly NOT REPLICATE delete commands for specified tables at all - so that the message of the delete wont even be SENT to the subscriber?
We have a Master table in the Db who's PK is referenced in at least 60 tables as FK. We want to delete all the dependent records in 1 go without using multiple delete statements or any cursor or loop.
Table table_2 is an intersection table between table_1 and another lookup table (called task). Whenever I delete from table_1 I need to delete from table_2 to maintain integrity. The reason I do not have clustered indexes on the tables is b/c they start out empty (every day a housekeeping task deletes from them. Unfortunately I cannot change this...).
In my (web/jsp) application before I delete, I select from table_1 to get data I need to remember after the delete. In order to avoid the "select for update problem", I use the "UPDLOCK" hint in the select like this:
select * from table_1 WITH (UPDLOCK), table_2 WITH (UPDLOCK) where table_1.id = table_2.table_1_id " and table_1.id = ? and table_2.task_id = ?
I then issue my deletes in the following order:
delete from table_1 where task_id = ? and table_1_id = ?
and then,
delete from table_1 where id = ?
The application sets the isolation level to READ COMMITTED. The above 3 sql statements are executed as part of a transaction. Occasionally, when I drive the application, I get a deadlock. This happens intermittently. I have been able to reproduce the problem by attempting to delete records really fast, one after the other from the (web) front end. When the deadlock occurs these are the locks that are held by the running processes:
Blocked SPID (10): ============
Object Lock Type Mode Status Owner Index Resource app DB S GRANT Sess TABLE_2 app.dbo.TABLE_1 TAB IX GRANT Xact TABLE_2 app.dbo.TABLE_1 KEY U GRANT Xact PK_TABLE_2 (a400f609034c) app.dbo.TABLE_1 RID U GRANT Xact TABLE_2 1:157:3 app.dbo.TABLE_1 PAG IU GRANT Xact PK_TABLE_2 1:443 app.dbo.TABLE_1 PAG IU GRANT Xact TABLE_2 1:157 app.dbo.TABLE_2 KEY S WAIT Xact PK_TABLE_2 (6501eda29ac1) app.dbo.TABLE_2 KEY S GRANT Xact PK_TABLE_2 (ef007b1066ea) app.dbo.TABLE_2 TAB IS GRANT Xact TABLE_2 app.dbo.TABLE_2 PAG IS GRANT Xact PK_TABLE_2 1:252
Blocking SPID (12): ============= Object Lock Type Mode Status Owner Index Resource app DB S GRANT Sess TABLE_2 app.dbo.TABLE_2 KEY X GRANT Xact PK_TABLE_2 (6501eda29ac1) app.dbo.TABLE_2 RID X GRANT Xact TABLE_2 1:176:3 app.dbo.TABLE_2 TAB IX GRANT Xact TABLE_2 app.dbo.TABLE_2 PAG IX GRANT Xact PK_TABLE_2 1:252 app.dbo.TABLE_2 PAG IX GRANT Xact TABLE_2 1:176
I could not figure out what the problem is. Please help me with this. Any help will be much appreciated.
Hello all. We are just getting started with replication and I'mwondering if there is a way to not have deletes replicated. I know Icould manually remove the delete trigger from every table but I'mintrested in have this a bit more automatic (like when its creating thesubscription).ThanksStephen
I have the following tableCREATE TABLE [tbl_Items]([item_id] int IDENTITY(1,1) CONSTRAINT PK_tbl_Items__item_idPRIMARY KEY,[parent_id] int DEFAULT(NULL) CONSTRAINTFK_tbl_Items__item_id__parent_id REFERENCES [tbl_Items]( [item_id] ) ONDELETE NO ACTION ON UPDATE NO ACTION)My Intention was to create a table that when I delete a record, allrecords that have on the [parent_id] field the deleted record[item_id].I am trying to avoid having to use triggers or create a storedprocedure that firsts delete the children (recursively) and thendeletes the parent.Is there any way to do this by changing my table definition here?
Hi, I'm using SQL server 2000, and I have set up two tables, table Aand table B. Table A and B have a foreign key constraint such that ifan entry is deleted in table A, then all the entries in table Bassociated with that entry are deleted as well. I imagine that I canfind out the number of records that are deleted in table A when Iexecute the SQL, but is there an easy way of determining the number ofrecords that will be deleted in table B?Thanks,Dan
I seem to be having a problem on all of my SQL servers. WHen I or a developer attmept to do a delete on a table i get a Log file for database is full. I truncate the log try again and get the same error. IT doesnt seem to matter how much is being deleted or how big the table is. THis is very strange and very frustrating.
Microsoft article Q142480 states "Triggers cannot be used to perform cascading updates and deletes if ForeignKey-to-PrimaryKey relationships have been extablished using SQL Server's DRI."
Does this mean that I cannot declare FK's in my scripts if I want to have triggers in the table? Do I just add a column in my table that will have a foreign key in it, but just not reference it in my script? Can someone clarify for me?
I was testing how the WITH CHECK OPTION works. I though that it prevents row from dissappearing from the view implementing this option but I found that I was able to DELETE all rows through the view - Why is this???
Please note that I only have the base version of sql server 2000 - No patches applied
Thanks for your help
------------------------------------------- Test
Create table T100 (A int) GO Create view VT100 AS (SELECT * FROM T100 WHERE A < 2) with check option GO INSERT INTO T100 VALUES (1) -- Part of the view's result INSERT INTO T100 VALUES (2) GO SELECT * FROM VT100 -- SHOWS 1 GO INSERT INTO VT100 VALUES (-2) -- Works GO UPDATE VT100 SET A=5 -- Update fails because of WITH CHECK OPTION - GOOD GO INSERT INTO VT100 VALUES (999) -- Inser fails because of WITH CHECK OPTION - GOOD GO DELETE FROM VT100 -- DELETES ALL ROWS!!!! ******************* ???
I've been having problems with my tempdb filling up, and causing all databases on the server to stop functioning properly. I've been removing alot of data lately (millions of rows), and I think this is the reason why my tempdb log is going thru an unusual load.
Whats the best way to make sure the tempdb doesnt fill up causing me major problems? I had temporarily turned off backups while I was having a new HD put in. Am I right in thinking that when a DB is backed up, the tempdb log is reduced in size? Should maintaining a daily backup solution help keep things under control ?
Please help.I have a table with single row. I need to allow only UPDATEs of thetable, forbid INSERTs and DELETEs. How to achieve it?Thank you for information/RAM/
Just wondering if this is good form:Alter Procedure "mySPName"@UniqueID intASset nocount onset xact_abort offDELETE FROM tblNameOneWHERE(tblNameOne.UniqueID = @UniqueID)DELETE FROM tblNameTwoWHERE(tblNameTwo.UniqueID = @UniqueID)Is it a good idea to run multiple detele statements within one SP?thanks,lq
I believe I can the effectively then remove the delete stored procedure from the replicated database.
And all this wroks no problem. However, when I come to add a new article, "Article2" to my publication it also snapshots and reinitialised the "Article1". How can I prevent the snapshot agent from re-initialising "Article1".
I need to create a table that only allows records to be inserted or deleted. Once the record has been created it can only be deleted. Is there anyway to configure a table in this manner?
Table Definition
USE [DB_AUTOMATED_PACKAGING_SYSTEM] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[TBL_PCL_LENS_DATA]( [SerialNumber] [varchar](50) NOT NULL, [ProcessedDate] [datetime] NOT NULL, [Filename] [varchar](50) NOT NULL, [CartonLabelImage] [image] NOT NULL, [ExpirationDateLabelImage] [image] NOT NULL, [LabelSetLabelImage] [image] NOT NULL, [ReplyCardLabelImage] [image] NOT NULL, [TextFile] [ntext] NOT NULL, CONSTRAINT [PK_TBL_PCL_LENS_DATA] PRIMARY KEY CLUSTERED ( [SerialNumber] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Using Merge replication between SQL Server 2000 and SQL Server CE, is there any way that row deletes could occur on the subscriber without a reinitialize or explicit delete of row on publisher.
More specifically, if there is a row filter that returns a days worth of data with each days pull, for example, and the filter looked like select <columns> from Table where UpdateDate < GETDATE() and UpdateDate >= DATEADD(d,1,GETDATE()) would there be some implicit delete at subscriber each day because data sent changed?
My research indicates this does not happen, but I have a colleague who thinks differently.
I thought this may be very helpful for those of you using SQL7 linked server to Oracle 7.3.4 Database. After much research and no answers when opening a case with Microsoft I finally figured out how to delete using Openquery. There are a few missing pieces of information that would of been a great help in BOL. The first is, the Microsoft OLE DB for Oracle is not the correct choice for the data provier as one might think. The correct choice is the Oracle Provider for OLE DB. The next important thing is that the Oracle table you are querying MUST have a unique index on at least one column not necessarily the column in your WHERE clause. Thirdly, you get much better performance and use of indexes if you put the WHERE clause inside the OPENQUERY statement. Here is the syntax that I found to work in my application:
SQL 2K - SP3a - Development BoxThe environment is a piece of garbage ... triggers firing triggers firing triggers. Application is a piece of garbage that a developer want to make better (yea!) I need to put together a profile template to capture inserts and deletes into two tables. Not asking for the fish (but will accept it if necessary). Looking thru BOL, cannot find find an event class to capture insert and / or delete. Looking at TSQL event class gives SQLStmtCompleted event, and maybe I can filter on the two object IDs (object name not available because dbname not available as data column for this event.) Or if exists, but not documented, the event class for insert and delete. Any ideas?
I am trying to create a stored procedure that Deletes Table if there is Data.
Also stored procedure will Insert new data into table.
I have already created table. This is part of my current stored procedure.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'srd.[PNMACjmerlos].FHLMC_Trials') AND type in (N'U')) DELETE srd.[PNMACjmerlos].FHLMC_Trials
