Update Column Using INSERT trigger

Hi All,

I really struggle when it comes to writing triggers so I figured I would ask the experts.

I have a table called "group_members" which has the following schema:

group_member_id PK int
group_id int FK to table "groups"
contact_id int FK to table "contacts"

Whenever a record is inserted into this table I want to do the following

Query the groups table for the ID of the group inserted above and retrieve the value groups.pricing_level

Then, update the field, contacts.pricing_level in the contacts table.

In other words, we add a member to a group, retrieve the pricing level for the group and update to contact to have the same level.

Any help appreciated.  Trigger contexts always baffles me.
ccleebeltAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
CREATE TRIGGER group_members__TRG_INSERT
ON dbo.group_members
AFTER INSERT
AS
SET NOCOUNT ON
UPDATE c
SET c.pricing_level = g.pricing_level
FROM inserted i
INNER JOIN dbo.groups g ON
    g.group_id = i.group_id
INNER JOIN dbo.contacts c ON
    c.contact_id = i.contact_id
GO
0
 
c1nmoCommented:
Would that not create redundancy, what if the groups.pricing_level value changed in the future?  Maybe better to retrieve the value dynamically when needed?
0
 
ccleebeltAuthor Commented:
Once again, you are the man.  Thank you.
0
All Courses

From novice to tech pro — start learning today.