*** 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:

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)

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.

*** 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 ***

 

IBM Connections 4.0: Follow a community by default
Tagged on:                     

10 thoughts on “IBM Connections 4.0: Follow a community by default

  • 05.06.2013 at 20:33
    Permalink

    Would you know how to add everyone to a community automatically?

    Reply
  • 05.06.2013 at 20:49
    Permalink

    I am quite sure that you can do that also with a SQL script. I would suggest to get in contact with IBM Software Services to help you here.

    Reply
    • 05.06.2013 at 20:53
      Permalink

      Anyway, thanks Michael.

      Regards

      Reply
  • 05.06.2013 at 21:12
    Permalink

    Again to make sure: Manipulating the data structure on the database level is DANGEROUS as the structure is not documented and may change at any time with no warranty. Use this really at your own risk and do not blame IBM for any data corruption you will get if you do this! That was the reason why this entry was private before (and maybe it would have been better not to set it to public ;-))

    Reply
  • 07.02.2014 at 11:56
    Permalink

    Saved my day - thank you very much for sharing this!

    Reply
    • 07.02.2014 at 11:57
      Permalink

      I just use the following Statement to add single followers:
      INSERT INTO SNCOMM.FOLLOWING (COMMUNITY_UUID, MEMBER_UUID, CREATED_BY, CREATED)
      VALUES ('2b6038ff-a130-481a-b26a-456240c04a8a', '6bc243a0-4abc-4083-b584-a9b50a62f47f', '6bc243a0-4abc-4083-b584-a9b50a62f47f', '2014-02-07 11:00:20.865')

      Reply
  • 15.03.2016 at 16:01
    Permalink

    Hi, here is an update for Connections 5.0 and Oracle dialect.
    CREATE OR REPLACE TRIGGER AUTO_FOLLOW_COMMUNITY
    AFTER INSERT ON SNCOMM.MEMBER
    REFERENCING NEW AS NEW_MEMBER
    FOR EACH ROW
    DECLARE
    mem_cnt integer;
    BEGIN
    SELECT COUNT(*) into mem_cnt FROM SNCOMM.FOLLOWING WHERE :NEW_MEMBER.MEMBER_UUID = SNCOMM.FOLLOWING.MEMBER_UUID AND :NEW_MEMBER.COMMUNITY_UUID = SNCOMM.FOLLOWING.COMMUNITY_UUID;
    IF ( :NEW_MEMBER.ROLE = 0 AND (mem_cnt=0 )) then
    INSERT INTO SNCOMM.FOLLOWING (COMMUNITY_UUID, MEMBER_UUID, ORG_ID, CREATED_BY, CREATED)
    VALUES (:NEW_MEMBER.COMMUNITY_UUID, :NEW_MEMBER.MEMBER_UUID, 'a', :NEW_MEMBER.MEMBER_UUID, :NEW_MEMBER.CREATED);
    END IF;
    END;

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *