Solved

How to design a complex Trigger

Posted on 2003-11-03
14
275 Views
Last Modified: 2012-06-21
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.

General Info:
MS SQL Server 2000 std. SP(latest)

I several tables involved here:
Company  CP_
Contact  CT_
CompanyMembership  CPM_
Groups  GR_
ContactGroupRT  CTGRT_

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
      ON Company
      FOR UPDATE
AS
      DECLARE @Count int
      
      SELECT @Count = COUNT(*) FROM Deleted
      
      IF @Count > 0
      BEGIN
            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)))
      END
      
      SELECT @Count = COUNT(*) FROM Inserted
      
      IF @Count > 0
      BEGIN
            SELECT @MembershipGUID = CP_MembershipGUID FROM Inserted
      END
============================

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

Scott Carullo
scott@softtech.net
321-632-1924
0
Comment
Question by:carullos
  • 8
  • 3
14 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9673315
please post the table definitions

and if possible

a couple of example rows....


does this make sense?
select
Company  CP_
inner join Contact  CT_
on CP_GUID = CT_CompanyGUID
left outer join CompanyMembership  CPM_
on CP_MembershipGUID = CPM_GUID
left outer join ContactGroupRT  CTGRT_
CT_GUID = CTGRT_ContactGUID


ie cp_ : ct  is 1:m
   
0
 

Author Comment

by:carullos
ID: 9673434
Thanks for the reply...  My mind is not cooperating with me much on this problem, so if you are going to make a suggestion it would be a really good idea to make it a complete one.  I'm not sure where/what to do with the select statement you provided above.  Does not look like you used any of the information in the update triggers Inserted table - nor did you update the appropriate contactgroupRT table info based on the contacts type and the company membership.  I had an old SPROC that did some of this but only had to be for one supplied contact/company at a time.  See below:

CREATE procedure sp_AddContactToPrimaryGroups
      (
      @iContactID int,
      @iCompanyID int
      )

AS

BEGIN

      DECLARE @iID int
      DECLARE @iMID int
      
      SELECT @iID = Company_PrimaryContactID, @iMID = Company_Membership_ID FROM Company WHERE Company_ID = @iCompanyID      

      DELETE FROM GroupRT WHERE GroupRT_Group_ID IN (148,149,150,151,267,344,346) AND GroupRT_Contact_ID = @iID
      
      IF @iMID = 1
            INSERT INTO GroupRT (GroupRT_Group_ID, GroupRT_Contact_ID) VALUES (151,@iContactID)
      IF @iMID = 2
            INSERT INTO GroupRT (GroupRT_Group_ID, GroupRT_Contact_ID) VALUES (150,@iContactID)
      IF @iMID = 3
            INSERT INTO GroupRT (GroupRT_Group_ID, GroupRT_Contact_ID) VALUES (149,@iContactID)
      IF @iMID = 4
            INSERT INTO GroupRT (GroupRT_Group_ID, GroupRT_Contact_ID) VALUES (148,@iContactID)
      IF @iMID = 5
            INSERT INTO GroupRT (GroupRT_Group_ID, GroupRT_Contact_ID) VALUES (344,@iContactID)
      IF @iMID = 7
            INSERT INTO GroupRT (GroupRT_Group_ID, GroupRT_Contact_ID) VALUES (267,@iContactID)
      IF @iMID = 8
            INSERT INTO GroupRT (GroupRT_Group_ID, GroupRT_Contact_ID) VALUES (346,@iContactID)
Print @iID
END
GO

Scott
0
 

Author Comment

by:carullos
ID: 9673613
If you have the desire to see all the table info for whats involved, here it is....

CREATE TABLE [dbo].[Company] (
      [CP_GUID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [CP_VTXGUID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CP_Name] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CP_InternationalPhone] [bit] NOT NULL ,
      [CP_Phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CP_Fax] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CP_Address1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CP_Address2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CP_City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CP_State] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CP_Zip] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CP_Country] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CP_AltAddress1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CP_AltAddress2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CP_AltCity] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CP_AltState] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CP_AltZip] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CP_AltCountry] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CP_PrimaryAddress] [bit] NOT NULL ,
      [CP_AlternateAddress] [bit] NOT NULL ,
      [CP_County] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CP_Website] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CP_HQ] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CP_Description] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CP_TypeGUID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CP_MembershipGUID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CP_MembershipTypeGUID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CP_MembershipDate] [datetime] NULL ,
      [CP_LastUpdate] [datetime] NULL ,
      [CP_UpdatedByWho] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CP_CustomInt1] [int] NULL ,
      [CP_CustomStr1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CP_CustomStr2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CP_CustomStr3] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CP_CustomBit1] [bit] NOT NULL ,
      [CP_CustomBit2] [bit] NOT NULL ,
      [CP_Approved] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[CompanyMembership] (
      [CPM_GUID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [CPM_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CPM_VTXGUID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[CompanyMembershipType] (
      [CPMT_GUID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [CPMT_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [CPMT_VTXGUID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Contact] (
      [CT_GUID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [CT_VTXGUID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CT_CompanyGUID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CT_Prefix] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CT_FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CT_MiddleName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CT_LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CT_Suffix] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CT_Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CT_ContactAddress] [bit] NOT NULL ,
      [CT_Address1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CT_Address2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CT_City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CT_State] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CT_Zip] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CT_Country] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CT_InternationalPhone] [bit] NOT NULL ,
      [CT_Phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CT_Fax] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CT_Cell] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CT_AltPhone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CT_Assistant] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CT_AssistantPhone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CT_NoEmail] [bit] NOT NULL ,
      [CT_EmailAddress] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CT_Confidential] [bit] NOT NULL ,
      [CT_TypeGUID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CT_Designation] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CT_LastUpdate] [datetime] NULL ,
      [CT_UpdatedByWho] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CT_Location] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CT_CustomStr1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CT_CustomBit1] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ContactGroupRT] (
      [CTGRT_GroupGUID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [CTGRT_ContactGUID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [CTGRT_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CTGRT_VTXGUID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Groups] (
      [GR_GUID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [GR_VTXGUID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [GR_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [GR_OwnerGUID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [GR_Active] [bit] NOT NULL ,
      [GR_System] [bit] NOT NULL ,
      [GR_Locked] [bit] NOT NULL ,
      [GR_Private] [bit] NOT NULL ,
      [GR_Contact] [bit] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Company] WITH NOCHECK ADD
      CONSTRAINT [DF_Company_Company_UseInternationalPhone] DEFAULT (0) FOR [CP_InternationalPhone],
      CONSTRAINT [DF_Company_CP_PrimaryAddress] DEFAULT (0) FOR [CP_PrimaryAddress],
      CONSTRAINT [DF__Temporary__Compa__2F10007B] DEFAULT (0) FOR [CP_AlternateAddress],
      CONSTRAINT [DF__Temporary__Compa__300424B4] DEFAULT (0) FOR [CP_CustomBit1],
      CONSTRAINT [DF__Temporary__Compa__30F848ED] DEFAULT (0) FOR [CP_CustomBit2],
      CONSTRAINT [DF_Company_Company_Approved] DEFAULT (0) FOR [CP_Approved],
      CONSTRAINT [aaaaaCompany_PK] PRIMARY KEY  NONCLUSTERED
      (
            [CP_GUID]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[CompanyMembership] WITH NOCHECK ADD
      CONSTRAINT [aaaaaMembership_PK] PRIMARY KEY  NONCLUSTERED
      (
            [CPM_GUID]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[CompanyMembershipType] WITH NOCHECK ADD
      CONSTRAINT [PK_MembershipType] PRIMARY KEY  CLUSTERED
      (
            [CPMT_GUID]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[Contact] WITH NOCHECK ADD
      CONSTRAINT [DF__Temporary__Conta__25869641] DEFAULT (0) FOR [CT_ContactAddress],
      CONSTRAINT [DF_Contact_Contact_UseInternationalPhone] DEFAULT (0) FOR [CT_InternationalPhone],
      CONSTRAINT [DF_Contact_Contact_NoEmail] DEFAULT (0) FOR [CT_NoEmail],
      CONSTRAINT [DF__Temporary__Conta__267ABA7A] DEFAULT (0) FOR [CT_Confidential],
      CONSTRAINT [DF_Contact_CustomBit1] DEFAULT (0) FOR [CT_CustomBit1],
      CONSTRAINT [aaaaaContact_PK] PRIMARY KEY  NONCLUSTERED
      (
            [CT_GUID]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[ContactGroupRT] WITH NOCHECK ADD
      CONSTRAINT [aaaaaContactGroupRT_PK] PRIMARY KEY  NONCLUSTERED
      (
            [CTGRT_GroupGUID],
            [CTGRT_ContactGUID]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[Groups] WITH NOCHECK ADD
      CONSTRAINT [DF_Groups_Group_Active] DEFAULT (1) FOR [GR_Active],
      CONSTRAINT [DF_Groups_Group_System] DEFAULT (0) FOR [GR_System],
      CONSTRAINT [DF_Groups_Group_Locked] DEFAULT (0) FOR [GR_Locked],
      CONSTRAINT [DF_Groups_Group_Private] DEFAULT (0) FOR [GR_Private],
      CONSTRAINT [DF_Groups_Group_Contact] DEFAULT (1) FOR [GR_Contact],
      CONSTRAINT [aaaaaGroup_PK] PRIMARY KEY  NONCLUSTERED
      (
            [GR_GUID]
      )  ON [PRIMARY]
GO

0
 

Author Comment

by:carullos
ID: 9674109
I guess if I knew how to incorporate a cursor loop into the inserted section walking through each row in the inserted table and acting on each with a case statement (inserting one contact at a time into the COntactGroupRT table then that would work but I am not too up on doing cursors or triggers or select statements - I do some of each but do not know how to add them all together for my solution.  I'll be taking a stab at it though and post what I feel is close - psuedocode wise anyway.

Thanks -Scott
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9674194
sorry why do you need to single step around the inserted table?

surely you just want to do an

 insert into Table
    select  a,b,c from  inserted                      
            where ....
   
and maybe use case statement to convert the values (but preferably obtain them from reference tables..)


the select statement was just an attempt to understand your relationships....

i'm still not clear how the company table relates to a group....

and how an update to company can detect the group changes....

what is the logic that used in the updates...

is the company table updated in the correct position....
so that the trigger can obtain the required information?

going home will try and log back on later...  
0
 

Author Comment

by:carullos
ID: 9674551
I took a stab at it.  Can someone who is more experienced look this over and see if it should work and not cause me problems.  Thanks...  btw - this is for Primary contacts only which are designated by the CT_Designation = 'P' field.

CREATE TRIGGER AutoGroupPrimaryContactFromCompanyMembershipLevel
      ON Company
      FOR UPDATE
AS
      DECLARE @Count int
      
      SELECT @Count = COUNT(*) FROM Deleted
      
      IF @Count > 0
      BEGIN
            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)))
      END
      
      SELECT @Count = COUNT(*) FROM Inserted
      
      IF @Count > 0
      BEGIN
            DECLARE InsertedCursor CURSOR
            GLOBAL
            SCROLL
            KEYSET
            FOR
            SELECT CP_GUID, CP_MembershipGUID FROM Inserted
            
            DECLARE @CPGUID Int
            DECLARE @CPMembershipGUID Varchar(50)
            
            OPEN InsertedCursor
            FETCH NEXT FROM InsertedCursor INTO @CPGUID, @CPMembershipGUID
            
            WHILE @@FETCH_STATUS=0
            BEGIN
                  SELECT @Count = COUNT(*) FROM Contact WHERE (CT_CompanyGUID = @CPGUID) AND (CT_Designation = 'P')
                  IF @Count > 0
                  BEGIN
                        SELECT @ContactGUID = CT_GUID FROM Contact WHERE (CT_CompanyGUID = @CPGUID) AND (CT_Designation = 'P')
                        
                        IF @CPMembershipGUID = '0'
                              INSERT INTO ContactGroupRT (CTGRT_GroupGUID, CTGRT_ContactGUID)      VALUES ('346',@ContactGUID)
                        IF @CPMembershipGUID = '1'
                              INSERT INTO ContactGroupRT (CTGRT_GroupGUID, CTGRT_ContactGUID)      VALUES ('151',@ContactGUID)
                        IF @CPMembershipGUID = '2'
                              INSERT INTO ContactGroupRT (CTGRT_GroupGUID, CTGRT_ContactGUID)      VALUES ('150',@ContactGUID)
                        IF @CPMembershipGUID = '3'
                              INSERT INTO ContactGroupRT (CTGRT_GroupGUID, CTGRT_ContactGUID)      VALUES ('149',@ContactGUID)
                        IF @CPMembershipGUID = '4'
                              INSERT INTO ContactGroupRT (CTGRT_GroupGUID, CTGRT_ContactGUID)      VALUES ('148',@ContactGUID)
                        IF @CPMembershipGUID = '5'
                              INSERT INTO ContactGroupRT (CTGRT_GroupGUID, CTGRT_ContactGUID)      VALUES ('344',@ContactGUID)
                        IF @CPMembershipGUID = '7'
                              INSERT INTO ContactGroupRT (CTGRT_GroupGUID, CTGRT_ContactGUID)      VALUES ('267',@ContactGUID)
                  END                  
                  FETCH NEXT FROM InsertedCursor INTO @CPGUID, @CPMembershipGUID
            END
      END
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:carullos
ID: 9674631
Hey Lowfatspread, I will attempt to answer your questions (logic wise).  I think by reading my stab at it above will probably make sense as to what I'm trying to accomplish.  Heck, what I have above (pulling pieces out of my trusty SQL Server 7 book from Wrox) may even work so long as the syntax is correct.  I have not tested it yet but I will.

The logic:  
First of all, there are companies.  
Companies have contacts.
Companies have membership levels.
Contacts can be in one or more groups.

Companies are related to membership levels.
Contacts are related to Companies.
There are groups that are not related to anything, except contacts and companies and thats via the resolver table which is related to contacts and groups.  (not interested in the company group membership right now).

So...  in the end, the following will happen:
I can get a list (via the group members) of all the primary contacts for all companies who are GOLD members and send them an email, or whatever.

And the reason for the trigger is that it will automatically maintain these groups (which is why I called then Auto-Groups).  The trigger will move contacts between groups if the membership level changes for the company.

A new company will not have contacts yet, which is why company table insert trigger not necessary.  Likewise, a company that is being deleted will not have any contacts left to be in a group so its not necessary either - just Company table UPDATES to monitor membership changes.

I could handle this in my VB.NET application easily, but this trigger allows me to ship a commercial application free of customizations and then add auto-group customizations to the database which will be specific to the company using the app anyway.  One version of my app for all clients = happy coder.  Customizations for each client = happy client.

I'm sure that all makes sense right? ;-)

Thanks -Scott

0
 

Author Comment

by:carullos
ID: 9674774
This works for a single update (when company changes membership level):

REATE TRIGGER AutoGroupCompanyPCByMembershipLevel
      ON Company
      FOR UPDATE
AS
      DECLARE @Count int
      
      SELECT @Count = COUNT(*) FROM Deleted
      
      IF @Count > 0
      BEGIN
            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)))
      END
      
      SELECT @Count = COUNT(*) FROM Inserted
      
      IF @Count > 0
      BEGIN
            DECLARE InsertedCursor CURSOR
            GLOBAL
            SCROLL
            KEYSET
            FOR
            SELECT CP_GUID, CP_MembershipGUID FROM Inserted
            
            DECLARE @CPGUID Varchar(50)
            DECLARE @CPMembershipGUID Varchar(50)
            DECLARE @ContactGUID Varchar(50)
            
            OPEN InsertedCursor
            FETCH NEXT FROM InsertedCursor INTO @CPGUID, @CPMembershipGUID
            
            WHILE @@FETCH_STATUS=0
            BEGIN
                  SELECT @Count = COUNT(*) FROM Contact WHERE (CT_CompanyGUID = @CPGUID) AND (CT_Designation = 'P')
                  IF @Count > 0
                  BEGIN
                        SELECT @ContactGUID = CT_GUID FROM Contact WHERE (CT_CompanyGUID = @CPGUID) AND (CT_Designation = 'P')
                        
                        IF @CPMembershipGUID = '0'
                              INSERT INTO ContactGroupRT (CTGRT_GroupGUID, CTGRT_ContactGUID)      VALUES ('346',@ContactGUID)
                        IF @CPMembershipGUID = '1'
                              INSERT INTO ContactGroupRT (CTGRT_GroupGUID, CTGRT_ContactGUID)      VALUES ('151',@ContactGUID)
                        IF @CPMembershipGUID = '2'
                              INSERT INTO ContactGroupRT (CTGRT_GroupGUID, CTGRT_ContactGUID)      VALUES ('150',@ContactGUID)
                        IF @CPMembershipGUID = '3'
                              INSERT INTO ContactGroupRT (CTGRT_GroupGUID, CTGRT_ContactGUID)      VALUES ('149',@ContactGUID)
                        IF @CPMembershipGUID = '4'
                              INSERT INTO ContactGroupRT (CTGRT_GroupGUID, CTGRT_ContactGUID)      VALUES ('148',@ContactGUID)
                        IF @CPMembershipGUID = '5'
                              INSERT INTO ContactGroupRT (CTGRT_GroupGUID, CTGRT_ContactGUID)      VALUES ('344',@ContactGUID)
                        IF @CPMembershipGUID = '7'
                              INSERT INTO ContactGroupRT (CTGRT_GroupGUID, CTGRT_ContactGUID)      VALUES ('267',@ContactGUID)
                  END                  
                  FETCH NEXT FROM InsertedCursor INTO @CPGUID, @CPMembershipGUID
            END
      END


Now I have a couple more questions...  Does anyone see a problem with a way this trigger if handling its job?  In what circumstance would there be multiple rows in a trigger Inserted/Deleted tables for UPDATES.  If I performed the following query from query analyzer and 10 rows were updated would this create 10 rows in the trigger tables?  

UPDATE Company SET CP_Flag = 1 WHERE CP_Name LIKE 'BOO%'

Does the amount of rows in the trigger tables = the number of affected rows per statement?  Per BEGIN - END contents, per transaction?  Can someone explain this please - no guesses!  Tell me only if you are sure - thanks.

Also, can I check the Inserted rows in the trigger and see if the CP_MembershipGUID was updated - and if not exit the trigger without doing anything?

Thanks -Scott

0
 

Author Comment

by:carullos
ID: 9675334
Things seemed to be working fine, but in my last test I ran into a problem updating multiple rows.  This query:
update company set cp_custombit2 = 0 where (cp_name like 'softtech%') or (cp_name like 'space coast network%')

returned the following:
(2 row(s) affected)

Server: Msg 16915, Level 16, State 1, Procedure AutoGroupCompanyPCByMembershipLevel, Line 25
A cursor with the name 'InsertedCursor' already exists.
The statement has been terminated.
0
 

Author Comment

by:carullos
ID: 9675349
oops, looks like a little cleanup was missing...

I need to close the cursor & deallocate the cursor

That whould fix that problem - ignore the last.

Scott
0
 
LVL 15

Accepted Solution

by:
namasi_navaretnam earned 250 total points
ID: 9675639
It looks like you have got it to work.

It is better to use Exists rather than IN.

DELETE FROM ContactGroupRT
   WHERE (CTGRT_GroupGUID IN ('151','149','150','344','148','267','346'))
       AND EXISTS ( SELECT 1
                                FROM Contact c , Deleted d
                             WHERE c.CT_GUID = d.CP_GUID  and
                                        ContactGroupRT.CTGRT_ContactGUID  = c.CT_GUID  
)

Instead of
SELECT @Count = COUNT(*) FROM Deleted
     
     IF @Count > 0
     BEGIN
     End

You can use ,

If Exists(select 1 from deleted)
BEGIN

END

For inserts,

Please see if you can accomplish that by joining table rather than using a cursor.

These will improve performance drastically.

:)
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 250 total points
ID: 9690459
>>Does the amount of rows in the trigger tables = the number of affected rows per statement?  Per >>BEGIN - END contents, per transaction?  Can someone explain this please - no guesses!  Tell me >>only if you are sure - thanks.

the rows in the trigger tables are those affected by the "current" statement
MS SQL server triggers are statement level triggers (not row or transaction level)
the triggers are also AFTER triggers.. the update has occurred if you want to disallow it
then it must be rolled back...

   

>>Also, can I check the Inserted rows in the trigger and see if the CP_MembershipGUID was >>updated - and if not exit the trigger without doing anything?
Yes
you join the inserted and deleted tables to confirm the data which has changed
there is also an updated_column function...

hth...

btw there are also (in sqk2k) INSTEAD OF TRIGGERS which can be used...

 

0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now