Why is the insert statement in the trigger not inserting?

user_11923945 0
Viewed 53

So what I'm trying to do is, if I add a row in the "angajati"(employees) table, I want the id of said employee to also appear in the "ospatari"(waiters) table. "ospatari" table only has one attribute, the id, and there's a foreign key constraint between ospatari.id and angajati.id.

delimiter //
CREATE TRIGGER InsertOspatar BEFORE INSERT ON angajati
FOR EACH ROW
BEGIN
IF (SELECT rol FROM Inserted) = "Ospatar"
THEN
INSERT INTO ospatari VALUES(Inserted.id);
END IF;
END//

While this code doesn't give me any errors, once I insert a row in "angajati", a row with the same id does NOT appear in "ospatari".

I would really appreciate some help, Thanks!

sql-insert triggers mysql sql
1 Answers

To access the inserted values in MySQL use the new pseudo record, not inserted (inserted would be used in SQL Server but you'd use it wrongly as it is a pseudo table, not necessarily containing only one record as triggers are fired per statement, not per row there.)

CREATE TRIGGER insertospatar
               BEFORE INSERT
               ON angajati
               FOR EACH ROW
BEGIN
  IF new.rol = 'Ospatar' THEN
    INSERT INTO ospatari
                (target_column)
                VALUES (new.id);
  END IF;
END;

db<>fiddle

Also don't get used to use double quotes for string or date literals. Yes, sadly MySQL accepts that, but in SQL double quotes are usually for identifiers, such as column names. Should you ever use another DBMS (or future MySQL versions become more sane about this) you'll likely get an "invalid object name" error. Always use single quotes for string or date literals.

And always list the targeted columns in an INSERT statement. Otherwise values might end up in wrong columns or existing INSERTs break when a new column is added to the table.

user_96614240
Related