creating a trigger based on time after one year in mysql My database has two tables: plans and status. plans has an attibute issue_date.

I want to create a trigger such that when the issue_date crosses one year, the attibute status in the status table should become inactive.
Triggers only occur when an action (INSERT, UPDATE or DELETE) occurs, but that's not the case here. 

You have a record which has "aged out". So, basically, it just sits there and does nothing unless you actively "look for it"! The server can only react to what it can perceive as a change - not an arbitrary record age!

You'll have to use events - say (in your case), fired once a day! Think of these as "temporal programmable triggers".

From the documentation, the first thing you do is (one of):

SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@global.event_scheduler = 1;


That sets the events daemon (or service on Windows), which are like a cron job or an at) in motion (see), so basically now, events will be fired. 

Then, from here do something like:

DELIMITER $$
CREATE 
    EVENT `entity_update` 
    ON SCHEDULE EVERY 1 DAY STARTS '2017-10-20 03:00:00'  -- do this at three in the morning! 
    DO BEGIN    

      -- This is where to put the SQL below - I forgot about 
      -- the non-standards-compliant dialect of SQL that 
      -- MySQL uses (see comparison with PostgreSQL below!).

    END */$$
DELIMITER ;


Be sure and test a couple of times before you "bed" your solution in!

The update SQL would look something like this (made a guess at table structure based on OP - see full DDL and DML below)

UPDATE status
SET status.status_plan = 'inactive'
WHERE status.status_plan_id IN
(
  SELECT del_id 
  FROM
  (
    SELECT s.status_plan_id AS del_id
    FROM plan p
    JOIN status s
    ON p.plan_id = s.status_plan_id
    WHERE p.plan_issue_date &lt; (DATE_SUB(NOW(), INTERVAL 1 YEAR)) -- plan_issue_date older than 1 year
    AND s.status_plan = 'active'  -- &lt;-- and only change those which are not already inactive!
  ) AS things_to_delete
);


Incredibly, for reasons outlined here, with MySQL, we programmers have to put in an artificial subSELECT into our UPDATE! Check out the much simpler and more elegant PostgreSQL syntax below:

UPDATE status
SET status_plan = 'inactive'
WHERE status.status_plan_id IN
(
  SELECT s.status_plan_id
  FROM plan p
  JOIN status s
  ON p.plan_id = s.status_plan_id
  WHERE p.plan_issue_date &lt; NOW() - INTERVAL '1 YEAR'
  AND s.status_plan = 'active'  -- &lt;-- only change those which are not already inactive!
);


(Full MySQL solution here, PostgreSQL here)

---------- MySQL DDL ---------

CREATE TABLE plan 
(
  plan_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  plan_issue_date DATE
);

CREATE TABLE status 
(
  status_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  status_plan_id INTEGER,
  status_plan VARCHAR (10),  -- no CHECK CONSTRAINTs in MySQL!
  CONSTRAINT status_plan_fk FOREIGN KEY (status_plan_id) REFERENCES plan (plan_id) 
);


Incredibly, MySQL does not support CHECK CONSTRAINTs on tables! You can get around this in three (clumsy IMHO) ways:


TRIGGERs - as outlined - here. Very clumsy - these should be for complex business logic and not for relatively simple criteria - Occam's Razor and all that!
A small lookup/reference table - here. Not a bad alternative, but not the most elegant - I prefer, if possible, to keep all definitions/constraints associated with a table in the CREATE TABLE statement!
VIEWs with CHECK OPTION - here. Quite why MySQL can provide VIEWs with such an option but not tables is beyond me!


&lt;rant over...&gt;

------------ DDL for both MySQL and PostgreSQL ------------

INSERT INTO status (status_id, status_plan_id, status_plan) VALUES (35, 1, 'active');
INSERT INTO status (status_id, status_plan_id, status_plan) VALUES (45, 2, 'active');
INSERT INTO status (status_id, status_plan_id, status_plan) VALUES (67, 3, 'active');
INSERT INTO status (status_id, status_plan_id, status_plan) VALUES (78, 4, 'active');
INSERT INTO status (status_id, status_plan_id, status_plan) VALUES (95, 5, 'inactive');


INSERT INTO status (status_id, status_plan_id, status_plan) VALUES (35, 1, 'active');
INSERT INTO status (status_id, status_plan_id, status_plan) VALUES (45, 2, 'active');
INSERT INTO status (status_id, status_plan_id, status_plan) VALUES (67, 3, 'active');
INSERT INTO status (status_id, status_plan_id, status_plan) VALUES (78, 4, 'active');
INSERT INTO status (status_id, status_plan_id, status_plan) VALUES (95, 5, 'inactive');


------------------ PostgreSQL DDL ----------------------------------

CREATE TABLE plan
(
  plan_id SERIAL, CONSTRAINT plan_pk PRIMARY KEY (plan_id),
  plan_issue_date DATE -- , CONSTRAINT plan_date_ct_gt_now CHECK (plan_issue_date &gt; NOW())
                       --   might be an idea for future inserts into plan? 
);
ALTER SEQUENCE plan_plan_id_seq RENAME TO plan_seq;

INSERT INTO plan (plan_issue_date) VALUES ('2017-10-15');
INSERT INTO plan (plan_issue_date) VALUES ('2016-10-15');  -- &lt;&lt; older than 1 year
INSERT INTO plan (plan_issue_date) VALUES ('2017-07-23');
INSERT INTO plan (plan_issue_date) VALUES ('2016-03-04');  -- &lt;&lt; older than 1 year
INSERT INTO plan (plan_issue_date) VALUES ('2016-02-25');  -- &lt;&lt;= older than 1 year and inactive in status table.

CREATE TABLE status 
(
  status_id SERIAL, 
  CONSTRAINT status_pk PRIMARY KEY (status_id),
  status_plan_id INTEGER, 
  status_plan VARCHAR(10) CHECK (status_plan IN ('active', 'inactive')),
  CONSTRAINT status_plan_fk FOREIGN KEY (status_plan_id) REFERENCES plan (plan_id)
);