zwwcn

Just another WordPress.com site

mysql trigger to track table change

Create a new table to store changes:

CREATE TABLE `data_log` (
	  `action` VARCHAR(40),
	  `changetime`   DATETIME,
	  `regionid` INT NOT NULL,
          `advisorid` INT NOT NULL,
          `agencyid` INT NOT NULL
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create trigger:

DELIMITER //
	CREATE TRIGGER add_region_data AFTER INSERT ON OP_REGION_ADVISOR_AGENCY
	FOR EACH ROW
	BEGIN
	  INSERT INTO data_log
	  VALUES('insert',NOW(),NEW.REGION_ID,NEW.ADVISOR_ID,NEW.AGENCY_ID);
	END;
        CREATE TRIGGER add_region_data AFTER DELETE ON OP_REGION_ADVISOR_AGENCY
        FOR EACH ROW
        BEGIN
          INSERT INTO data_log
          VALUES('insert',NOW(),OLD.REGION_ID,OLD.ADVISOR_ID,OLD.AGENCY_ID);
        END;
//
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: