Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Update Column Using INSERT trigger

Posted on 2014-03-10
3
Medium Priority
?
456 Views
Last Modified: 2014-03-10
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.
0
Comment
Question by:ccleebelt
3 Comments
 
LVL 6

Expert Comment

by:c1nmo
ID: 39919143
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
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 39919205
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
 

Author Closing Comment

by:ccleebelt
ID: 39919405
Once again, you are the man.  Thank you.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

877 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question