Cascading Deletes

I'm trying to implement cascading deletes in Sybase via a trigger. I know I
can't just specify this when I create the foreign key constraint. So, my
question is this, which will fire first, the RI or the trigger? If it's the
former, then I won't be able to delete any children because the parent will
exist. Is there a clean workaround if this is the case?

Thanks!

Arthur
---== Posted via the PFCGuide Web Newsreader ==---
http://www.pfcguide.com/_newsgroups/group_list.asp
0
Arthur
10/23/2000 6:34:24 PM
sybase.sqlserver.general 4269 articles. 0 followers. Follow

4 Replies
481 Views

Similar Articles

[PageSpeed] 3

Don't use the constraints.  Use only the triggers for your RI.

Bob Densmore

Arthur Langham <alangham@yahoo.com> wrote in message
news:uxaLe$RPAHA.294@forums.sybase.com...
> I'm trying to implement cascading deletes in Sybase via a trigger. I know
I
> can't just specify this when I create the foreign key constraint. So, my
> question is this, which will fire first, the RI or the trigger? If it's
the
> former, then I won't be able to delete any children because the parent
will
> exist. Is there a clean workaround if this is the case?
>
> Thanks!
>
> Arthur
> ---== Posted via the PFCGuide Web Newsreader ==---
> http://www.pfcguide.com/_newsgroups/group_list.asp


0
Bob
10/23/2000 6:23:13 PM
Bob,

Don't use the constraints in general, or just to solve the cascading deletes
problem?

If it's just to solve the cascading deletes problem, then we end up with some
tables that have constraints, and some that have triggers.

If it's in general, then we have to drop all our existing constraints, and
reimplement RI with triggers.

Arthur

On Mon, 23 Oct 2000 13:23:13 -0500,
 in sybase.public.sqlserver.general
Bob Densmore <bob.densmore@dhs.state.tx.us> wrote: 
>Don't use the constraints.  Use only the triggers for your RI.
>
>Bob Densmore
>
>Arthur Langham <alangham@yahoo.com> wrote in message
>news:uxaLe$RPAHA.294@forums.sybase.com...
>> I'm trying to implement cascading deletes in Sybase via a trigger. I know
>I
>> can't just specify this when I create the foreign key constraint. So, my
>> question is this, which will fire first, the RI or the trigger? If it's
>the
>> former, then I won't be able to delete any children because the parent
>will
>> exist. Is there a clean workaround if this is the case?
>>
>> Thanks!
>>
>> Arthur
>> ---== Posted via the PFCGuide Web Newsreader ==---
>> http://www.pfcguide.com/_newsgroups/group_list.asp
>
>

---== Posted via the PFCGuide Web Newsreader ==---
http://www.pfcguide.com/_newsgroups/group_list.asp
0
Arthur
10/23/2000 6:45:15 PM
Arthur,

To answer your original question which I did not do, the RI constraint will
fire first.

I generally prefer to not use RI constraints, but use triggers.  I see you
are already down the road and cannot go back.

If you stick with RI constraints, you will have to delete from the child up
or reconsider your delete strategy to maybe a logical delete strategy.

One of the problems you run into with RI constraints, is if you are doing
alot of deleting in your system, you have to delete from the child up.  This
typically leads to deadlocks.  You have inserts going in from the parent
down and deletes happening from the child up.  This is a recipe for
deadlocks.

Bob Densmore

Arthur Langham <alangham@yahoo.com> wrote in message
news:ByZ$hFSPAHA.288@forums.sybase.com...
> Bob,
>
> Don't use the constraints in general, or just to solve the cascading
deletes
> problem?
>
> If it's just to solve the cascading deletes problem, then we end up with
some
> tables that have constraints, and some that have triggers.
>
> If it's in general, then we have to drop all our existing constraints, and
> reimplement RI with triggers.
>
> Arthur
>
> On Mon, 23 Oct 2000 13:23:13 -0500,
>  in sybase.public.sqlserver.general
> Bob Densmore <bob.densmore@dhs.state.tx.us> wrote:
> >Don't use the constraints.  Use only the triggers for your RI.
> >
> >Bob Densmore
> >
> >Arthur Langham <alangham@yahoo.com> wrote in message
> >news:uxaLe$RPAHA.294@forums.sybase.com...
> >> I'm trying to implement cascading deletes in Sybase via a trigger. I
know
> >I
> >> can't just specify this when I create the foreign key constraint. So,
my
> >> question is this, which will fire first, the RI or the trigger? If it's
> >the
> >> former, then I won't be able to delete any children because the parent
> >will
> >> exist. Is there a clean workaround if this is the case?
> >>
> >> Thanks!
> >>
> >> Arthur
> >> ---== Posted via the PFCGuide Web Newsreader ==---
> >> http://www.pfcguide.com/_newsgroups/group_list.asp
> >
> >
>
> ---== Posted via the PFCGuide Web Newsreader ==---
> http://www.pfcguide.com/_newsgroups/group_list.asp


0
Bob
10/23/2000 6:54:12 PM
I don't have any real experience with this angle but would think that one
way to eliminate the up/down deadlocks would be to have a dummy table (with
holdlock) that is always updated at the beginning of either kind of
transaction.

(I do prefer RI but strangely enough have had no deadlock problems since
we're currently designing with SQL-Remote and doing deletes/inserts with a
batch polling process.)


Bob Densmore <bob.densmore@dhs.state.tx.us> wrote in message
news:Cr9c1SSPAHA.202@forums.sybase.com...
> Arthur,
>
> To answer your original question which I did not do, the RI constraint
will
> fire first.
>
> I generally prefer to not use RI constraints, but use triggers.  I see you
> are already down the road and cannot go back.
>
> If you stick with RI constraints, you will have to delete from the child
up
> or reconsider your delete strategy to maybe a logical delete strategy.
>
> One of the problems you run into with RI constraints, is if you are doing
> alot of deleting in your system, you have to delete from the child up.
This
> typically leads to deadlocks.  You have inserts going in from the parent
> down and deletes happening from the child up.  This is a recipe for
> deadlocks.
>
> Bob Densmore
>



0
Carl
10/24/2000 2:47:00 PM
Reply:

Similar Artilces:

Cascading deletes - Which is better
I need to implement my cascading deletes on a SQL database.  Is it better (performance/reliablility-wise) to use the Foreign Key Cascading Deletes or to just write my own triggers to do the deletes?I was hoping someone had experimented and found which works best. DRI(declarative referential integrity) triggers are used when Cascade Deletes and Updates cannot be implemented in the RDBMS(relational database management system) as in SQL Server 7.0 and below.  In SQL Server 2000 using Cascade Delete and Cascade Update is the way to go.  The reason is reliability related because ...

Is Cascading deletes prefered to deleting thru a stored procedure?
I have a situation in which I have three tables - 'Table1', 'Table2' and 'Table3'. 'Table1' is a primary table for 'Table2' so that the the primary key of 'Table1' is a foreign key in 'Table2'. In the same way, 'Table1' is also the primary table for 'Table3'. If I delete a row in 'Table1' then I could automatically let SQL Server also delete the related rows in 'Table2' and 'Table3' through 'cascading delete' option. Or I could run a trigger or stored procedure for deleting related rows in 'Table2' and 'Table3'. Which of these methods would be quicker? Also would cascading deletes option a...

CASCADE ON DELETE?
I didn't create the database I'm now working with, in SQLAnywhere 5.0. I want to delete a large number of records and have it do a CASCADE on DELETE. How do I check if this particular referential integrity constraint exists for this table? On Mon, 04 Jan 1999 15:34:05 -0500, "Rick Charnes" <rick.charnes@state.ma.us> wrote: >I didn't create the database I'm now working with, in SQLAnywhere 5.0. I >want to delete a large number of records and have it do a CASCADE on >DELETE. How do I check if this particular referential integrity constrai...

delete cascade
I'm trying to add delete cascade to an already-existing table via ISQL. I'm getting a syntax error: alter table carrier on delete cascade; Error = syntax error near 'on' Should I be able to do this? I'm running on Windows 2000 Pro, SQLAnywhere 5.5.03, using DBENG50.EXE. It should be something like: alter table carrier drop foreign key fk1 alter table carrier add foreign key fk1 references ... etc ... on delete cascade Then when the row in the fk1 table is deleted the row in the "carrier" table will be deleted also. -- Steve http://www...

on delete cascade?
This is a multi-part message in MIME format. --------------DB2070C890FF2F3A31CE38D9 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hi, I'm new to Sybase SQL Server 11 and I'm wondering whether there is any possibility to perform a cascading delete in a master-detail-relationship using a foreign key constraint? Any ideas? Thanks Thomas --------------DB2070C890FF2F3A31CE38D9 Content-Type: text/x-vcard; charset=us-ascii; name="vcard.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for Thomas Golob Content-Dispositi...

cascade delete
Does anyone know how to implement cascade delete within ASE on tables with foreign keys? If so please help me! I know that ASA has this capability but I can not find anything in ASE to support it. Here is my email if you have an answer pitsnogle@va.prestige.net thanks, Richard ASE does not support declarative cascading deletes through referential integrity. If you want to do cascading deletes, you will have to write triggers to handle your foreign keys, rather than using the constraints. -bret Rich and Peg wrote: > Does anyone know how to implement cascade dele...

CASCADE ON DELETE
Hi, Just a quick question: will ASE 12.5 support CASCADE ON DELETE on table creation? Take care, Yicheng ...

Cascading delete
In my consolidated database I have a foreign key between two tables with the cascading delete integrity constraint applied. The two tables are replicated (in their entirety) to a remote database. In the remote database should I have the delete integrity constraint applied in the foreign key between the two tables? If a record is deleted from table A in the consolidated database and this cascades and deletes a record in table B, won't the individual delete statements from the transaction log be applied to the remote database thus removing the need for the "cascade" to ...

