ON DELETE CASCADE

[Steve Whatmore]

Good afternoon,

I am seeing some conflicting information when I search for the ON DELETE
CASCADE statement on the Internet.

Does ASE 15.x support the ON DELETE CASCADE function or is this same
functionality implemented via a trigger?

Thanks in advance.

Whatty

[datvo]

Hi,

I recall there is a feature request to have this function
but don't know if it is in ASE 15.x yet (Change Request
289627)

Also there is a similar request from ISUG which you can vote
on to increase it priority. Enhancement ID 2235:

http://my.isug.com/index.php?mo=is&op=vi&type=1&iid=2235

Cheers,

Dat Vo
[TeamSybase Intern]

[Bret Halford]

ASE 15.x does not have an ON DELETE CASCADE ddl feature, the
functionality can be implemented using a delete trigger. One also has
to implement foreign key constraints using triggers if you are going to
implement cascading deletes.

The conflicting information may be due to the fact that the ASA product
does have the feature.

Cheers,
-bret

[Steve Whatmore]

TY

"Bret Halford" <br...@sybase.com> wrote in message
news:4787B845...@sybase.com...

[Steve Whatmore]

Okay,

I have defined a DELETE trigger on a table which automatically removes the
dependent records in the associated table.

It compiles fine and I can see it defined in the DB but it does not seem to
be getting fired, cause when I delete the parent record the dependent
records are not being removed and thus I get a RI constraint violation.

Is there some configuration option or anything else that I have to do too
enable triggers, or what would be causing this not too fire.

Thanks in advance.

Whatty

"Steve Whatmore" <steven....@lynxdev.com> wrote in message
news:4787a5f9@forums-1-dub...

[A Cornell]

You get RI constraint violation becaues if you define the RI constraints
you must delete the child row first.

"Steve Whatmore" <steven....@lynxdev.com> wrote in message

news:4787d0f9$1@forums-1-dub...

[Steve Whatmore]

That doesn't make sense, the trigger is supposed to maintiain the RI for me
and thus the reason I am using the trigger in the first place

I followed the example from the Sybase manuals (see below) where I am trying
to delete a row from my primary table and the trigger should delete the rows
in the dependent table thus avoiding the RI constraint violation.

Whatty
Allow Deletes, Cascading Delete
This example allows deletes in the primary table (titles) and cascades
deletes to the dependent table (roysched). The full trigger is as follows:

create trigger cascade_dtrig
on titles
for delete as
begin
/* Cascade deletes to dependants in the dependant table */
delete roysched from roysched, deleted where
roysched.title_id = deleted.title_id
end
You can test this trigger with the following delete statement:

delete titles
where title_id = "MC2222"
Afterwards, the title_id MC2222 should have been deleted from roysched as
well as titles.

Restrict Deletes, with Error Message
This example prevents deletes if the primary table (titles) has dependent
children (in titleauthor). It assumes the following error message has been
added to sysusermessages:

sp_addmessage 35001, "restrict_dtrig - delete failed:
row exists in titleauthor for this title_id."
The full trigger is as follows:

create trigger restrict_dtrig
on titles
for delete as
if exists (select * from titleauthor, deleted where
titleauthor.title_id = deleted.title_id)
begin
rollback transaction
raiserror 35001
return
end
To test this trigger, try the following delete statement:

delete titles
where title_id = "PS2091"
"A Cornell" <agcorne...@msn.com> wrote in message
news:4787d564$1@forums-1-dub...

[Mark A. Parsons]

Please post the DELETE command and the entire text of the error(s) you're receiving.

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

In the meantime ...

Sybase triggers fire *after* the DML operation on the base table has been performed.

So, let's assume you have 2 tables:

parent_table w/ PK RI defined
child_table w/ FK RI defined

And we'll assume you've got a trigger created for delete on parent_table; said trigger's job is to delete associated
records from child_table.

Now perform a delete on parent_table:

1 - delete parent_table ...

2 - row is deleted from parent_table

3a - if related record still exists in child_table the RI constraint will kick in and disallow the delete (ie, delete is
rolled back); the delete trigger on parent_table will *NOT* fire since no rows have been affected

3b - if there are no related records in the child_table the RI constraint will not be applied; the delete trigger on
parent_table will fire ('course, there aren't be any records to delete from child_table)

So, how to implement cascading DELETEs via triggers?

a - FK constraints have to be implemented via triggers on both parent_table and child_table (Bret already stated this)

and

b - no FK RI constraints can exist on child_table

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

A couple other approaches you could take:

1 - Write a stored proc which performs the cascading DELETE for you.

Within the stored proc include logic which deletes any associated records from child_table *first*, followed by a
deletion of the desired record from parent_table.

This allows you to maintain your FK RI constraint while also implementing a cascade DELETE capability.

Better yet, write a stored proc to handle the delete from child_table and call this from the proc which deletes from
parent_table. 'course, the child_table stored proc could also call another stored proc to delete from grand_child_table.

[I'd recommend you wrap the deletes in some "begin/commit/rollback tran" logic to make sure you don't lose your
child_table records if there's a failure with the delete from parent_table.]

2 - If you're running with ASE 15.0.2 you could try implementing 'instead of' triggers, though this would basically
require the same type of coding as in option #1 *plus* the creation of a view on top of parent_table.

[Mark A. Parsons]

minor corrections ...

Step #2 - remove

Step #3a - if the RI constraint disallows the delete then *technically* there's nothing to rollback

Mark A. Parsons wrote:
> Please post the DELETE command and the entire text of the error(s)
> you're receiving.
>
> -------------------
>
> In the meantime ...
>
> Sybase triggers fire *after* the DML operation on the base table has
> been performed.
>
> So, let's assume you have 2 tables:
>
> parent_table w/ PK RI defined
> child_table w/ FK RI defined
>
> And we'll assume you've got a trigger created for delete on
> parent_table; said trigger's job is to delete associated records from
> child_table.
>
> Now perform a delete on parent_table:
>
> 1 - delete parent_table ...
>

> 2 - ... deleted ...

>
> 3a - if related record still exists in child_table the RI constraint

> will kick in and disallow the delete; the delete trigger on parent_table

> will *NOT* fire since no rows have been affected
>
> 3b - if there are no related records in the child_table the RI
> constraint will not be applied; the delete trigger on parent_table will

> fire ('course, there aren't any records to delete from child_table)

[Steve Whatmore]

Thanks for the assistance, where I was falling down was the fact that in
examples in Sybase documentation it is not immediately clear that there was
no RI defined on the example tables and that RI was being maintained by
triggers, thus their trigger worked.

I had RI already defined on my tables and as explained the delete trigger
fires after the delete occurs which caused the RI integrity constraint
violation first.

IMHO, I am a little surprised that I have to jump through hoops to replicate
the ON DELETE CASCADE functionality which is supported my almost all of the
other major DB vendors. Not to start a religious flame war but when I say
jump through hoops, I consider a one line statement (ON DELETE CASCADE)
versus several stored procedures and extra calls to be jumping thru hoops.

Whatty

"Mark A. Parsons" <iron_horse@no_spamola.compuserve.com> wrote in message
news:4788dd50$1@forums-1-dub...

[Patsy Rossiter]

Steve,

Hi, this functionality is still processed via a trigger in Sybase.

"Steve Whatmore" <steven....@lynxdev.com> wrote in message
news:4787a5f9@forums-1-dub...

[vijaym...@gmail.com]

Hi, Can someone please let me know, how it got resolved finally?