Is it possible to derive table name as "NEW.<TABLE FEILD>" in MySql Trigger?

user_1273407 0
Viewed 37

I have two tables

COMPANYINFO(SYMBOL, PRICE, PARENTTABLE)

WATCHLIST(SYMBOL, WATCHVALUE)

Whenever the PRICE in COMPANYINFO is updated I need to get maximum value from from the table in PARENTTABLE field of COMPANYINFO and update WATCHVALUE of WATCHLIST table.

The trigger I defined in COMPANYINFO is as follows

BEGIN UPDATE WATCHLIST SET WATCHVALUE= (SELECT MAX(WATCHVALUE) FROM NEW.PARENTTABLE) WHERE SYMBOL = NEW.SYMBOL; END

The database is not taking the NEW.PARENTTABLE value rather trying to take PARENTTABLE table of NEW database. Is it possible to make the trigger take the field value. I cannot use dynamic query as it is not allowed in MySql trigger.

database-trigger mysql
1 Answers

No, you can't use a value as the name of a table. The name of a table must be fixed at the time the SQL statement is parsed.

You could use a CASE statement for each possible parent table name, and query the max from the respective table.

BEGIN
DECLARE MAX_VALUE NUMERIC(9,2);

CASE NEW.PARENTTABLE
 WHEN 'table1' THEN SELECT MAX(WATCHVALUE) INTO MAX_VALUE FROM table1;
 WHEN 'table2' THEN SELECT MAX(WATCHVALUE) INTO MAX_VALUE FROM table2;
 ...others...
END CASE;

UPDATE WATCHLIST SET WATCHVALUE = MAX_VALUE WHERE SYMBOL = NEW.SYMBOL;
END

I think your data model may not be designed well if you need to reference a variety of tables. Sounds fishy.

Re your comment:

You need to not have a different table for each symbol. That's the root cause of the fishy pattern in your database design. I called it "Metadata Tribbles" in my book SQL Antipatterns.

Bill_Karwin_208600
Related