Im supposed to test the trigger by inserting rows in the members table, deleting rows from the members table, updating a row by changing the groupid and then print out both the members table and the groups table. (Then Test putting in several rows by creating a new table, NewMem with the same head as the members table, putting some rows in it and then inserting all the rows of NewMem into members at once.) Im confused as to go about testing this trigger. How do I go about inserting the rows into the members table, and updating a row etc...?
Oh also, about the update, you would only want to recalc NumMems if the groupid was changed. You can check if the groupid has changed using the follow commands IF NOT UPDATE(groupid) RETURN Put that in the update trigger before the recalc, so if groupid wasn'tt changed you exit out of the trigger.
You can insert/update/delete from the members table like normal. A trigger won't affect that, only after the action took place (for an after trigger that is)
CREATE TRIGGER MembersDelete ON members After Delete AS Update groups set NumMems = (Select count(*) from members where groupid= Deleted.groupid) from Deleted where groups.gid= Deleted.groupid IF NOT UPDATE(groupid) RETURN Does this account for all three INSERT, DELETE and UPDATING? How would I test these, just by trying to insert new members, delete some, and update them? What does that code above do exactly in this case (even though u have them seperately, the three Ins, delete and update)?
the trigger should automatically update the groups table, affecting only those rows which had changes in the number of members in the group. This occurs when a member is inserted, deleted, or is updated to change group. Would this be the case?
wrong forum. http://www.nerd-out.com/forum/index.php jesus christ, i can't believe i just read this thread. i need a beer.
How can I combine this trigger to include all of the inerseting, updating and deleting? Your mentioned trigger is only for delete right?
No, that was only the delete trigger. Also, the if not update groupid line isn't in the right trigger, that is only for the update trigger. Delete trigger: CREATE TRIGGER MembersDelete ON members After Delete AS Update groups set NumMems = (Select count(*) from members where groupid= Deleted.groupid) from Deleted where groups.gid= Deleted.groupid ==================================== Update Trigger CREATE TRIGGER MembersUpdate ON members After Update AS IF NOT UPDATE(groupid) RETURN Update groups set NumMems = (Select count(*) from members where groupid= Inserted.groupid) from Inserted where groups.gid= Inserted.groupid ===================================== The insert trigger is the same as before, I just changed the name: CREATE TRIGGER MembersInsert ON members After Insert AS Update groups set NumMems = (Select count(*) from members where groupid= Inserted.groupid) from Inserted where groups.gid= Inserted.groupid ====================================== Triggers are a set of commands that get performed when a specified action occurs on the table. So in your case, whenever a row gets inserted into member table, the insert trigger fires, which calculates the number of members for the groupid of the newly inserted row and then updates the groups table. Same thing happens when a delete occurs. The delete trigger fires and recalcutes the number of memebers for the groupid of the row that just go deleted. The same thing for when an update occurs, except the trigger checks to see if groupid was updated. If not, there isn't a point of recalculating, as the number of members hasn't changed. To just test perform a bunch of insert/deletes/updates to the members table. So if you insert 3 rows with groupid of 10 and there were already 2 rows with groupid of 10, the trigger should have fired and updated NumMems in the groups table with 5 where gid = 10
I don't mean to change the subject, but this is indeed one of the funniest threads of the year. Good times
The question is Write a trigger that maintains the correct values of NumMems on update (of the groupid column in the members table), or on delete or insert in the members table. Remember that one update, delete or insert command may affect more than one row. Test your trigger by inserting rows in the members table, deleting rows from the members table, updating a row by changing the groupid and then print out both the members table and the groups table. (Test putting in several rows by creating a new table, NewMem with the same head as the members table, putting some rows in it and then inserting all the rows of NewMem into members at once.) How would these three triggers be combined to form one trigger statement? Instead of three seperate ones? It wouldn't work if I had to fire them seperately right?
Try this. It ain't pretty, but this should work for you. CREATE TRIGGER UpdateMemberCount ON [dbo].[members] FOR Insert, Delete, Update AS if update(groupid) Begin Update groups set count = (Select count(*) from members where groupid= Inserted.groupid) from Inserted where groups.gid= Inserted.groupid End Update groups set count = (Select count(*) from members where groupid= Deleted.groupid) from Deleted where groups.gid= Deleted.groupid Its hard writing code in this little box.
How would I create the NewMem "(Test putting in several rows by creating a new table, NewMem with the same head as the members table, putting some rows in it and then inserting all the rows of NewMem into members at once.)"
Im starring to think maybe those 3 seperate statements would be a good way of doing it. But how would I create the new table (Test putting in several rows by creating a new table, NewMem with the same head as the members table, putting some rows in it and then inserting all the rows of NewMem into members at once.)
There was a flaw in the original update trigger. It didn't take into account recalculating the count of the groupid that got replaced.
So is what you have above basically 2 pieces of code or three? The three pieces you wrote before this one has that error you mention?
yeah, there is a logic flaw in the individual update trigger. It isn't there in the combined trigger.
To create the NewMems table would I just do something like Create Table NewMems...... The thing is I was given the members table, so I don't understand how to create it from scratch into a new table (even though it has the same headings etc...)
