--- In firebird-support@yahoogroups.com, "yaedos2000"
<yaedos2000@y...> wrote:

>
> Hi,
>
> I've got two tables, TBL_MARKER and TBL_MTRNS, both of which contain
> a field MARKID.
>
> I have a procedure which deletes a row from TBL_MARKER (DELETE FROM
> TBL_MARKER WHERE MARKID = SOME_VALUE). But this row can only be
> deleted if MARKID from TBL_MARKER doesn't exist in TBL_MTRNS. If it
> does then the delete cannot take place.
>
> I'm developing a Before Delete trigger to test for this and to stop
> the delete occuring if MARKID exists in the other table.
>
> How would I check each row of TBL_MTRNS for a match? Could it be done
> using a SELECT statement, ie. SELECT MARKID FROM TBL_MTRNS? And how
> would I stop the delete from occur if there is a match?
>
> CREATE TRIGGER BD_MARKER FOR TBL_MARKER
> ACTIVE BEFORE DELETE POSITION 0
> AS
> BEGIN
> END ^
>
> Thanks

There are heaps of ways of doing this. This way uses the trigger and
an exception. Obviously adjust as necessary, and give a better
exception message. You will also want to remember that deleting
multiple records will all fail if any of the records cause an
exception, so you will want to send something a bit more intelligent
than a "delete from tbl_marker".

Create Exception e_TBL_MARKER_NoDelete "Cant do that";

CREATE TRIGGER BD_MARKER FOR TBL_MARKER
ACTIVE BEFORE DELETE POSITION 0
AS
DECLARE VARIABLE VMARKID INTEGER;
BEGIN
-- Find first record where the MARKID matches

SELECT FIRST 1 MARKID
FROM TBL_MTRNS
WHERE MARKID = OLD.MARKID
INTO :VMARKID;

IF (:VMARKID IS NOT NULL) THEN
BEGIN
-- A match was found, create exception
-- and roll back

EXCEPTION e_TBL_MARKER_NoDelete;
END
END
^

Adam