SQL Server Trigger Insert Values from New Row into Another Table

Sql Server trigger insert values from new row into another table

try this for sql server

CREATE TRIGGER yourNewTrigger ON yourSourcetable
FOR INSERT
AS

INSERT INTO yourDestinationTable
        (col1, col2    , col3, user_id, user_name)
    SELECT
        'a'  , default , null, user_id, user_name
        FROM inserted

go

Trigger to insert one record to new rows from another table

You need to include the Inserted pseudo table into your statement, to find the rows that were actually updated - and I would recommend using proper JOIN syntax instead of those nested subqueries - seems a lot easier to read and understand for me.

So try this:

CREATE TRIGGER dbo.TR_CHANGES
ON dbo.SYSTEM 
AFTER INSERT
AS
    UPDATE t1
    SET col3 = t2.col1
    FROM table1 t1
    INNER JOIN table2 t2 ON t1.col3 = t2.col1
    INNER JOIN inserted i ON t1.primarykeycol = i.primarykeycol

You need to replace the .primarykeycol for the Inserted and t1 tables with the actual primary key column for your table - this is needed to link the Inserted rows with the actual data table

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

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

INSERT TRIGGER AFTER UPDATE ANOTHER TABLE

Your code should be using the new variable to reference rows. In addition, your tables should be defining the ids as auto-increment.

So, the code should look more like this:

create trigger `update_order_pizza` after update on `orders` 
for each row
begin

    insert into order_pizza (order_id)
        values (new.order_id);

end;

Alternatively, the pizza_id might be in the orders table, and you may intend:

    insert into order_pizza (order_id, pizza_id)
        values (new.order_id, new.pizza_id);

Create trigger that checks for a date from another table

There are a few things to include in a trigger. Returning extra counts is not a good thing in general. (I am not sure what count is returned for an instead of trigger. The rule to include the following statement might be something to investigated. We would not want both the original insert and the replacement counts. Does SQL Server suppress the count for the original insert if there's an instead of trigger?)

SET NOCOUNT ON

A test if there is nothing to do. @@ROWCOUNT (or the bigint version) is not reliable for this anymore because the source can be a merge statement. If the trigger fires on deletes, then testing the "deleted" table is also needed. The first statement below is good enough for inserts. The second is okay for inserts, updates, and deletes. (Don't use both.)

IF NOT EXISTS(SELECT * FROM inserted) RETURN -- no rows inserted or updated
IF NOT EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted) RETURN -- no rows inserted, updated, or deleted

Now each record in inserted needs to be tested. It appears each can be from a different plant. I would check the logic in the function and avoid the function. It can be a performance killer to use a scalar function in a set based query. Perhaps something like this.

INSERT INTO Maintenance 
SELECT I.PlantId, I.MaintenanceDate, I.description, I.type  
FROM inserted I
LEFT JOIN Plant p
ON p.ID = I.PlantId
WHERE I.MaintenanceDate >= p.BirthDate;

For testing, you don't have an "inserted" table. You can use the real table. Don't forget to exclude the insert. Test it on a good sample of the data to insure you get the results you desire. I imagine the existing data has dates that will be selected.

SELECT Top 100 I.PlantId, I.MaintenanceDate, I.description, I.type  
FROM Maintenance I
LEFT JOIN Plant p
ON p.Id = I.PlantId
WHERE I.MaintenanceDate >= p.BirthDate;

Oracle: Trigger to insert values into another Table with one additional auto increment primary key column

There's nothing wrong with it - at least, it works properly on my 11gXE (which database version do you use?):

SQL> create table table1 (roll_number number, mytimestamp date);

Table created.

SQL> create sequence myseq;

Sequence created.

SQL> create table table2 (roll_number number, mytimestamp date, recordid number);

Table created.

SQL> create or replace trigger mytrigger
  2    after insert on table1
  3    for each row
  4  begin
  5    insert into table2 values
  6      (:new.roll_number, :new.mytimestamp, myseq.nextval);
  7  end;
  8  /

Trigger created.

SQL> insert into table1 values (1, sysdate);

1 row created.

SQL> select * From table2;

ROLL_NUMBER MYTIMESTAMP           RECORDID
----------- ------------------- ----------
          1 12.03.2021 07:14:42          1

SQL>

---

Alternatively, instead of VALUES use SELECT:

SQL> create or replace trigger mytrigger
  2    after insert on table1
  3    for each row
  4  begin
  5    insert into table2
  6      select :new.roll_number, :new.mytimestamp, myseq.nextval
  7      from dual;
  8  end;
  9  /

Trigger created.

SQL> insert into table1 values (2, sysdate);

1 row created.

SQL> select * From table2;

ROLL_NUMBER MYTIMESTAMP           RECORDID
----------- ------------------- ----------
          1 12.03.2021 07:14:42          1
          2 12.03.2021 07:16:00          2

SQL>

---

Related Topics