zwwcn

Just another WordPress.com site

Monthly Archives: February 2015

Mysql:There is no ‘@’ registered. Error 1449 (ERR_NO_SUCH_USER)

I copied a database from remote server to my local and I get this exception when trying to execute a trigger.

Delete the triggers fix the problem.

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;
//