SQL - date column automatically changing each time update table

SQL - date column automatically changing each time update table

If you want a date column to automatically update to the current date and time each time a row in the table is updated, you can use a database trigger or a special column property depending on the SQL database you are using.

Here are examples for different databases:

MySQL

In MySQL, you can use the ON UPDATE CURRENT_TIMESTAMP property when defining a TIMESTAMP or DATETIME column.

Example Table Creation

CREATE TABLE your_table ( id INT PRIMARY KEY, data VARCHAR(255), updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );

Explanation

  • TIMESTAMP DEFAULT CURRENT_TIMESTAMP: Sets the default value of the column to the current timestamp when the row is created.
  • ON UPDATE CURRENT_TIMESTAMP: Updates the timestamp to the current timestamp whenever the row is updated.

PostgreSQL

In PostgreSQL, you can use a trigger to automatically update the timestamp.

Example Trigger Creation

  1. Create the function that will be called by the trigger:
CREATE OR REPLACE FUNCTION update_updated_at_column() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql;
  1. Create the trigger that will call the function:
CREATE TRIGGER set_timestamp BEFORE UPDATE ON your_table FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

Example Table Creation

CREATE TABLE your_table ( id SERIAL PRIMARY KEY, data VARCHAR(255), updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

SQL Server

In SQL Server, you can use a trigger to achieve this.

Example Trigger Creation

  1. Create the trigger:
CREATE TRIGGER trgUpdateTimestamp ON your_table AFTER UPDATE AS BEGIN SET NOCOUNT ON; UPDATE your_table SET updated_at = CURRENT_TIMESTAMP FROM your_table t INNER JOIN inserted i ON t.id = i.id; END;

Example Table Creation

CREATE TABLE your_table ( id INT PRIMARY KEY, data NVARCHAR(255), updated_at DATETIME DEFAULT GETDATE() );

Oracle

In Oracle, you can use a trigger similarly to PostgreSQL and SQL Server.

Example Trigger Creation

  1. Create the trigger:
CREATE OR REPLACE TRIGGER trgUpdateTimestamp BEFORE UPDATE ON your_table FOR EACH ROW BEGIN :NEW.updated_at := SYSDATE; END;

Example Table Creation

CREATE TABLE your_table ( id NUMBER PRIMARY KEY, data VARCHAR2(255), updated_at DATE DEFAULT SYSDATE );

Summary

  • MySQL: Use TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.
  • PostgreSQL: Use a trigger to update the timestamp.
  • SQL Server: Use a trigger to update the timestamp.
  • Oracle: Use a trigger to update the timestamp.

These methods ensure that the updated_at column is automatically set to the current date and time whenever a row is updated in the table.

Examples

  1. SQL trigger update timestamp on column change

    Description: Implement a SQL trigger that updates a timestamp column whenever another column in the same table is updated.

    CREATE TRIGGER update_timestamp AFTER UPDATE ON your_table FOR EACH ROW BEGIN IF NEW.column_name <> OLD.column_name THEN UPDATE your_table SET timestamp_column = CURRENT_TIMESTAMP WHERE primary_key_column = NEW.primary_key_value; END IF; END;

    Replace your_table, column_name, timestamp_column, and primary_key_column with your actual table and column names.

  2. SQL prevent date column auto-update on update

    Description: Prevent a specific date column from automatically updating whenever the table is updated.

    CREATE TRIGGER prevent_date_update BEFORE UPDATE ON your_table FOR EACH ROW BEGIN IF NEW.date_column <> OLD.date_column THEN SET NEW.date_column = OLD.date_column; END IF; END;

    Replace your_table and date_column with your actual table and column names.

  3. SQL update timestamp column only on certain conditions

    Description: Update a timestamp column conditionally based on specific criteria during an update operation.

    CREATE TRIGGER conditional_timestamp_update AFTER UPDATE ON your_table FOR EACH ROW BEGIN IF NEW.column_to_check = 'condition' THEN UPDATE your_table SET timestamp_column = CURRENT_TIMESTAMP WHERE primary_key_column = NEW.primary_key_value; END IF; END;

    Adjust column_to_check, condition, your_table, timestamp_column, and primary_key_column as per your requirements.

  4. SQL prevent automatic update of timestamp column

    Description: Prevent a timestamp column from being automatically updated on every table update.

    CREATE TRIGGER prevent_timestamp_update BEFORE UPDATE ON your_table FOR EACH ROW BEGIN SET NEW.timestamp_column = OLD.timestamp_column; END;

    Replace your_table and timestamp_column with your actual table and column names.

  5. SQL trigger update column based on previous value

    Description: Create a trigger that updates a column based on its previous value when updated.

    CREATE TRIGGER update_based_on_previous_value AFTER UPDATE ON your_table FOR EACH ROW BEGIN UPDATE your_table SET column_to_update = OLD.column_to_update + 1 WHERE primary_key_column = NEW.primary_key_value; END;

    Modify your_table, column_to_update, and primary_key_column accordingly.

  6. SQL update column on insert and prevent change on update

    Description: Ensure a column is updated only on insert and prevents its modification on subsequent updates.

    CREATE TRIGGER insert_only_update_prevent BEFORE UPDATE ON your_table FOR EACH ROW BEGIN SET NEW.column_to_update = OLD.column_to_update; END;

    Adjust your_table and column_to_update as needed.

  7. SQL trigger update date column only if different

    Description: Update a date column only if the new value is different from the old value.

    CREATE TRIGGER update_if_different_date BEFORE UPDATE ON your_table FOR EACH ROW BEGIN IF NEW.date_column <> OLD.date_column THEN SET NEW.date_column = CURRENT_TIMESTAMP; END IF; END;

    Replace your_table and date_column with your actual table and column names.

  8. SQL update timestamp column without changing date

    Description: Update a timestamp column without altering its associated date value.

    CREATE TRIGGER update_timestamp_without_date_change AFTER UPDATE ON your_table FOR EACH ROW BEGIN UPDATE your_table SET timestamp_column = CONCAT(CAST(OLD.timestamp_column AS DATE), ' ', CAST(CURRENT_TIME AS CHAR)) WHERE primary_key_column = NEW.primary_key_value; END;

    Modify your_table, timestamp_column, and primary_key_column as necessary.

  9. SQL update column based on another column change

    Description: Update a column whenever another specified column in the same row changes.

    CREATE TRIGGER update_based_on_other_column AFTER UPDATE ON your_table FOR EACH ROW BEGIN IF NEW.trigger_column <> OLD.trigger_column THEN UPDATE your_table SET update_column = NEW.updated_value WHERE primary_key_column = NEW.primary_key_value; END IF; END;

    Adjust your_table, trigger_column, update_column, and primary_key_column to fit your scenario.

  10. SQL disable auto-update on specific column

    Description: Disable automatic updates to a specific column when other columns are updated.

    CREATE TRIGGER disable_auto_update_specific_column BEFORE UPDATE ON your_table FOR EACH ROW BEGIN IF NEW.column_to_update <> OLD.column_to_update THEN SET NEW.column_to_update = OLD.column_to_update; END IF; END;

    Replace your_table and column_to_update with your actual table and column names.

More Tags

acl openxml-sdk awk self-signed swift4 payara iterable dpkt sections jhipster

More Programming Questions

More Dog Calculators

More Chemical thermodynamics Calculators

More Housing Building Calculators

More Everyday Utility Calculators