Insert trigger to update a second table.

[MartinSmithhh]

My SQL knowledge is currently pretty limited so I would really appreciate some help with the following:

I have 2 tables (say table1 and table2)

table1 is a list of users and contains the fields: UKey, SKey, and GKey (all int)

table2 contains a running total of members with a particular SKey/GKey combination and contains the fields UKey, SKey, and TotalUsers (also all int)

I would like to write an insert trigger so that when a new record is added to table1 it checks to see if that SKey/GKey combination already exists in table2

If so: the TotalUsers field for that combination needs to be incremented.

If not: a new row should be added to table2 with that SKey and GKey and totalusers =1

If anyone has any sample code which would achieve something like this it would be very much appreciated!

Obviously I will also need to write delete and update triggers but hopefully I should be able to figure that out for myself when I get the insert one nailed!

 

[MartinSmithhh]

OK I think I have figured out how to do it now, but any comments on efficiency/better ways would be appreciated!

Create Trigger Update_Users
For Insert as

Declare @SKey int
Declare @GKey int
Declare @UKey int

Select @UKey=UKey, @SKey=SKey, @GKey=GKey
From Inserted

If Exists(Select 1 From table2 Where SKey=@SKey And GKey=@GKey)
Begin
Update table2
Set TotalUsers = TotalUsers + 1
Where SKey = @SKey
And GKey = @GKey
End
Else
Begin
Insert Into table2(UKey, SKey, GKey, TotalUsers)
Values (@UKey, @SKey, @GKey, 1)
End