Reputation: 19
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
Reputation: 42741
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