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