Design: DB2 Triggers to manage relation update between two tables

Given the two tables below where the first one is for industries and 2^nd one to manage the relation between service & industry many-to-many relation (table service is not included below). If there is a column called “IS_SERVICE_AVAILABLE” included in table INDUSTRY which can take one of two values:

• N : when there is no service for this industry at all or in other words there is no record for this specific industry at all in table SERVICE_INDUSTRY_REL
• Y: when there is at least one service for this industry or in other words there is least one record for this specific industry in table SERVICE_INDUSTRY_REL
  
  

  

  Figure 1 - Industry Table

  

  Figure 2 - Service - Industry Relation Table

  
  

Question: how can I use triggers to automatically update the column IS_SERVICE_AVAILABLE to either 'Y' or 'N' automatically with every update performed on table SERVICE_INDUSTRY_REL ?

Answer:

We need to add three triggers for insert, update and delete as shown below:

CREATE TRIGGER SERVICES.INDUSTRY_ON_INSERT AFTER INSERT ON SERVICES.SERVICE_INDUSTRY_REL FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

UPDATE SERVICES.INDUSTRY SET IS_SERVICE_AVAILABLE='N';

UPDATE SERVICES.INDUSTRY SET IS_SERVICE_AVAILABLE='Y' WHERE ID IN (SELECT INDUSTRY_ID FROM SERVICES.SERVICE_INDUSTRY_REL);

END

CREATE TRIGGER SERVICES.INDUSTRY_ON_DELETE AFTER DELETE ON SERVICES.SERVICE_INDUSTRY_REL FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

UPDATE SERVICES.INDUSTRY SET IS_SERVICE_AVAILABLE='N';

UPDATE SERVICES.INDUSTRY SET IS_SERVICE_AVAILABLE='Y' WHERE ID IN (SELECT INDUSTRY_ID FROM SERVICES.SERVICE_INDUSTRY_REL);

END

CREATE TRIGGER SERVICES.INDUSTRY_ON_UPDATE AFTER UPDATE ON SERVICES.SERVICE_INDUSTRY_REL FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

UPDATE SERVICES.INDUSTRY SET IS_SERVICE_AVAILABLE='N';

UPDATE SERVICES.INDUSTRY SET IS_SERVICE_AVAILABLE='Y' WHERE ID IN (SELECT INDUSTRY_ID FROM SERVICES.SERVICE_INDUSTRY_REL);

END