Cascading Actions

{question}

Is it possible to perform cascading actions with ReferentialIntegrity(I.e. ON Cascade Update, Delete like in Sqlserver) with NuoDB?

{question}

{answer}

This can be done in NuoDB with the following triggers for ON DELETE CASCADE and ON UPDATE CASCADE. This first trigger handles deleting a record in parent table when you also want to delete all children records associated with the record from the parent table.  

Here is an example: 

set delimiter @
CREATE TRIGGER trg_parent_table_FK_delete
FOR schema.parent_tablename BEFORE DELETE 
AS 

IF (OLD.column IS NOT NULL) /* foreign key from parent table */ DELETE FROM schema.child_tablename WHERE childtablecolumnname=OLD.column; END_IF;
END_TRIGGER; @ set delimiter ;
/* This second trigger handles updating record in parent table when you also
want to update all children records associated with the record from the parent table. */
set delimiter @ CREATE TRIGGER trg_parent_table_FK_update FOR schema.parent_tablename BEFORE UPDATE AS
IF (OLD.column <> NEW.column) /* foreign key from parent table */ update schema.child_tablename set childtablecolumnname = NEW.column /*updated column value in the parent table */ WHERE childtablecolumnname = OLD.column /* original column value in the parent table before updating */ END_IF;
END_TRIGGER; @ set delimiter;

{answer}

Have more questions? Submit a request

Comments