Create Sql Trigger to add new rows based on field in other table

Question

I need to create trigger that add new row in table when the value in other table is upadated. For example, If I change the value into 5 in table1, I need to create in other table (table2) five (5) new row with some values of table1 (for example ID or get also other value).

Example: Table1

ID     Date     Flag   NumberOfNewRowsInTable2
1   17/07/2017    1              0

When change into 5 the value of column "NumberOfNewRowsInTable2" I need to create in Table2 new five rows:

Table2

ID     Field1     Field2
1        X          Y
1        X          Y
1        X          Y
1        X          Y
1        X          Y

Thank you all, sorry for my bad explain.I use SQL Server. Now I try to explain it better. I need to create a payment by instalments and so I want the user to choose a number of instalments and automatically I would have in the table2 (payment instalments table) the equivalent number of rows (one for each instalments) . Now, for example: There is the Table1. In the Table1 there is an ID field and I can choose the type of payments for the record. When the user choose the payment by instalments, he can choose also the number of instalments. When the user select the desired number of instalments, automatically I would have in the table2 (payment instalments table) the equivalent number of rows (one for each instalments) and the corrisponding ID (in the field ID of Table2). The other problem is this: when user remove the payment by instalments and insert other kind of payment, I need to remove it from the Table2. Moreover, if the user change the number of instalments, I need to adjust the rows with the new equivalent number of instalments. I hope to explain it better and thank you all for your answers. Andrea

It is possible to do this with a trigger? Thank you all, Andrea.

Please tell us which RDBMS you are using, as trigger syntax is vendor specific. — Tim Biegeleisen, Jul 18 '18 at 14:03
This smells of implementing physical or forms processes in a database rather than modelling the *data*. Can rows be changed in table2 after they're inserted? If so, what should happen (since you say we do this based on *updates* to table1) if `NumberOfNewRowsInTable2` is *reduced* from 10 to 5 - which rows should be removed from table2? If they're not changable, why do they exist at all (even if you need to *display* five rows on a screen somewhere, that doesn't mean you need to *store* five rows when you can compute their existence entirely from `table1`, on demand) — Damien_The_Unbeliever, Jul 18 '18 at 14:15

movip1991 · Answer 1 · 2018-07-19T14:25:38.217

This certainly can be done with a trigger. You didn't specify which RDBMS you're using in the question, but my knowledge is in T-SQL, so that's what this answer will be written in.

First off, it looks like the number of rows you want to insert is dynamic based on that column value, so you'll want a Numbers table in your database if you don't have one already. That will make it considerably easier to add as many rows as you need without trying to rely on loops. You can find some methods of properly creating and populating a Numbers table here, but I just made something small for this example:

CREATE TABLE dbo.Numbers
(
    ID bigint
)

INSERT INTO dbo.Numbers
VALUES (1), (2), (3), (4), (5)

Next, the trigger itself. T-SQL triggers will only be triggered by changes to the table that they are created on, so you want to create the trigger on Table1. The contents of the trigger can then insert/update/delete rows in any number of other tables. I only say this explicitly because the phrasing of your question seems to imply that you were thinking of it the other way around -- that the trigger would be on Table2 -- and I apologize if I'm reading too much into that. Anyhow, the code for the trigger:

CREATE TRIGGER dbo.tgrTable1_Update
    ON dbo.Table1
    AFTER UPDATE
AS
BEGIN
    INSERT INTO dbo.Table2
    (
        ID,
        Field1,
        Field2
    )
    SELECT
        inserted.ID,
        'X',
        'Y'
    FROM inserted
    JOIN deleted
        ON deleted.ID = inserted.ID
    JOIN dbo.Numbers n
        ON n.ID <= inserted.NumberOfNewRowsInTable2
    WHERE inserted.NumberOfNewRowsInTable2 != deleted.NumberOfNewRowsInTable2
END

The join condition on the Numbers table ensures that you're only adding the number of rows that you need, and nothing more. You can tweak the joins and where clause to fit the conditions that you actually need.

Edit: Thanks for adding some extra detail to your question. A trigger like this should still be able to do what you describe. You'll just need to add some logic to the contents of the trigger to handle the scenarios that you foresee. You might want to consider changing the contents of the SELECT to contain the installment calculations, or using a soft-delete column on Table2 so you're not adding and deleting rows constantly in case that matters for historical data. You can also do multiple insert/update statements within one trigger, so you should be able to fit all of your required logic into this structure.

Create Sql Trigger to add new rows based on field in other table

1 Answers1