CODEWITHSUNDEEP
sqlsql-server
charles tench
charles tench

Reputation: 3

Sql Insert Into table if Colid + multiple other columns are all unique

I have the following table setup

Campaigns: CampaignID PK CampaignName

CampaignActions: CampaignActionID PK CampaignActionName

CampaignActionSubmissions: CampaignActionSubmissionID PK UserID EmailAddress CampaignID CampaignActionID IPaddress Timestamp UserAddress

I am trying to insert a record only if The CampaignActionID + IPaddress and/or CampaignActionID + UserID does not exist and/or CampaignActionID + EmailAddress.

in other words: If CampaignActionID + any of the three (UserID, IPAddress, EmailAddress) exists, then do not insert.

The idea is to not make people log in, but to still prevent them from completing the same action multiple times. UserID is stored in a cookie. so UserID 1 completes CampaignactionID 1 with UserID A, IPAddress B, EmaiAddress C If userID 1 deletes cookies, to get new UserId, his Ipaddress still matches, do not insert If userID 1 uses new computer, his emailaddress still matches, do not insert. Is userID changes IPAddress, his UserID still matches, do not insert.

Upvotes: 0

Views: 36

Answers (1)

Avitus
Avitus

Reputation: 15958

I am just guessing at types but here's some psydo code:

 create procedure doInsert
         @camppaignActionID int,
         @ipaddress varchar(50),
         @userid varchar(50),
         @emailaddress varchar(50),
         @CampaignID int,
         @userAddress varchar(50)
    as

    if ( not exists (select 1 from CampaignActionSubmissions where camppaignActionID  = @camppaignActionID  and ipaddress = @ipAddress ) 
        and 
        not exists (select 1 from CampaignActionSubmissions where camppaignActionID  = @camppaignActionID  and userid= @userid) 
        and 
        not exists (select 1 from CampaignActionSubmissions where camppaignActionID  = @camppaignActionID  and emailaddress = @emailaddress )  ) 
    begin
         insert into CampaignActionSubmissions (  EmailAddress, CampaignID, CampaignActionID, IPaddress, Timestamp, UserAddress )
  values ( @emailaddress, @campaignID, @campaignActionId, @ipaddress, getdate(), @userAddress)
    end

Upvotes: 1

Related Questions