Databases
StackOverflow2013
Postsdbo
POCreating a query that iterates through list of ids from table A inserting a new row in table B for each item
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCreating a query that iterates through list of ids from table A inserting a new row in table B for each item
    text
    copied!<p>I am trying to implement a simple notification system that inserts "Notification" rows into a table "Notifications" for each user who has participated in a thread. The code I have takes place in a trigger and currently works on notifying a user that another user has responded to his specific comment, but does not notify everyone else in the thread that a new comment has been posted.</p> <pre><code>ALTER TRIGGER OnDocumentCommentPosted ON DocumentComments AFTER INSERT AS IF (SELECT InResponseTo FROM inserted) IS NOT NULL BEGIN INSERT INTO Notifications ([CommentID],[Type],[RecieverId]) //This is a notification object from table Notifications shown at the bottom of this post SELECT id, 'DocumentComment', InResponseTo //this is a DocumentComment object from table DocumentComments shown at the bottom of this post FROM inserted END </code></pre> <p>I basically want to grab the results from the below query and then run the same query above for each result.</p> <pre><code>SELECT OwnerId FROM DocumentComments WHERE DocumentID = @DocumentId </code></pre> <p><strong>gets me a list of ids</strong></p> <p><em>then for each id in list run the below query substituting each id into @id</em></p> <pre><code>SET @Comid = (SELECT id FROM inserted); INSERT INTO Notifications ([CommentID],[Type],[RecieverId]) @Comid, 'DocumentComment', @id </code></pre> <p>Is there a way to join this type of functionality into my original trigger query above?</p> <p><strong>EDIT</strong></p> <p>It seems this might be a bit confusing to comprehend so let me try to explain my table structure simply</p> <p><strong>Brief explanations</strong></p> <p>Table: Documents - (Holds the actual data for excel, pdfs, whatever is uploaded via users)</p> <p>Table: DocumentComments - (Holds comments made on each document [One - Many])</p> <p>Table: Notifications - (Holds notification data for each comment made and provides boolean if it has been read or not</p> <p><strong>Underlying structure</strong></p> <p><strong>Table Documents</strong> <br/> PK = id</p> <p><strong>Table DocumentComments</strong><br/> PK = id<br/> FK = DocumentId<br/> InResponseTo = UserId of the user who's comment has been responded to by inserting comment OwnerId = UserId of the user who owns or created this comment</p> <p><strong>Table Notifications</strong> (Bears no direct relationship with DocumentComments since this table is a generic table that holds many types of notifications, not just DocumentComment notifications<br/></p> <p>PK = id<br/> FK = RecieverId = The UserId of the user who will be receiving this notification.<br/> CommentID = if the notification deals with a comment this value will be filled with the comment in question</p> <p>AnnouncementID = if the notification deals with an announcement this value will be filled with the announcement in question</p> <p>EventID<br/> MeetingID = Both of these fields work the same as the above two fields.<br/> <br/> Read = boolean, read state of the notification</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload