--- In firebird-support@yahoogroups.com, "petesouthwest"
<petesouthwest@h...> wrote:

> Hi
>
> I have a contacts database for the educational resources that we are
> developing, with the following tables:
>
> Organisation -< Contact -<Contact_Exam >-Exam
>
> When we add a new contact we will need to add a record in the
> contact_exam table linking the contact with the type of exam they are
> responsible for.
>
> I was wondering if this record could be automatically generated by the
> database somehow or whether it should/can only be/ generated by my
> frontend software.
>
> If it can be automatically generated by the database, could you point
> me in the right direction please!
>
> Thanks
> Pete

This is one of the uses for a stored procedure. Adjust as required
depending on the information you would like to record.

CREATE PROCEDURE SP_NEW_CONTACT
(
ORGANISATIONID INTEGER,
CONTACTNAME VARCHAR(50),
EXAMID INTEGER
)
RETURNING
(
CONTACTID INTEGER
)
AS
BEGIN
CONTACTID = GEN_ID(GEN_CONTACTID,1);

INSERT INTO CONTACT (ID, ORGANISATIONID, CONTACTNAME)
VALUES (:CONTACTID, :ORGANISATIONID, :CONTACTNAME);

INSERT INTO CONTACT_EXAM (CONTACTID, EXAMID)
VALUES (:CONTACTID, :EXAMID);

SUSPEND;
END
^

Note, suspend is only required if you call this in the form

select contactid
from sp_new_contact(5, 'Adam', 6);

Stored procedures in Firebird execute as an atomic operation. They
either entirely suceed or they are rolled back (internally a savepoint
is created at the start of the procedure and if there is an exception
that you do not handle within the stored procedure, then it is rolled
back to that point, it does not rollback the entire transaction, that
is up to the client program to decide what it wants to do. The client
program can rollback the transaction, or it might swallow the
exception and carry on doing something else.)

Back in the real world, what that means is that if you use an ExamID
that does not exist in the Exam table (I am assuming you have a FK
defined), then a foreign key violation would be raised within the SP.
Because this is not caught inside the SP, it will be passed back to
the application, and the insert into contact will be undone.

Adam