Thesql trigger to insert/update date column from datetime column

MySQLmysql-5.1row-modification-timetrigger

probably simple question but can't get it to work:

I have a table with two datetime columns. In order to set up a date index and have simpler queries, i have added two date columns next to them.

Now, i would like to, whenever a row is inserted or updated, to extract the date part from the datetime columns, and insert or update the date columns.

Not very well versed with triggers, so pointers would be much appreciated.

server version: 5.1.49-3

Best Answer

It's unclear why you need this but assuming that the table looks something like

CREATE TABLE Table1
    (`id` int not null auto_increment primary key, 
     `dt1` datetime, 
     `dt2` datetime, 
     `d1`  date, 
     `d2`  date);

your triggers might look the following way

CREATE TRIGGER tg_bi_table1
BEFORE INSERT ON table1
FOR EACH ROW
  SET NEW.d1 = DATE(NEW.dt1), 
      NEW.d2 = DATE(NEW.dt2);

CREATE TRIGGER tg_bu_table1
BEFORE UPDATE ON table1
FOR EACH ROW
  SET NEW.d1 = DATE(NEW.dt1), 
      NEW.d2 = DATE(NEW.dt2);

Here is SQLFiddle demo

Related Solutions

Sql-server – Trigger Logic (from SQL 6.5 )

I'm not sure if this code is fully accurate for columns that have more than 1 row with the same value, but I suppose that's a different subject. What I see is that the only value the null counter is ever initialized to is 0. That means it will never have an effect on the addition. Putting on my dev hat, I could see the start of an unfinished concept.

If I needed to update a column to NULL, the where clause on line 9 wouldn't work. The test NULL = NULL is false. That means the join logic fails for update values of null. That being said, it appears the error handling for that was never implemented beyond having a count column to use.

PostgreSQL – Use Trigger to Synchronize JSON Columns on Insert or Update

Actually, this is all you need:

NEW := jsonb_populate_record(NEW, NEW.json);

Per documentation:

jsonb_populate_record(base anyelement, from_json jsonb)
Expands the object in from_json to a row whose columns match the record type defined by base (see note below).

What's not documented: The row provided as first argument retains all values that are not overwritten (no matching key in the json value). I see no reason why this should change, but you cannot fully rely on it unless it's documented.

One thing to note - you wrote:

Setting the column to NULL if the corresponding json field does not exist is fine.

This retains all values with no matching key in the JSON value, which should be even better.

If "undocumented" is too uncertain for you, use the hstore operator #= doing exactly the same.

NEW := (NEW #= hstore(jsonb_populate_record(NEW, NEW.json)));

The hstore module should be installed in most systems anyway. Instructions:

Assign to NEW by key in a Postgres trigger

Both solution can be derived from my answer that Daniel already referenced:

How to set value of composite variable field using dynamic SQL

Function code

CREATE OR REPLACE FUNCTION json_fn()
  RETURNS TRIGGER AS
$func$
BEGIN
   NEW := jsonb_populate_record(NEW, NEW.json); -- or hstore alternative
   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

Everything else in your setup looks right, just add a PK to testy:

CREATE TABLE testy (
    id   int   PRIMARY KEY REFERENCES testy_index
  , data jsonb NOT NULL
);

Tested in pg 9.4 and it works for me as advertised. I doubt that the PLv8 function can rival performance and simplicity.

Set other columns to NULL

As per comment:

CREATE OR REPLACE FUNCTION json_fn()
  RETURNS TRIGGER AS
$func$
DECLARE
  _j jsonb := NEW.json;  -- remember the json value
BEGIN
   NEW := jsonb_populate_record(NULL::testy, _j);
   NEW.json := _j;   -- reassign
   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

Obviously, you need to make sure that the column name or your jsonb column does not appear as key name in the JSON value. And i wouldn't use json as column name, since it's a data type name and that can get confusing.