Trigger causes insert to say column doesn't exist.

[sKunKbad]

I have a table named d_records:

+--------------+-----------------------+------+-----+---------+----------------+
| Field        | Type                  | Null | Key | Default | Extra          |
+--------------+-----------------------+------+-----+---------+----------------+
| id           | int(10) unsigned      | NO   | PRI | NULL    | auto_increment |
| user_id      | int(10) unsigned      | NO   |     | NULL    |                |
| msg_datetime | datetime              | NO   |     | NULL    |                |
| msg          | text                  | YES  |     | NULL    |                |
| status       | enum('open','closed') | NO   |     | open    |                |
+--------------+-----------------------+------+-----+---------+----------------+

I have a trigger that is supposed to do an insert on another table, the h_records. On insert into d_records, I need to look up the user's full name, but that requires also looking up their user level in another table. So I tried this:

DELIMITER $$

DROP TRIGGER IF EXISTS cks_trigger ;
$$
CREATE TRIGGER cks__trigger
AFTER INSERT
   ON `d_records` FOR EACH ROW
BEGIN
   DECLARE uLevel tinyint(2);
   DECLARE fullName varchar(120);

   SELECT user_level
   FROM users
   WHERE user_id = NEW.user_id
   INTO uLevel;

   IF uLevel = 1 THEN
       SELECT CONCAT( p.first_name, " " , p.last_name)
       FROM basic_users p
       WHERE p.user_id = NEW.user_id
       INTO fullName;

   ELSEIF uLevel = 2 THEN
       SELECT CONCAT( p.first_name, " " , p.last_name)
       FROM employees p
       WHERE p.user_id = NEW.user_id
       INTO fullName;

   ELSEIF NEW.user_id = 123456 THEN
       SET fullName = 'skunk';

   END IF;

   IF fullName IS NOT NULL THEN
       INSERT INTO `h_records` ( user_id, full_name )
       VALUES ( NEW.user_id, fullName );
   END IF;

END;$$
DELIMITER ;

But when I do that, MySQL throws an error on insert into d_records, claiming that user_id column doesn't exist in d_records. As soon as I drop the trigger I can do an insert again.

 

The actual tables and trigger are more complex, but I've simplified the code to make the problem easier to see. What am I doing wrong with the trigger?

 

 

 

[Muddy_Funster]

I can't see anything obviously wrong with the code that's posted.  The only thing that I notice is that you're first select to get the user level should have the table identifier in there too:

 

WHERE users.user_id = NEW.user_id

But that would throw up a "column user_id in WHERE clause is ambiguous" error if it was causing a problem, not that the column user_id does not exist in d_records.

Maybe another member will see something I don't, but I suspect in your attempt to dumb things down for us you may have lost the syntax that is causing the actual issue.

[sKunKbad]

I ended up a solution that doesn't use a trigger, but would still like to know what was wrong. While "dumbed down", the proposed trigger was a good representation of the real trigger.

[requinix]

Behavior like this with a trigger? Ew. How about a stored procedure instead?

[sKunKbad]

Behavior like this with a trigger? Ew. How about a stored procedure instead?

It sounds like you are saying this is better suited for a procedure, but even so, what is wrong with my trigger? At this point, I would like the learning experience to know the answer.

[requinix]

But when I do that, MySQL throws an error on insert into d_records, claiming that user_id column doesn't exist in d_records.

Obviously it does exist. What is the exact error message?

 

The actual tables and trigger are more complex, but I've simplified the code to make the problem easier to see. What am I doing wrong with the trigger?

You've made sure that with those exact table structures and that exact trigger you're getting the same error, right? I think so but it's not clear.

 

It sounds like you are saying this is better suited for a procedure,

It sounds like you are turning a "insert a record into d_records" operation into a "create a record thing by inserting rows into d_records and h_records" operation. Or it could be that h_records is some sort of history or auditing table and you want inserts to be sort of mirrored? Or I don't know.

Either way I think (not knowing your situation) I would go with a stored procedure; triggers that cause side effects in other tables and not because of something like foreign key relations feel icky to me. Too automagical. It's entirely non-obvious that inserting into d_records will also insert into h_records, but that's also due to what triggers are so...

 

Eh, opinion.