IBM Connections 4.0: Follow a community by default

*** MANIPULATING DATA DIRECTLY WITHIN THE DATABASE MAY LEAD TO DATA LOSS AND DATA CORRUPTION. THIS IS NOT SOMETHING WHICH IS OFFICIALLY SUPPRTED SO USE IT ON YOUR OWN RISK AND DO NOT BLAME IBM FOR ANY DAMAGE ***

We just had the customer request that every user who will be added to a community should automatically follow the community as well. As this is by default only done for users who created the community or are added as owner to a community we needed to find a solution for normal users as well.

If a user follows a community there is a row in the table SNCOMM.FOLLOWING which contains the COMMUNITY_UUID (from SNCOMM.COMMUNITY) for the commnity beeing followed and the MEMBER_UUID (from SNCOMM.MEMBER).

So you can define a database trigger which automatically adds a row to SNCOMM.FOLLOWING as soon as a new member row will be created in SNCOMM.MEMBER. The SQL code for this trigger will be:

[codesyntax lang=”sql”]

CREATE TRIGGER SNCOMM.auto_follow_community
AFTER INSERT ON SNCOMM.MEMBER
REFERENCING NEW AS NEW_MEMBER
FOR EACH ROW 
WHEN ( ROLE = 0 AND ( NOT EXISTS (SELECT * FROM SNCOMM.FOLLOWING WHERE NEW_MEMBER.MEMBER_UUID = SNCOMM.FOLLOWING.MEMBER_UUID AND NEW_MEMBER.COMMUNITY_UUID = SNCOMM.FOLLOWING.COMMUNITY_UUID)))
INSERT INTO SNCOMM.FOLLOWING (COMMUNITY_UUID, MEMBER_UUID, CREATED_BY, CREATED) 
VALUES (NEW_MEMBER.COMMUNITY_UUID, NEW_MEMBER.MEMBER_UUID, NEW_MEMBER.MEMBER_UUID, NEW_MEMBER.CREATED)

[/codesyntax]

The trigger fires only for “ROLE=0” which means only normal members and not owners (which would be “ROLE=1”). This is because Connections by itself does already add such a row to the table SNCOMM.FOLLOWING or all owners.

This trigger was tested with DB 2 but should be working as well with the other supported databases.

Caution: Be sure to drop the DB trigger before upgrading or changing Db schemas. You can re-apply it afterwards.