1

Morning, All -

I'm currently working on some SQL logic, and wanted to get some input on it.

What I'm trying to accomplish is the following (Psuedo-code):

When a record is inserted into Table A -
IF inserted.Field5 = 'ABC'    
    Select records from table B, where - 
        TableB:Field1 = inserted:Field1
        AND
        TableB:Field2 = inserted:Field2
    Set TableB.Field3 for selected record(s) to 'Hello World'

While I've worked with SQL in the past, I usually use external programs and languages to manipulate data.

Using SQL directly, it looks like the best way to do this is a trigger.

I've come up with a rough approximation (below) of what this might look like, but wanted to get some input before I went too much further with it.

CREATE TRIGGER I_Am_A_Trigger
    ON TableA
        AFTER Insert
            AS
                IF inserted.Field5 = 'ABC'
                    UPDATE [TableB]
                    SET [Field3] = 'Hello World'
                    WHERE
                        [Field1] = [inserted].[Field1]
                        AND
                        [Field2] = [inserted].[Field2]

Does this look like I'm heading down the right track?

Also - how can I best deal with the possibility of multiple records being inserted at once?

1

1 Answer 1

1

Here is an example of joining the virtual inserted table with TABLEB.

--demo setup
Drop table if exists TableA
Drop table if exists TableB

Create table TableA (Field1 varchar(20), Field2 varchar(20), Field5 varchar(20))
Create table TableB (Field1 varchar(20), Field2 varchar(20), Field3 varchar(20))

insert into TableB(Field1,Field2,Field3) values('one','two','three')
insert into TableB(Field1,Field2,Field3) values('four','five','six')
go
--the trigger
CREATE TRIGGER I_Am_A_Trigger ON TableA
AFTER INSERT
AS
UPDATE tb
SET tb.[Field3] = 'Hello World'
FROM inserted i
JOIN TableB tb
    ON i.Field5 = 'ABC'     --make sure to only join where the inserted Field5 was 'ABC'
        AND tb.[Field1] = i.[Field1]
        AND tb.[Field2] = i.[Field2]
GO

insert into TableA(Field1,Field2,Field5) values
('one','two','abc')

select * from TableB

| Field1 | Field2 | Field3      |
|--------|--------|-------------|
| one    | two    | Hello World |
| four   | five   | six         |

Including a check on Inserted.Field5 in the UPDATE statement takes care of multiple inserts; only those inserted rows where Field5 has the correct value will be used to update tableB.

1
  • Added a note explaining how this takes care of the possibility of a multi-row insert; hope you don't mind.
    – RDFozz
    Aug 22, 2018 at 20:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.