Create a Trigger That Updates a Column on One Table When a Column in Another Table Is Updated

Create a trigger that updates a column on one table when a column in another table is updated

How do i get the date and row id?

Assuming these are columns on your ORDER table called DELIVERY_DATE and ID your trigger should look something like this:

CREATE OR REPLACE TRIGGER your_trigger_name
    BEFORE UPDATE ON Order
    FOR EACH ROW 
BEGIN
   if :new.delivery_date != :old.delivery_date
   then
       UPDATE Delivery d
       set d.delivery_date = :new.delivery_date
       where d.order_id = :new.id;
    end if;
END;

Note the FOR EACH ROW clause: that is necessary to reference values from individual rows. I have used an IF construct to test whether to execute the UPDATE on Delivery. If you have no other logic in your trigger you could write it like this...

CREATE OR REPLACE TRIGGER your_trigger_name
    BEFORE UPDATE OF delivery_date ON Order
    FOR EACH ROW 
BEGIN
   UPDATE Delivery d
   set d.delivery_date = :new.delivery_date
   where d.order_id = :new.id;
END;

I have answered the question you asked but, as an aside, I will point out that your data model is sub-optimal. A properly normalized design would hold DELIVERY_DATE on only one table: DELIVERY seems teh logical place for it.

Create trigger that updates row depending on column value from other table

As far as I understood is you want to update table2 only when the state in table1 changed to 'approved' for a row and if a row is inserted in table1 trigger will insert the row in table2.

I have made some corrections to your code. Let me know if it is not what you wanted.

CREATE OR REPLACE TRIGGER INSERT_PAGE 
    BEFORE UPDATE OR INSERT
    ON TABLE1 
    FOR EACH ROW
    DECLARE 
    BEGIN
        IF INSERTING THEN 
                INSERT INTO TABLE2 (TBL1ID,STEP,PAGE) VALUES 
                (:NEW.TBL1ID,:NEW.STEP,15); 
      
        ELSIF UPDATING THEN 
          
            IF :NEW.STATE = 'APPROVED' THEN 
          
               UPDATE table2 t2 SET
               STATE = :NEW.STATE, PAGE=16, STEP1='TEXT123'      
               WHERE t2.TBL1ID = :OLD.TBL1ID;
            
            END IF;
          
        END IF; 
    END;

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

How to write a trigger that updates a table based on the action taken in another table?

Types of Triggers
There are two types of Triggers. After and Instead of Triggers. you have to use After triggers

1)After Triggers

These triggers are executed after an action such as Insert, Update or Delete is performed.

you have to use after trigger like below

After Insert

CREATE TRIGGER [dbo].[Customer_INSERT]
   ON [dbo].[Customers]
   AFTER INSERT
AS
BEGIN
   SET NOCOUNT ON;
   DECLARE @OrderItemsID;
  DECLARE @StroreentID;
   DECLARE @OrdersID;
   DECLARE @TermCondID;
   DECLARE @TradingID ;
   DECLARE @ItemSPCID;
   DECLARE @CatentryID;
   DECLARE @Partnum;  
   DECLARE @SysDateTime datetime = SYSDATETIME();
   DECLARE @LogActionType VARCHAR(1);

   SELECT @OrderItemsID = INSERTED.ORDERITEMS_ID;
   SELECT @StroreentID = INSERTED.STOREENT_ID;
   SELECT @OrdersID = INSERTED.ORDERS_ID;
   SELECT @TermCondID = INSERTED.TERMCOND_ID;
   SELECT @TradingID = INSERTED.TRADING_ID;
   SELECT @ItemSPCID = INSERTED.ITEMSPC_ID;
   SELECT @CatentryID = INSERTED.CATENTRY_ID;
   SELECT @Partnum = INSERTED.PARTNUM;

   @LogActionType = "1";

   FROM INSERTED

   INSERT INTO ORDERITEM_LOG
   VALUES (@OrderItemsID, @StroreentID, @OrdersID, @TermCondID, @TradingID, @ItemSPCID, @CatentryID, Partnum, LogActionType, @LogActionType)
END

After Update

 CREATE TRIGGER [dbo].[Customer_INSERT]
   ON [dbo].[Customers]
   AFTER UPDATE
AS
BEGIN
   SET NOCOUNT ON;
   DECLARE @OrderItemsID;
  DECLARE @StroreentID;
   DECLARE @OrdersID;
   DECLARE @TermCondID;
   DECLARE @TradingID ;
   DECLARE @ItemSPCID;
   DECLARE @CatentryID;
   DECLARE @Partnum;  
   DECLARE @SysDateTime datetime = SYSDATETIME();
   DECLARE @LogActionType VARCHAR(1);

   SELECT @OrderItemsID = INSERTED.ORDERITEMS_ID;
   SELECT @StroreentID = INSERTED.STOREENT_ID;
   SELECT @OrdersID = INSERTED.ORDERS_ID;
   SELECT @TermCondID = INSERTED.TERMCOND_ID;
   SELECT @TradingID = INSERTED.TRADING_ID;
   SELECT @ItemSPCID = INSERTED.ITEMSPC_ID;
   SELECT @CatentryID = INSERTED.CATENTRY_ID;
   SELECT @Partnum = INSERTED.PARTNUM;

   @LogActionType = "1";

   FROM INSERTED

   INSERT INTO ORDERITEM_LOG
   VALUES(@OrderItemsID, @StroreentID,@OrdersID,@TermCondID,@TradingID,@ItemSPCID,@CatentryID,Partnum,LogActionType, @LogActionType)
END

After DELETE

 CREATE TRIGGER [dbo].[Customer_INSERT]
   ON [dbo].[Customers]
   AFTER UPDATE
