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.
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