MySQL Conditional Trigger Delete – How to Implement

deleteMySQLtabletrigger

I have a few tables (6 to be exact) and I want to make a trigger that will delete a row from a table based on a value from the main table.

To elaborate,

Let's say I have a main table called "main" and there is a column there called "table_short" and that row contains either "S", "P", "A", "Z" or "K".
"S" points to the "Stanovi" table, "K" points to the "Kuce" table and so on, meaning the data in the "table_short" in the main table is associated with another table. In the main table there is also a "id_t" column, that points to a specific row inside the table associated in the "table_short" column.

Now, I want to make a trigger statement that would, upon deleting a row in the main table, delete the row number in the table associated in "table_short", with the id specified in "id_t".

How could I do this?
Hopefully someone understood what I mean.

I tried this but it gives me an error when trying to make a trigger.

BEGIN
 CASE old.sifra
  WHEN "S" THEN SET @table="stanovi";
  WHEN "K" THEN SET @table="kuce";
  WHEN "A" THEN SET @table="apratmani";
  WHEN "P" THEN SET @table="poslovne_zgrade";
  WHEN "Z" THEN SET @table="zemljista";
 END CASE;
 DELETE FROM @table WHERE id = old.id_t;
END

Best Answer

it can not check trigger condition at the moment of save trigger code:

DELETE FROM @table WHERE @table.id = old.id_t;

because table name at this moment unidentified

BEGIN
 CASE old.sifra
  WHEN "S" THEN DELETE FROM stanovi WHERE id = old.id_t;
  WHEN "K" THEN DELETE FROM kuce WHERE id = old.id_t;
  WHEN "A" THEN DELETE FROM apratmani WHERE id = old.id_t;
  WHEN "P" THEN DELETE FROM poslovne_zgrade WHERE id = old.id_t;
  WHEN "Z" THEN DELETE FROM zemljista WHERE id = old.id_t;
 END CASE;
END

will be work

Related Question