Delete on cascade
Hello, I have a problem with the speed of a delete on a table that use cascades delete I use ASA 7.0.2.1493 I have a table (tb_a). It has 100 records. This table is referenced by 16 other tables. (tb_b) Some of these tables are referenced by other tables.(tb_c) Some of these referenced tables are referenced by other tables. (tb_d) Sometimes, tables are referencing other tables directly ex : tb_a is referenced by tb_d When I make a delete on tb_a, it takes 30 sec. to make the delete. The delete is indexed as I delete on the primary key from tb_a. For tb_b, tb_c, tb_c, There ...

Delete cascade
Hello, I'm trying to use cascade delete on Sybase, but it doesn't work like SQL Server 2000. Does anyone know how to do it (I can't use a stored procedure, only triggers)? I've got a table that has referencied rows on another tables, and I want to delete these records when I delete the row on the parent table. This is the parent table CREATE TABLE dbo.tbl_Protocolo_x_Documento ( Cn_Protocolo int, Cn_Documento int, Ds_Motivo varchar(255), CONSTRAINT PK_tbl_Protocolo_x_Documento PRIMARY KEY(Cn_Protocolo,Cn_Documento) ) GO...

Cascade Delete
PowerDesigner Version 9.0.0.438 and MS SQL 7.0 SP3. I need to be able to allow for Cascading deletes and updates. I see two problems at this point: One is the current code in the DBMS properties for Relationship Create is as follows: [%USE_SP_FKEY%?[execute ]sp_foreignkey %TABLE%, %PARENT%, %FKEYCOLUMNS% :alter table [%QUALIFIER%]%TABLE% add [constraint %CONSTNAME%] foreign key (%FKEYCOLUMNS%) references [%PQUALIFIER%]%PARENT%[ (%CKEYCOLUMNS%)]] In order for it to place the correct code I believe I need to have the following: [%USE_SP_FKEY%?[execute] sp_foreig...

On Delete Cascade
Hi all, I'm working on the porting of an application from Oracle to Sybase (ASE 11.9.2). Now I'm facing with the "on delete cascade" and "cascade constraints" directives present in Oracle. Is there anything similar in Sybase or is there any workaround? Thanks to anybody can help me. Gianpaolo. -- ------------------------------------------------------------------ Gianpaolo Araco phone +39-06-47823776 - ext: 17 Web Bridges Srl fax +39-06-48906270 Via Mecenate 59 mailto:gianpaolo@web...

CASCADE deletes
In SQL Central, how do I set sqlanywhere to enforce referential integrety and delete all foreign keys when a record is deleted? Thanks for any help, Randy Nelson >In SQL Central, how do I set sqlanywhere to enforce referential integrety >and delete all foreign keys when a record is deleted? To create a foreign key you can click on the "Add foreign Key" line to start a fkey creation wizard. To change the properties of an existing foreign key definition, Right click on the foreign key and choose Properties. Click the 'Integrity' tab. Under "de...

Web resources about - Cascading Deletes - sybase.sqlserver.general

Cascading Style Sheets - Wikipedia, the free encyclopedia
Cascading Style Sheets ( CSS ) is a style sheet language used for describing the presentation semantics (the look and formatting) of a document ...

Ads API Profile: Alchemy’s Cascading Ad Creation, Analytics, and Auto-Optimization
Facebook’s performance advertising system provides buyers with a wealth of targeting options, but not the depth of control or analytics to effectively ...

Twitter blames global outage on 'cascading bug'
AFTER nearly a year without any significant periods of downtime, using Twitter was almost nothing like the early days of being on the service. ...

In defence of Gen Y, and cascading generations, by a boomer
Some accuse Generation Y of feeling entitled, overly precocious, demanding and rude but that doesn't necessarily reflect reality.

In defence of Gen Y, and cascading generations, by a boomer
Some accuse Generation Y of feeling entitled, overly precocious, demanding and rude but that doesn't necessarily reflect reality.

Cascading Style Sheets - 9to5Mac
Adobe announced updates on Thursday to many of its HTML5 design tools for Creative Cloud users. Among the updates, we get new features for Edge ...

West Texas Cascading Safety Failure
In the interests of Calamity Fairness, here’s a most depressing Bloomberg article : Texas Explosion Seen as Sign of Weak U.S. Oversight The ...

Watch this captivating video of 11,000 marbles cascading down a marble run
Everyone has a hobby. Some people like to play recreational sports, others prefer to go fishing, but did you know there are creative types out ...

Austria Announces Fence With Slovenia; Irony of German Whine; Cascading Fences; Mish Proposed Strategy ...
Two months ago, Austria's chancellor Werner Faymann criticized Hungary for building a fence along its border. Faymann proclaimed "To think that ...

Cascading Slopes is Jacob from The Drums' new band, NYC residency this month
Cascading Slopes at Cameo, 1/31/2013 (via @FreshBread) Cascading Slopes is a new project from Jacob Graham of The Drums. Unlike his other band, ...

Resources last updated: 1/21/2016 2:45:46 AM