At 10:08 AM 4/05/2004 +0200, you wrote:

>Hi
>
>I have the following tables:
>
>CREATE TABLE "Attachment"
>(
> "AttachmentID" SMALLINT NOT NULL, //Primary key
> "NoteID" INTEGER NOT NULL, //Primary key
> "Attachment" BLOB SUB_TYPE 0 SEGMENT SIZE 80,
> "Description" VARCHAR(50),
> "FileName" VARCHAR(50)
>);
>
>CREATE TABLE "Notes"
>(
> "NoteID" INTEGER NOT NULL, //Primary key
> "Description" VARCHAR(250),
> "NoteDate" TIMESTAMP
>);
>
>I want the children(Attachment) to be deleted when the parent(Notes) are
>deleted.
>First I tried a Foreign key constraint:
>ALTER TABLE "Attachment" ADD CONSTRAINT "FK_Attachment_Notes" FOREIGN KEY
>("NoteID") REFERENCES "Notes" ("NoteID") ON DELETE CASCADE;
>
>And then a trigger:
>CREATE TRIGGER "Notes_BD0" FOR "Notes"
>ACTIVE BEFORE DELETE POSITION 0
>AS
>begin
> delete from "Attachment" where "Attachment"."NoteID" = "Notes"."NoteID";
>end
>
>But whenever I delete Notes the children(Attachment) isn't deleted.
>Can anyone show me the correct way?

Two problems here:

1) When you declare the RI with ON DELETE CASCADE, Firebird writes its own
delete trigger. You'll mess things up if you then go and define your own
that does the same thing (though you can define triggers to *modify* the
behaviour of the automatic RI triggers)

2) The syntax in your trigger (if it worked: thank goodness it didn't!!)
would delete all of the rows in the Attachment table. Correct
delete-trigger syntax to relate a row in a different table to the row being
deleted is OLD.<column-name>

CREATE TRIGGER "Notes_BD0" FOR "Notes"
ACTIVE BEFORE DELETE POSITION 0
AS
begin
delete from "Attachment" where "NoteID" = old."NoteID";
end

But, as I said, this isn't what you want to do here. In any case, when
using triggers to operate on different tables, do the stuff in AFTER
triggers. Use BEFORE triggers to transform the input data. For UPDATE
triggers, refer to new.<column-name> or old.<column-name> as required; for
INSERT, old new.<column-name> is available.

Another MAJORLY MAJOR ADVICE: avoid those quoted identifiers!!!

/heLen