Create a Trigger to Insert Records from a Table to Another One. Get Inserted Values in a Trigger

Create a trigger to insert records from a table to another one. Get inserted values in a trigger

Please try:

CREATE TRIGGER trigger_UpdateItemDetails ON tbl_PurchaseDetails
FOR INSERT AS
BEGIN

    INSERT INTO 
    tbl_ItemDetails
    (
        PurchaseID,
        Quantity,
        WarehouseID
    )
    SELECT 
        PurchaseID, 
        ItemQuantity, 
        WarehouseID
    FROM 
        INSERTED
END

and make sure that you are inserting a NOT NULL value to column PurchaseID of table tbl_PurchaseDetails.

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

Insert values in second table using triggers

You don't need the set identity_insert on if you are not attempting to insert values to the identity column. Also, your current insert statement, if you loose the set identity_insert, will simply inside a single null row for any insert statement completed successfully on the FridgeTemperture table.

When using triggers, you have access to the records effected by the statement that fired the trigger via the auto-generated tables called inserted and deleted.

I think you are after something like this:

CREATE TRIGGER [dbo].[FridgeTemperature_INSERT]
   ON  [dbo].[FridgeTemperture]
   AFTER  INSERT
AS 
BEGIN

   INSERT INTO MpSensors(fridge_temp)
   SELECT CAST(Fridgetemp as varchar(10)) 
   FROM inserted

END

Though I can't really see any benefit of storing the same value in two different places, and in two different data types.

Update

Following our conversation in the comments, you can simply use an update statement in the trigger instead of an insert statement:

UPDATE MpSensors
SET fridge_temp = (
    SELECT TOP 1 CAST(Fridgetemp as varchar(10)) 
    FROM inserted
    ORDER BY Id DESC
)

This should give you the latest record in case you have an insert statement that inserts more than a single record into the FridgeTemperture table in a single statement.

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>

SQL triggers - how to get auto incremented value from inserted data of one table, inserted into another?

If you want auto-generated columns, then use an after insert trigger:

CREATE TRIGGER `marketplace_to_stats`
AFTER INSERT ON `marketplace`
FOR EACH ROW
BEGIN
INSERT INTO stats (studentID, listingID, listingName, date, percent, listingViews, listingWatching, listingSales) 
    VALUES (NEW.studentID, NEW.listingID, NEW.listingName, curdate(), 0, 0, 0, 0);
END;

Although MySQL allows the columns in the insert to be qualified, I find that is unnecessary (and incompatible with other databases).

Create Trigger : update table column by inserting data into another table rows

You need to create two triggers one for insert and another for update.

CREATE TRIGGER `insert_trigger`
AFTER INSERT ON `data`
FOR EACH ROW

UPDATE info
SET gtotal = gtotal + new.total
WHERE id = new.info_id;

Here new.info_id will have the info_id value of newly insert record

CREATE TRIGGER `update_trigger`
AFTER UPDATE ON `data`
FOR EACH ROW

UPDATE info
SET gtotal = (gtotal - old.total) + new.total
WHERE id = new.info_id;

Here old.total will have the total value of record before updating. new.total is the total value after updation.

Since you haven't conveyed what to do after update I have added the logic of subtracting the total from old total and added the new total value. Change it as per your requirement.

You can also create these triggers using GUI in phpmyadmin. You have to select the triggers in the menu bar and add the trigger definition.

Trigger insert to another table if record inserted to table

Use inserted:

CREATE TRIGGER AutoInsert ON tblA AFTER INSERT AS
BEGIN
    INSERT INTO tblB (tblA_id, usrnm, InsertDate)
        select id, usrnm, InsertDate
        from inserted i;
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);

---

Related Topics