sKunKbad Posted January 19, 2016 Share Posted January 19, 2016 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? Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted January 21, 2016 Share Posted January 21, 2016 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. Quote Link to comment Share on other sites More sharing options...
sKunKbad Posted February 2, 2016 Author Share Posted February 2, 2016 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. Quote Link to comment Share on other sites More sharing options...
requinix Posted February 2, 2016 Share Posted February 2, 2016 Behavior like this with a trigger? Ew. How about a stored procedure instead? Quote Link to comment Share on other sites More sharing options...
sKunKbad Posted February 3, 2016 Author Share Posted February 3, 2016 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. Quote Link to comment Share on other sites More sharing options...
requinix Posted February 3, 2016 Share Posted February 3, 2016 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.