#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    5
    Rep Power
    0

    A second trigger


    Relevant tables:

    create table messages
    (
    msgID number(10),
    fromID number(10),
    message varchar2(1024),
    ToUserID number(10) default NULL,
    ToGroupID number(10) default NULL,
    dateSent date,
    constraint messages_pk primary key(msgID),
    constraint messages_fk1 foreign key(fromID) references profile(userID) on delete cascade,
    constraint messages_fk2 foreign key(ToUserID) references profile(userID) on delete cascade
    );

    create table messageRecipient
    (
    msgID number(10),
    userID number(10),
    constraint messageRecipient_pk primary key(msgID, userID),
    constraint messageRecipient_fk1 foreign key(msgID) references messages(msgID) on delete cascade,
    constraint messageRecipient_fk2 foreign key(userID) references profile(userID) on delete cascade
    );

    create table groupMembership
    (
    gID number(10),
    userID number(10),
    constraint groupMembership_pk primary key(gID, userID),
    constraint groupMembership_fk1 foreign key(gID) references groups(gID) on delete cascade,
    constraint groupMembership_fk2 foreign key(userID) references profile(userID) on delete cascade
    );

    I need to create a trigger that adds corresponding records into messageRecipient when a message is sent to a group. So, for example, the sql statement

    insert into messages values(1, 2, 'message sent to group 3 from user 2', NULL, 3, currentdate);

    should add records of the form (1, userID) into messageRecipient for every (3, userID) pair in groupMembership. Here are two triggers I've tried, neither of which works.

    create or replace trigger SendGroupMessage
    after insert on messages
    FOR EACH ROW
    when (new.ToUserID is NULL)
    BEGIN
    insert into messageRecipient(msgID,UserID)
    select messages.msgID as msgID , groupMembership.userID as UserID
    from messages,groupMembership
    where messages.ToGroupID = groupMembership.gID;
    END;
    /

    and

    create or replace trigger SendGroupMessage
    after insert on messages
    FOR EACH ROW
    when (new.ToUserID is NULL)
    DECLARE
    newMsgID number(10);

    BEGIN
    newMsgID := :new.msgID;

    select userID as UserID
    from groupMembership
    where messages.msgID = newMsgID and messages.ToGroupID = groupMembership.gID;

    insert into messageRecipient values(newMsgID, UserID)
    END;
    /
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    840
    Rep Power
    387

    Cool


    You need to THINK Oracle not M$ sql.
    Try this:
    Code:
    CREATE OR REPLACE TRIGGER sendgroupmessage
      AFTER INSERT
      ON MESSAGES
      FOR EACH ROW
    BEGIN
      IF :new.togroupid IS NULL
      THEN
        INSERT INTO messagerecipient ( msgid, userid)
             VALUES (:new.msgid, :new.touserid);
      ELSE
        INSERT INTO messagerecipient
          SELECT :new.msgid, g.userid
            FROM groupmembership g
           WHERE g.gid = :new.togroupid;
      END IF;
    END;
    /
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    5
    Rep Power
    0
    Originally Posted by LKBrwn_DBA
    You need to THINK Oracle not M$ sql.
    Try this:
    Code:
    CREATE OR REPLACE TRIGGER sendgroupmessage
      AFTER INSERT
      ON MESSAGES
      FOR EACH ROW
    BEGIN
      IF :new.togroupid IS NULL
      THEN
        INSERT INTO messagerecipient ( msgid, userid)
             VALUES (:new.msgid, :new.touserid);
      ELSE
        INSERT INTO messagerecipient
          SELECT :new.msgid, g.userid
            FROM groupmembership g
           WHERE g.gid = :new.togroupid;
      END IF;
    END;
    /
    Using this trigger, I ran the following inserts into messages:

    Code:
    Insert into messages values (1, 1, 'are we meeting tomorrow for the project?', 2, NULL, '09-NOV-2012');
    Insert into messages values (2, 1, 'Peter''s pub tomorrow?', 5, NULL, '07-NOV-2012');
    Insert into messages values (3, 2, 'Please join our DB Group forum tomorrow', NULL, 1, '06-NOV-2012');
    The messageRecipient table only contains:

    Code:
         MSGID     USERID
    ---------- ----------
    	 1	    2
    	 2	    5
    The first part of the trigger (for messages sent to individual users) works but it still doesn't insert the userID's of group 1 members into the table.
  6. #4
  7. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    840
    Rep Power
    387

    Thumbs down


    Originally Posted by Kamonok
    Using this trigger, I ran the following inserts into messages:

    . . . Etc . . .
    The messageRecipient table only contains:

    . . . Blah, blah, blah ...

    The first part of the trigger (for messages sent to individual users) works but it still doesn't insert the userID's of group 1 members into the table.
    And? you must have bad data.
    Post full test with insert statements for the data in all tables.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    5
    Rep Power
    0
    All data in the two tables.

    Code:
    Insert into messages values (1, 1, 'are we meeting tomorrow for the project?', 2, NULL, '09-NOV-2012');
    Insert into messages values (2, 1, 'Peter''s pub tomorrow?', 5, NULL, '07-NOV-2012');
    Insert into messages values (3, 2, 'Please join our DB Group forum tomorrow', NULL, 1, '06-NOV-2012');
    Insert into messages values (4, 5, 'Here is the paper I will present tomorrow', NULL, 2, '04-NOV-2012');
    
    Insert into groupMembership values(1,1);
    Insert into groupMembership values(1,2);
    Insert into groupMembership values(1,3);
    Insert into groupMembership values(1,4);
    Insert into groupMembership values(1,5);
    Insert into groupMembership values(1,6);
    Insert into groupMembership values(1,7);
    Insert into groupMembership values(2,1);
    Insert into groupMembership values(2,2);
    Insert into groupMembership values(2,5);
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    5
    Rep Power
    0
    Thanks. I ran another insert statement after initializing all the tables with their initial data and the trigger worked. Thanks LKBrwn_DBA for your help on both triggers.
  12. #7
  13. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    840
    Rep Power
    387

    Cool


    Great!!!

IMN logo majestic logo threadwatch logo seochat tools logo