CODEWITHSUNDEEP
sqlsql-server
Revokez
Revokez

Reputation: 323

Using a trigger to insert values into another table unless value exists in which case update

I have a table (Table1) which has three columns: 'ID' , 'Status' , 'Title'. I have second table (Table2) that has three columns: 'ID' , 'Table1ID' , 'Title'.

ID is a sequential number value, title is an alphanumeric value and status have values of either 0 or 1. Starting with a value of 0 when the record is created in Table1.

What I'm trying to achieve is a trigger: that when [Table1].Status changes from value 0 to 1 then to insert a new row into [Table2] with the value of [Table1].ID into [Table2].Table1ID and [Table1].Title into [Table2].Title of only the updated record in [Table1].

However if [Table2].Table1ID already has a matching value of [Table1].ID on the updated value then instead of inserting a new row it should simply update the value of [Table2].Title.

Here's what I've attempted so far:

Create Trigger [dbo].[test]
ON [dbo].[Table1]
After Update
As Begin
Set NOCOUNT ON;
IF UPDATE (Status)
Begin
INSERT INTO [Table2]
VALUES ((SELECT DISTINCT ID FROM INSERTED WHERE NOT EXISTS (SELECT * FROM TABLE2 WHERE TABLE2.TABLE1ID = INSERTED.ID)))

END
END
Go

Upvotes: 0

Views: 1574

Answers (1)

Sean Lange
Sean Lange

Reputation: 33571

If I understand all the requirements I think you need something like this. First it will update Table2 only when the status changes. Since you are updating the Title I would think you would update it when the value of Title changes but that isn't what you stated you want.

Then it will insert any rows that don't exist already.

Please notice that this will work no matter how rows get updated in a single update statement.

Create Trigger [dbo].[test]
ON [dbo].[Table1]
After Update
As Begin
    Set NOCOUNT ON;

    --first we need to update any existing rows in Table2
    update t2
    set Title = i.Title
    from inserted i
    join deleted d on d.ID = i.ID 
    join Table2 t2 on t2.ID = i.ID
    where d.Status = 0 --only where the row in Table1 has a status of 0
        and i.Status = 1 --only when the new value has status = 1

    --now we can insert any rows that don't already exist into Table2
    INSERT INTO [Table2]
    (
        ID
        , Title
    )
    SELECT i.ID
        , i.Title 
    FROM INSERTED i
    WHERE NOT EXISTS 
    (
        SELECT * 
        FROM TABLE2 
        WHERE TABLE2.TABLE1ID = i.ID
    )

END

Upvotes: 1

Related Questions