ASE Database forum - Foreign Key

Author

Foreign Key

Rabih

2005-04-02, 7:02 am

Hi All

I'm using a ASE 11.9
I'm creating a foreign key between 2 tables (using Power designer 7 or 8 or
9) with a cascade deletion properties. The problem is that the cascade
deletion is always transformed to a trigger ... is that normal ??

Another issue : I have a "trigger on deletion" on a child table, that will
insert a row in another table called "my_deletion" for each row deleted
When the "cascade delete" is fired on this table, and more than 1 row is
being deleted, only the last row is written in the "my_deletion" table
even if a process a delete statement that will delete more than 1 row, only
the last row is written in the "my_deletion" table
if a delete the rows 1 by 1 than all the rows will be written in the
"my_deletion" table
Anyone knows how can I solve this issue??

Any help will be very appreciated
Regards
Rabih

Bret Halford

2005-04-03, 8:02 pm

Rabih wrote:

> Hi All
>
> I'm using a ASE 11.9
> I'm creating a foreign key between 2 tables (using Power designer 7 or 8 or
> 9) with a cascade deletion properties. The problem is that the cascade
> deletion is always transformed to a trigger ... is that normal ??
>
> Another issue : I have a "trigger on deletion" on a child table, that will
> insert a row in another table called "my_deletion" for each row deleted
> When the "cascade delete" is fired on this table, and more than 1 row is
> being deleted, only the last row is written in the "my_deletion" table
> even if a process a delete statement that will delete more than 1 row, only
> the last row is written in the "my_deletion" table
> if a delete the rows 1 by 1 than all the rows will be written in the
> "my_deletion" table
> Anyone knows how can I solve this issue??
>
> Any help will be very appreciated
> Regards
> Rabih

ASE doesn't yet support declarative RI with cascade delete, so a trigger
generally is the solution to implementing it - so yes, I'd say this was
normal.

The issue with the number of rows is almost certainly due to the way the
trigger is written - I suggest you post the trigger code.

Cheers,
-bret

Rabih

2005-04-04, 7:02 am

Hi Bret,and thx for your reply...

Concerning the first point :
So what is the solution to protect the data integrity, and at the same time,
have a cascade deletion. The problem is that the delete restrict is always
beeing checked before the "cascade deletion", and ASE is preventing me from
deleting the parent ID.

Concerning the second point:
print 'td_programs'
SETUSER 'dbo'
go

create trigger td_programs ON programs for DELETE as
begin
declare
@ls_key varchar(255),
@numrows int,
@errno int,
@errmsg varchar(255)

select @numrows = @@rowcount
if @numrows = 0
return

select @ls_key = rtrim(str(deleted.code)) from deleted

/* Delete all children in "prog_details" */
delete prog_details
from prog_details t2, deleted t1
where t2.programs_code = t1.code

/* Keep track of each record deleted in the table "my_deletion" */
Insert Into my_deletion
(table_name,
deletion_date,
row_key)
Values ("programs" ,
getdate() ,
@ls_key)

return

/* Errors handling */
error:
raiserror @errno @errmsg
rollback transaction
end
go

SETUSER
go

TIA
Rabih

"Bret Halford" <bret@sybase.com> wrote in message
news:4250083C.DCD16D7C@sybase.com...
>
>
> Rabih wrote:
>
or[color=darkred]
will[color=darkred]
only[color=darkred]
>
> ASE doesn't yet support declarative RI with cascade delete, so a trigger
> generally is the solution to implementing it - so yes, I'd say this was
> normal.
>
> The issue with the number of rows is almost certainly due to the way the
> trigger is written - I suggest you post the trigger code.
>
> Cheers,
> -bret
>
>

2005-04-04, 8:05 pm

@ls_key can only hold one value, the data from the last row
returned.
The trigger is fired once for the batch, not for each
deleted row.

Replace:

select @ls_key = rtrim(str(deleted.code)) from deleted

with:

Insert Into my_deletion (table_name, deletion_date, row_key)
select 'programs', getdate(), rtrim(str(deleted.code)) from
deleted

Then remove the other insert statement.

> Hi Bret,and thx for your reply...
>
> Concerning the first point :
> So what is the solution to protect the data integrity, and
> at the same time, have a cascade deletion. The problem is
> that the delete restrict is always beeing checked before
> the "cascade deletion", and ASE is preventing me from
> deleting the parent ID.
>
> Concerning the second point:
> print 'td_programs'
> SETUSER 'dbo'
> go
>
> create trigger td_programs ON programs for DELETE as
> begin
> declare
> @ls_key varchar(255),
> @numrows int,
> @errno int,
> @errmsg varchar(255)
>
> select @numrows = @@rowcount
> if @numrows = 0
> return
>
> select @ls_key = rtrim(str(deleted.code)) from
> deleted
>
> /* Delete all children in "prog_details" */
> delete prog_details
> from prog_details t2, deleted t1
> where t2.programs_code = t1.code
>
> /* Keep track of each record deleted in the table
> "my_deletion" */
> Insert Into my_deletion
> (table_name,
> deletion_date,
> row_key)
> Values ("programs" ,
> getdate() ,
> @ls_key)
>
>
> return
>
> /* Errors handling */
> error:
> raiserror @errno @errmsg
> rollback transaction
> end
> go
>
> SETUSER
> go
>
>
> TIA
> Rabih
>
>
>
>
>
>
>
>
> "Bret Halford" <bret@sybase.com> wrote in message
> news:4250083C.DCD16D7C@sybase.com...
> Power designer 7 or 8 or
> child table, that will
> even if a process a delete statement that will delete more
> than 1 row, only
> post the trigger code. >
>
>

Bret Halford

2005-04-04, 8:05 pm

If you wish to use cascade delete, .the solution is to implement the RI for the
table in a trigger.

-bret

Rabih wrote:
[color=darkred]
> Hi Bret,and thx for your reply...
>
> Concerning the first point :
> So what is the solution to protect the data integrity, and at the same time,
> have a cascade deletion. The problem is that the delete restrict is always
> beeing checked before the "cascade deletion", and ASE is preventing me from
> deleting the parent ID.
>
>
>
> TIA
> Rabih
>
> "Bret Halford" <bret@sybase.com> wrote in message
> news:4250083C.DCD16D7C@sybase.com...
> or
> will
> only

Rabih

2005-04-06, 7:02 am

Is that an option that can have many other advantages, or is it a bug ??
Any solution for this issue in the newer ASE versions ??

TIA
Rabih

"Bret Halford" <bret@sybase.com> wrote in message
news:425168FB.2EC62ABC@sybase.com...
> If you wish to use cascade delete, .the solution is to implement the RI
for the
> table in a trigger.
>
> -bret
>
>
> Rabih wrote:
>
time,[color=darkred]

always[color=darkred
]
from[color=darkred]
or 8[color=darkred]
cascade[color=darkre
d]
that[color=darkred]
deleted[color=darkre
d]
row is[color=darkred]
table[color=darkred]

row,[color=darkred]
trigger[color=darkre
d]
was[color=darkred]
the[color=darkred]
>

Bret Halford

2005-04-06, 8:03 pm

Well, you can do a lot of things with triggers, so I suppose it might
be considered to have other advantages. It isn't a bug, it is just the
absence of a feature - ASE has never directly supported RI with
cascading effects. There is an open feature request for it, CR 289627.
It is not currently planned for 15.0 but may be considered for the future.

-bret

Rabih wrote:
[color=darkred]
> Is that an option that can have many other advantages, or is it a bug ??
> Any solution for this issue in the newer ASE versions ??
>
> TIA
> Rabih
>
> "Bret Halford" <bret@sybase.com> wrote in message
> news:425168FB.2EC62ABC@sybase.com...
> for the
> time,
> always
> from
> or 8
> cascade
> that
> deleted
> row is
> table
> row,
> trigger
> was
> the

Sponsored Links