CODEWITHSUNDEEP
mysqlsqltriggers
Mcam435
Mcam435

Reputation: 101

MySQL Trigger Creation. What's wrong with my trigger?

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

Answers (3)

peterm
peterm

Reputation: 92845

Besides properly using DELIMITER when creating a trigger you have at least two fundamental issues with your current code:

  1. In MySQL you can't use issue a DML statement (in your case 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.
  2. You can't arbitrarily reference a column (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

Vijay
Vijay

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

Imran
Imran

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

Related Questions