VB6,Sql7,delete records using diagrams

[Gil Ben-Yosef]

Hi,

I am using diagrams in Sql 7 to connect some tables one to anoher. (Father table and sons) Like I was doing with access.
When I am trying to delete the Father record
I am getting error.
What is the appropriate syntax to do so?

Thanks,

Gil

[Clunietp]

did you (indirectly or directly) add a constraint to your tables so that a parent record must correspond to each child record?

[Gil Ben-Yosef]

If you mean the constraint that appear in the table folder then no. I don't know what to write there.
Gil

[JHausmann]

You can write a trigger to issue a delete on the child...

[lychew]

this could be because of referential integrity. You are trying to delete a record in the father table which is used by the child. So when you delete, make sure it is a cascade delete.

[Gil Ben-Yosef]

Thank you all.

1. How do I write triggers for this missions?
2. What is a cascade delete?

I tried to look in the sql documentation but I didn't find any. Does anyone know where it is in the documentation?

Gil

[JHausmann]

You'll have to write the triggers to support a cascade delete (SQL Server 6.5 doesn't have cascade deletes, not sure about 7.0 but I suspect it doesn't also).

A cascade delete deletes records in associated tables when you issue a delete on one or more tables. The term comes from hierarchical databases that have parent-child structures. Hierarchical databases, for the most part, lost out to relational databases in the secret database wars.

As an example, a delete trigger might look like:

CREATE TRIGGER CascadeExample
ON table1
FOR DELETE
AS

Delete table2 from deleted a, table2 b
where a.uniquekey=b.uniquekey

[Gil Ben-Yosef]

Thanks,
The Cascade delete works.
But I still have problems with the diagrams.
Meanwhile I disconnect them but I don't think that it is the right solution.
I am still looking for a way to prevent errors while performing delete within connect tables.

Gil

[Clunietp]

If the cascade delete works, what error messages are you getting?

[JHausmann]

You will need to add a trigger for every table that has RI to the one you're deleting.

[Gil Ben-Yosef]

The error I am getting is: DELETE statement conflicted with COLUMN REFERENCE constraint...
I need the diagrams for automatic update between tables as it is in access.
If I have to write triggers then I don't think that I need the diagrams.

Gil