Jump to content

Trigger causes insert to say column doesn't exist.


sKunKbad

Recommended Posts

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?

 

 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 2 weeks later...

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.