*** 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”]
|
1 2 3 4 5 6 7 |
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.
*** 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 ***
Very cool hack, thx for sharing!
Would you know how to add everyone to a community automatically?
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.
Anyway, thanks Michael.
Regards
This offers many other possibilities… thx.
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 ;-))
Saved my day – thank you very much for sharing this!
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’)
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;
Thanks a lot, Anatoly!