How to design a complex Trigger
Posted on 2003-11-03
This will be a bit difficult to give you all the information necessary to have a complete understanding of what I'm trying to do, but I'll give you the basics and see how that goes first.
Let me start by thanking you for reading this and providing any possible solutions - I appreciate your time.
MS SQL Server 2000 std. SP(latest)
I several tables involved here:
Contacts are related to companies via CP_GUID = CT_CompanyGUID
There are defined membership types in the company membership table.
Company's can be members related by CP_MembershipGUID = CPM_GUID
The groups table contains lots of defined groups. Contacts can be in many groups - which is why there is the ContactGroupRT resolver table.
CT_GUID -- CTGRT_ContactGUID -- CTGRT_GroupGUID -- GR_GUID
What I am trying to accomplish:
I have an application that allows the user to change the company membership level. I want to automaticly modify contacts group memberships based on the companys membership level. Contacts can be one of these three types of contact: Primary, Secondary, NULL (not defined as primary or secondary). So... A company record (or multiple records) are updated and the Trigger I need to define acts upon UPDATES. I already handle company deletions and additions via the application. When the company information is updated I need to automatically add/modify the groups memberships for any primary and secondary contacts for the company. After all is said and done, I should be able to get a list of primary contacts for all companies whos membership level is Gold, or all the secondary contacts for the PLATINUM level members. Once they are in the defined groups I can do anything really. They should always be up to date because the trigger we are discussing here keeps the groups up to date. If I change the membership level of a company from GOLD to SILVER then the contacts (primary and secondary) should be removed from any auto-groups defned based on the deleted table and then added to any defined groups based on the Inserted table. Remember this is only for UPDATES to the company table. Also, The groups are pre-defined so we can hard-code their GUIDs in the trigger. For discussion sake lets say there are three auto-groups defined: Platinum (GUID='P') Gold (GUID='G') Silver (GUID='S')
Here is the beginnings of what I had when I got stumped:
CREATE TRIGGER AutoGroupPrimaryContactFromCompanyMembershipLevel
DECLARE @Count int
SELECT @Count = COUNT(*) FROM Deleted
IF @Count > 0
DELETE FROM ContactGroupRT
WHERE (CTGRT_GroupGUID IN ('151','149','150','344','148','267','346'))
AND (CTGRT_ContactGUID IN (SELECT CT_GUID FROM Contact WHERE CT_CompanyGUID IN (SELECT CP_GUID FROM Deleted)))
SELECT @Count = COUNT(*) FROM Inserted
IF @Count > 0
SELECT @MembershipGUID = CP_MembershipGUID FROM Inserted
The Inserted is where I'm not sure what to do because there can be multiple rows and thats where I get stumped. I was setting the trigger above up to handle only the Primary contacts, was going to be another trigger for secondary contacts unless you can provide a solution that handles both at the same time.
If you read this far, you're cool - thanks. Look forward to figuring this out and learning something from you gurus. THANKS AGAIN