How to Use Update Trigger to Update Another Table

How to use update trigger to update another table?

You don't reference table1 inside the trigger. Use the inserted pseudo table to get the "after" values. Also remember that an update can affect multiple rows.

So replace your current update statement with

UPDATE table2
SET    table2.annualyear = inserted.intannualyear
FROM   table2
       JOIN inserted
         ON table2.id = inserted.id  

SQL Trigger update another table

You will need to write an UPDATE trigger on table 1, to update table 2 accordingly.

Be aware: triggers in SQL Server are not called once per row that gets updated - they're called once per statement, and the internal "pseudo" tables Inserted and Deleted will contain multiple rows, so you need to take that into account when writing your trigger.

In your case, I'd write something like:

-- UPDATE trigger on "dbo.Table1"
CREATE TRIGGER Table1Updated
ON dbo.table1 FOR UPDATE
AS 
BEGIN
    -- update table2, using the same rows as were updated in table1
    UPDATE t2
    SET t2.Column = 'something'
    FROM dbo.Table2 t2
    INNER JOIN Inserted i ON t2.ID = i.ID        
END
GO

The trick is to use the Inserted pseudo table (which contains the new values after the UPDATE - it has the exact same structure as your table the trigger is written for - here dbo.Table1) in a set-based fashion - join that to your dbo.Table2 on some column that they have in common (an ID or something).

Create a trigger that updates another table when a new item is inserted

This should work:

CREATE TRIGGER `INSERT_TOTAL` AFTER INSERT ON `rating`
FOR EACH ROW INSERT INTO serviceprovider( spid, name, totalrating ) 
VALUES (

NEW.spid,  'name', (

SELECT AVG( spid ) 
FROM rating
)
)

With update:

CREATE TRIGGER `INSERT_TOTAL` AFTER INSERT ON  `rating` 
FOR EACH ROW 
UPDATE serviceprovider SET totalrating = ( SELECT AVG( spid ) FROM rating ) 
WHERE name =  'name'

Update column value of another table in After Insert Trigger

It's a safety measure from MySql to avoid deadlocks.

But you can avoid it by not directly selecting from the table that will be updated via the trigger.

For example by using variables.

CREATE TRIGGER TrgOrderItemAftIns
AFTER INSERT ON ORDERITEM
FOR EACH ROW
BEGIN
  INSERT INTO DEBUG_TABLE (MSG) VALUES (CONCAT('TrgOrderItemAftIns: ', NEW.ITEMID,',',NEW.PIECES));
  UPDATE ITEM SET PIECES = PIECES - NEW.PIECES WHERE ID = NEW.ITEMID;
END;

SET @OrderID = (select ID from TabOrder where Name = 'Order 1');
SET @ItemID  = (select ID from ITEM where Name = 'Item1');
INSERT INTO ORDERITEM (OrderID, ITEMID, PIECES) VALUES 
(@OrderID, @ItemID, 5);

SET @OrderID = (select ID from TabOrder where Name = 'Order 1');
SET @ItemID  = (select ID from ITEM where Name = 'Item2');
INSERT INTO ORDERITEM (OrderID, ITEMID, PIECES) VALUES 
(@OrderID, @ItemID, 1);

SET @OrderID = (select ID from TabOrder where Name = 'Order 2');
SET @ItemID  = (select ID from ITEM where Name = 'Item1');
INSERT INTO ORDERITEM (OrderID, ITEMID, PIECES) VALUES 
(@OrderID, @ItemID, 3);

Demo on db<>fiddle here

Trigger for update table after update records in another table?

Finally, I found perfect answer.

In the case of an UPDATE, the

Deleted table will contain the old values, while the Inserted table
contains the new values after Update.

Based on requirement, if we want to all fields in trigger we need to mention all fields in the table and write something like:

For records after UPDATE:

CREATE TRIGGER trg_ProdUpdate
ON dbo.Production 
AFTER UPDATE
AS
Begin

INSERT INTO dbo.Attribiute (ID, attributeValue, attributetype)

SELECT i.ID, i.prodrecord, i.prodvalue
FROM Production i
INNER JOIN INSERTED d ON i.ID = d.ID

For Records before UPDATE:

CREATE TRIGGER trg_ProdUpdate
ON dbo.Production 
AFTER UPDATE
AS
Begin

INSERT INTO dbo.Attribiute (ID, attributeValue, attributetype)

SELECT i.ID, i.prodrecord, i.prodvalue
FROM Attribiute i
INNER JOIN DELETED d ON i.ID = d.ID

We can join the Inserted and Deleted in to some History tables for audit by grabbing the Identical field (which is the same in both cases). The old value from the Deleted table, the new value from the Inserted table, and you store everything in the separate Table

For better understanding check this link Trigger insert old values- values that was updated

Use SQL Server trigger to update a column in another table after an insert

The other responses both fail to take into account that the Inserted pseudo table in a trigger will contain MULTIPLE rows at times - one should never ever select a single value - this will NOT WORK as expected if multiple rows are being inserted at once.

The trigger must be written in a set-based manner to handle this situation - which is really quite easy - try something like this:

CREATE TRIGGER trgActivityInsert
ON [dbo].[Activity]
FOR INSERT
AS
BEGIN
    UPDATE u
    SET Last_Activity_TimeStamp = GETDATE()
    FROM dbo.Users u
    INNER JOIN Inserted i ON u.User_Id = i.User_Id
END

With this, your trigger will work just fine whether you insert one, ten or a hundred rows at once, and all corresponding entries in the dbo.User table will be updated properly.

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);

How to create trigger when one rows is updated to update another table

I used below structure to create the trigger

create table table1 (id int, rgrupa varchar2(100));
create table table2 (id int, rgrupa varchar2(100));

insert into table1 (id,rgrupa) values (1,'A');
insert into table1 (id,rgrupa) values (2,'B');
insert into table1 (id,rgrupa) values (3,'C');

insert into table2 (id,rgrupa) values (1,'A');
insert into table2 (id,rgrupa) values (2,'B');

commit;

select * from table1;
select * from table2;

create or replace trigger sandeeptest after update of rgrupa on table1 for each row
begin
update table2 set rgrupa=:new.rgrupa where id=:new.id;
end;

update table1 set rgrupa='Aa' where rgrupa='A';

update table1 set rgrupa='Cc' where rgrupa='C';

---

Related Topics