CODEWITHSUNDEEP
mysqltriggers
sharonbetts
sharonbetts

Reputation: 19

mysql trigger insert field in separate table

When a record is inserted into general, I want the regno to be copied to conf_regno in conf.

I plan to do this with an AFTER INSERT trigger, but am unable to get it to work. My host is running MySQL v 5.5

This is my trigger - which errors out in phpMYadmin:

DELIMITER $$
DROP TRIGGER IF EXISTS sharonb9_titles.ins_regno;
CREATE TRIGGER ins_regno AFTER INSERT ON general
  FOR EACH ROW
  BEGIN
    INSERT INTO conf(conf_regno) VALUES(NEW.regno);
    END;
$$
DELIMITER;

Tables:

CREATE TABLE general (
id INT(4) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
regno VARCHAR(20) NOT NULL,
owner VARCHAR(50) NOT NULL,
dog VARCHAR(50) NOT NULL);

CREATE TABLE conf (
id INT(4) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
conf_regno VARCHAR(20) NOT NULL,
conf_CH_Y VARCHAR(4) NOT NULL DEFAULT 'xxxx',
conf_CH_V INT(2) UNSIGNED  NOT NULL DEFAULT 0,
conf_GCH_Y VARCHAR(4) NOT NULL DEFAULT 'xxxx',
conf_SN_Y VARCHAR(4) NOT NULL DEFAULT 'xxxx',
conf_SN_V INT(2) UNSIGNED  NOT NULL DEFAULT 0);

Can anyone help?

Upvotes: 1

Views: 37

Answers (1)

miken32
miken32

Reputation: 42743

You are setting the delimiter to $$ but then executing the DROP TRIGGER command with a ; at the end.

Anyway, with only one statement you can skip all the DELIMITER and BEGIN/END stuff anyway:

CREATE TRIGGER ins_regno AFTER INSERT ON general
  FOR EACH ROW INSERT INTO conf(conf_regno) VALUES(NEW.regno);

http://sqlfiddle.com/#!9/4c77ee/1

Upvotes: 1

Related Questions