So should I first execute the trigger (with all three components) and then write code that will insert, edit, delete rows in the members table? Do I need to keep re-running my triggers after each?
Yes. For some reason, when I run the trigger code it states Server: Msg 207, Level 16, State 1, Procedure UpdateMemberCount, Line 7 Invalid column name 'count'. Server: Msg 207, Level 16, State 1, Procedure UpdateMemberCount, Line 15 Invalid column name 'count'.
oops, not good writing free hand like this. Anyhooo.. CREATE TRIGGER UpdateMemberCount ON [dbo].[members] FOR Insert, Delete, Update AS if update(groupid) Begin Update groups set NumMems = (Select count(*) from members where groupid= Inserted.groupid) from Inserted where groups.gid= Inserted.groupid End Update groups set NumMems= (Select count(*) from members where groupid= Deleted.groupid) from Deleted where groups.gid= Deleted.groupid ============== As for the newmem table: In query analyzer, right click the members table > script object to new windows as > create Then change the auto generated script from create members to create newmen. Presto, instant create table script.
The problem is it has in that code, CREATE TABLE [NewMem] ( [SSN] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [lname] [varchar] (40) COLLATE .................................................................................................SQL_Latin1_General_CP1_CI_AS NULL , CONSTRAINT [PK_members] PRIMARY KEY CLUSTERED ([SSN]) ON [PRIMARY] ) ON [PRIMARY] I am suppose to create it from scratch, but can't seem to find the right code to do so (example). Should I edit this and remove the COLLATE portions? The first part of the code worked (trigger), I just need to test it. By testing, do I need to run the tirgger 3 times after I do an update, then delete, then insert? Or is it all set once I execute it once?
I ran it as it was..... it said succesful, but i cant seem to find the table lol? Is it under a diff folder name?
The trigger, you just create it once, and it will automatically run everytime the members gets updated/inserted/deleted. Just set it and forget it =) Yeah, just edit that script and remove the stuff you don't want. CREATE TABLE [NewMem] ( [SSN] [char] (11) COLLATE NOT NULL , [lname] [varchar] (40) AS Null, ... ) Is this your homework assignment? =)
Yes it is an assignment,that is basically impossible for me, and I have been trying at it for hours, looking at class notes/the book, and none of it is really helping much, For the create table, if I delete that stuff, won't it affect the actual creating of the fields and cause errors? I created, and refreshed, but it still didnt show up?
Just be sure to create all the fields you need, set the right datatype and declare it as null or not null, same as the original table. Are you sure you ran the script in the right database?
The table is there, just not viewable from the folder (but when I do code it can display when I ask it to). What I don't understand how to do is to write code that can UPDATE or DELETE rows from the members table. Any ideas?
You guys are learning about triggers but have gotten into updating or deleting records from a table? Try Delete from where [condition] and Insert into (field1, field2) values(value1,value2)
Lets say I inserted INSERT into NewMem Values ('123-46-7844', 'Duncan', 'Tim', '345 445-5645', '32 Sun Dr.', 'Phoenix', 'AZ', '38575', '00100') How would I remove that Delete from [NewMem] where [SSN='123-46-7844'] ?? How do I remove the entire row?
For the update how would I change just one column cell Update from NewMem where SSN='123-46-7844' ................................? (and then change the groupid from say 5 to 6?)
Update members set groupid = 34 where SSN='123-46-7844' You only need ' around varchar, char, datetime etc.. if it's a int, tinyint etc... you won't need it.
