CODEWITHSUNDEEP
sql-servertriggersinsert
cheryl
cheryl

Reputation: 11

SQL Server trigger to Insert, Update, Delete data into another table based on changes in another table

I am extremely new to SQL Server (Express) and would very much appreciate anyone willing to help a newbie. I have a split Access database that I have migrated the tables into SQL Server. The last issue I need to resolve is to create a trigger that will update data rows in one table based on data updates/changes in another table. There are two tables I need to create triggers on, but I'm assuming once I get one right, I just need to use the same logic for the second one.

The gist of it is that data is entered in tblData and tblFamily and certain columns in those tables are the same as some columns in Contacts (for example, Name, Address, City, State, Zip...etc).

As new data is entered in one or both of the two tables, or data is updated in one or both of the two tables, or data is deleted in one or both of the two tables --- I need for that data to be the same in tblContacts.

I'm assuming the best way is to do a trigger, but if there's a better way, I'm happy to learn.

So far I've got this trigger:

CREATE TRIGGER ContactsTrigger 
ON [dbo].[tblData]
FOR UPDATE
AS
    INSERT INTO dbo.Contacts (LastName, FirstName, MiddleName, 
                              EmailAddress, HomePhone, MobilePhone, 
                              Address, City, StateProvince, ZIPPostal, 
                              id, ContactIDType)
        SELECT
            LastName, FirstName, MiddleName, 
            EmailAddress, HomePhone, MobilePhone, 
            Address, City, StateProvince, ZipPostal, 
            ID, ContactIDType
        FROM 
            INSERTED
GO

This seems to work for inserting a new record, but when I change data in an existing record in tblData, it just inserts a new record in Contacts so that I then have a duplicate record of sorts (old value and new value). I don't need this -- I need for it to UPDATE the record in the Contacts table when data is changed from that same record in tblData, while at the same time also continuing to insert NEW records or deleting records based on new records entered or old records deleted in tblData.

I feel very stupid asking for this as I'm sure it must be an easy solution, but I am very much a novice at this and am willing to look foolish to obtain a little help or direction. =D

Upvotes: 1

Views: 2989

Answers (1)

marc_s
marc_s

Reputation: 755531

You will need to decide whether to update or insert - based on the values entered. Then, in your trigger, you must have two separate statements - one to insert (as you have already), one to update.

Something like this:

CREATE TRIGGER ContactsTrigger 
ON [dbo].[tblData]
FOR UPDATE
AS
    IF ....... (you need to somehow decide whether to update or insert here) ......
    BEGIN
        INSERT INTO dbo.Contacts (LastName, FirstName, MiddleName,     
                                  EmailAddress, HomePhone, MobilePhone, 
                                  Address, City, StateProvince, ZIPPostal, 
                                  id, ContactIDType)
            SELECT
                LastName, FirstName, MiddleName, 
                EmailAddress, HomePhone, MobilePhone, 
                Address, City, StateProvince, ZipPostal, 
                ID, ContactIDType
            FROM 
                INSERTED
    END
    ELSE BEGIN
        UPDATE dbo.Contacts
        SET LastName = i.LastName, FirstName = i.FirstName, 
            MiddleName = i.MiddleName, EmailAddress = i.EMailAddress, 
            HomePhone = i.HomePhone, MobilePhone = i.MobilePhone, 
            Address = i.Address, City = i.City, 
            StateProvince = i.StateProvince, ZipPostal = i.ZipPostal, 
            ContactIDType = i.ContactIDType
        FROM
            Inserted i            
        WHERE 
            -- define some condition that links the rows in the `Inserted` pseudo table 
            -- with the `Contacts` table - I just **guessed** it might be the `ID` column
            ID = I.ID  


    END
GO

So for now, your job is to figure out what kind of condition you could test on to know whether a row is new and should be inserted, or updated.

  • Could it be the fact that the ID value already exists in Contacts?
  • Could it be the combination of first and last name? Other columns?

UPDATE: if your column ID is the deciding factor - try this: it first updates all those rows in dbo.Contacts that are already in the table, then it inserts those that aren't part of dbo.Contacts:

CREATE TRIGGER ContactsTrigger 
ON [dbo].[tblData]
FOR UPDATE
AS
    -- first update existing rows in dbo.Contacts with values from "Inserted"...
    UPDATE dbo.Contacts
    SET LastName = i.LastName, FirstName = i.FirstName, 
        MiddleName = i.MiddleName, EmailAddress = i.EMailAddress, 
        HomePhone = i.HomePhone, MobilePhone = i.MobilePhone, 
        Address = i.Address, City = i.City, 
        StateProvince = i.StateProvince, ZipPostal = i.ZipPostal, 
        ContactIDType = i.ContactIDType
    FROM
        Inserted i            
    WHERE 
        -- define some condition that links the rows in the `Inserted` pseudo table 
        -- with the `Contacts` table - I just **guessed** it might be the `ID` column
        ID = I.ID  

    -- then secondly insert new rows into dbo.Contacts 
    INSERT INTO dbo.Contacts (LastName, FirstName, MiddleName,     
                              EmailAddress, HomePhone, MobilePhone, 
                              Address, City, StateProvince, ZIPPostal, 
                              id, ContactIDType)
        SELECT
            LastName, FirstName, MiddleName, 
            EmailAddress, HomePhone, MobilePhone, 
            Address, City, StateProvince, ZipPostal, 
            ID, ContactIDType
        FROM 
            INSERTED i
        WHERE
            i.ID NOT IN (SELECT ID FROM dbo.Contacts);

GO

Upvotes: 1

Related Questions