0

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
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.