Automatic setting of the last modified date for orders

Description

This trigger automatically updates the last_modified_date attribute whenever an existing order is changed.

The application does not need to manually provide the modification timestamp. The database automatically records the exact time when the order was modified.

Tables involved

• Order

Type of trigger

• BEFORE UPDATE

Activated on the Order table.

SQL Code

CREATE OR REPLACE FUNCTION update_order_modified_date() 
RETURNS TRIGGER 
LANGUAGE plpgsql 
AS $$ 
BEGIN 
    NEW.last_modified_date := CURRENT_TIMESTAMP; 
    RETURN NEW;
END; 
$$; 

CREATE TRIGGER trg_update_order_modified_date 
BEFORE UPDATE 
ON "order" 
FOR EACH ROW 
EXECUTE FUNCTION update_order_modified_date();

Logic explanation

Whenever an order is updated, the trigger:

1. Detects the update operation. 2. Gets the current timestamp using CURRENT_TIMESTAMP. 3. Stores the timestamp in NEW.last_modified_date. 4. Allows the updated order to be saved.

Reason for trigger

This trigger is useful because:

• t automatically tracks order modifications.
• It prevents incorrect or missing modification timestamps.
• It removes the responsibility from the application layer.
• It provides a reliable history of when an order was last changed.