AS
BEGIN
   SET NOCOUNT ON;
   DECLARE @OrderItemsID;
   DECLARE @StroreentID;
   DECLARE @OrdersID;
   DECLARE @TermCondID;
   DECLARE @TradingID ;
   DECLARE @ItemSPCID;
   DECLARE @CatentryID;
   DECLARE @Partnum;  
   DECLARE @SysDateTime datetime = SYSDATETIME();
   DECLARE @LogActionType VARCHAR(1);

   SET @LogActionType = "1";
   SELECT @OrderItemsID = DELETED.ORDERITEMS_ID;
   SELECT @StroreentID = DELETED.STOREENT_ID;
   SELECT @OrdersID = DELETED.ORDERS_ID;
   SELECT @TermCondID = DELETED.TERMCOND_ID;
   SELECT @TradingID = DELETED.TRADING_ID;
   SELECT @ItemSPCID = DELETED.ITEMSPC_ID;
   SELECT @CatentryID = DELETED.CATENTRY_ID;
   SELECT @Partnum = DELETED.PARTNUM;

   INSERT INTO ORDERITEM_LOG
   VALUES(@OrderItemsID, @StroreentID,@OrdersID,@TermCondID,@TradingID,@ItemSPCID,@CatentryID,Partnum,LogActionType, @LogActionType)
END

Create a trigger that updates the columns of another table (Postgresql)

The procedure compiled after adding a semi-colon after the WHERE clause.

And it also had to return something.

But the update doesn't need to update each row in the target table whenever books gets updated.

CREATE OR REPLACE FUNCTION update_bookorder_availability() 
RETURNS TRIGGER
AS $$
BEGIN

  UPDATE bookOrder bo
  SET availability = CASE 
                     WHEN bo.required <= NEW.quantity THEN 'yes' 
                     ELSE 'no' 
                     END
  WHERE bo.bookName = NEW.bookName;
  
  RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER bookorder_availability_changes
  AFTER UPDATE
  ON books
  FOR EACH ROW
  EXECUTE PROCEDURE update_bookorder_availability();

-- before update
select * from books;
select * from bookOrder;


bookname     | quantity
:----------- | -------:
Harry Potter |       10
Twilight     |        5
Bible        |        8

orderid | bookname     | required | availability
------: | :----------- | -------: | :-----------
      1 | Harry Potter |        9 | yes         
      2 | Twilight     |        8 | yes         
      3 | Bible        |        8 | yes

update books set quantity = quantity;


3 rows affected

-- after update
select * from books;
select * from bookOrder;


bookname     | quantity
:----------- | -------:
Harry Potter |       10
Twilight     |        5
Bible        |        8

orderid | bookname     | required | availability
------: | :----------- | -------: | :-----------
      1 | Harry Potter |        9 | yes         
      2 | Twilight     |        8 | no          
      3 | Bible        |        8 | yes

Test on db<>fiddle here

Trigger to create or update records in another table

Even if you want to create a normalised verison of the kids table, you don't need to repeat the additional data, e.g. storing the same address in both kids and parents is redundant and will potentially cause issues. As a simple example, what if I run:

UPDATE dbo.Kids
SET Address = 'A new Address'
WHERE Name = 'John'
AND Surname = 'Adams';

How should this update the parent record? There are 3 children in this house, if it is possible for one to change address but not the other two, then the address only belongs on kids and not on parents. Or if the parents and the children both need an address, but there is no requirement that they live at the same address, then you might have an address column each, but it is not possible to provide any automated synchronicity. Or if (as I suspect) the parent address is what is relevent, then there is no need to also store it against each individual child, if you ever need the address for a child (or any other common field), just join back to the parent, e.g.

SELECT k.*, p.address, p.surname, p.sports, p.team 
FROM Kids AS k 
JOIN Parents AS p ON p.Id = k.ParentId;

This should hopefully cover off your third question. Either your two way trigger is redundant (if you don't duplicate data), or it is not possible/sensible due to combining multiple rows into a single row, potentially causing conflicts.

To answer your 1st question though about why a record is not being created for Taylor domer, it is because you effectively have the following pseudo code:

If any record already exists in kids
    -  then update the existing record
else
    - add a new record.

Since Samuel Domer already exists, then you only ever reach the update, and never do the insert. You don't really need the IF statement at all, and if you take my advice about not duplicating the data then you don't need the update either (as you are only updating fields common to both tables), so all you need is an insert

INSERT INTO dbo.kids(KidCode, Name, ParentId, join_date)
SELECT  kidCode = CONVERT(VARCHAR(32), HASHBYTES('SHA2_256', CONCAT(INSERTED.id, '-', TRIM(s.value))), 2),
        TRIMname = (value),
        parentID = INSERTED.id,
        join_date = GETDATE()
FROM    INSERTED
        CROSS APPLY string_split(INSERTED.kids, ',') AS s
WHERE   NOT EXISTS (SELECT 1  FROM dbo.Kids AS k WHERE k.ParentId = INSERTED.id AND k.name = TRIM(s.value));

You may also want to consider what happens if a child is removed from a parent, which could be handled as follows:

DELETE  k
FROM    dbo.Kids AS k
WHERE   EXISTS
(
    SELECT  1
    FROM    DELETED AS d
            CROSS APPLY string_split(d.kids, ',') AS s
    WHERE   d.id = k.ParentID
    AND     TRIM(s.value) = k.name
    AND     NOT EXISTS 
            (   SELECT 1 
                FROM    INSERTED AS i 
                        CROSS APPLY string_split(i.kids, ',') AS s2 
                WHERE   i.id = d.id 
                AND     TRIM(s2.value) = TRIM(s.value)
            )
);

Complete demo on db<>fiddle

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.

Create a Trigger That Updates a Column on One Table When a Column in Another Table Is Updated