-1

In my database I have 2 tables linked together with a foreign key relationship.

Parent Table = controls.slot_table

Child Table = controls.IO_table

When a new row is inserted into the parent table (A), I would like to insert a row into table B and link it via foreign key to the new row in table A.

My rough draft looks like this (I know it has syntax errors, but this is the idea):

CREATE TRIGGER controls.Test_Insert_Linked_Row ON controls.slot_table AFTER INSERT AS
BEGIN

    SET NOCOUNT ON

    INSERT INTO controls.IO_table
    (
        point_description_A,
        PLC_slot_ID

    )
    VALUES
    (
        'Test Automatically Generated Line',
        t1.ID_auto
    )

    FROM controls.slot_table as t1
    LEFT JOIN inserted as i

END
GO
3
  • I guess you meant ...VALUES ( 'Test Automatically Generated Line', inserted.ID_auto )
    – mustaccio
    Jun 23, 2023 at 17:12
  • @mustaccio I appreciate the suggestion, but your code using inserted.ID_auto causes the error "multi-part identifier could not be bound".
    – Hibbert
    Jun 23, 2023 at 17:33
  • Well, I just used your pseudo-syntax to show what needs to change in your example.
    – mustaccio
    Jun 24, 2023 at 1:56

1 Answer 1

0

Triggers run for each statement, so you need INSERT ... SELECT not INSERT ... VALUES. And there's no need to join the target table; INSERTED has everything you need.

INSERT INTO controls.IO_table(point_description_A, PLC_slot_ID)
SELECT 'Test Automatically Generated Line', i.ID_auto
FROM inserted  i

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.