RECREATE TABLE TABLE1
(
ID BIGINT NOT NULL,
< various data columns >
SUBMITTED DATE,
CONSTRAINT PK_RPL_PO PRIMARY KEY (ID)
);

RECREATE TABLE TABLE2
(
PARENT BIGINT NOT NULL;
)

ALTER TABLE TABLE2 ADD CONSTRAINT FK_TABLE2_TABLE1
FOREIGN KEY (PARENT) REFERENCES TABLE1 (ID)
ON DELETE CASCADE
ON UPDATE CASCADE

CREATE TRIGGER TABLE1_NODEL FOR TABLE1 ACTIVE BEFORE DELETE POSITION 0
AS
BEGIN
IF ( OLD.SUBMITTED IS NOT NULL ) THEN
BEGIN
EXCEPTION E_NOT_DELETEABLE;
END
END

Transaction one:
Set SUBMITTED to CURRENT_DATE.
Reads from Table2, sending data to an external source.
If submission fails, rollback; otherwise commit.

Hypothetical Transaction two:
Delete item from Table1

What would happen? Will the item and its children be deleted from the
table after it commits? TX 2 can't see that SUBMITTED was set, so the
trigger won't fire.

Rick DeBay