Reputation: 101
I'm trying to create a trigger, however I keep getting back a syntax error.
Here's the statement:
DELIMITER $$
CREATE TRIGGER `swtalentbank`.`after_candidate_insert`
AFTER INSERT ON `Candidates` FOR EACH ROW
BEGIN
INSERT INTO useradmin (username, talent)
VALUES (NEW.email, 1);
UPDATE `Candidates` SET UserID = useradmin.userid where useradmin.username = NEW.email;
END
DELIMITER ;
I have a registration form on my site. When a person registers it populates the Candidates table with their profile information.
In the Candidates table, there are various fields, two of them being 'email'
and 'UserID'
.
UserID is also the PK in 'useradmin'
, so I'm linking the two up.
So when a user registers, I need to insert a record into 'useradmin
' with the email address that's just been used to register, and then update the 'Candidates' table, with UserID that's just been created in 'useradmin
'.
I hope this makes sense?
NB. I am changing the delimiter before running the statement.
Upvotes: 0
Views: 60
Reputation: 92835
Besides properly using DELIMITER
when creating a trigger you have at least two fundamental issues with your current code:
UPDATE
) against a table (candidates
) on which you defined a trigger (also candidates
). Your only option is to use BEFORE
trigger and set a value of userid
column of a row being inserted to a proper value.useradmin.userid
) of a table out of the context like you did in your UPDATE
. You didn't joined useradmin
table or used it in a subquery.That being said and assuming that userid
in useradmin
table is an auto_increment
column your trigger might look like this
DELIMITER $$
CREATE TRIGGER after_candidate_insert
BEFORE INSERT ON candidates
FOR EACH ROW
BEGIN
INSERT INTO useradmin (`username`, `talent`) VALUES (NEW.email, 1);
SET NEW.userid = LAST_INSERT_ID();
END$$
DELIMITER ;
Here is SQLFiddle demo
Upvotes: 1
Reputation: 8461
try out this...
DELIMITER $$
CREATE TRIGGER `swtalentbank`.`after_candidate_insert`
AFTER INSERT ON `Candidates` FOR EACH ROW
BEGIN
INSERT INTO useradmin (username, talent)
VALUES (NEW.email, 1);
UPDATE `Candidates` SET UserID = useradmin.userid where useradmin.username = NEW.email;
END $$
DELIMITER ;
Upvotes: 0
Reputation: 3072
You should use semicolon after end your insert query.
You can use INSERT ... ON DUPLICATE KEY UPDATE syntax for your purpose
Upvotes: 0