Dynamic evaluation of a table column within an insert before trigger
- by Tim Garver
HI All,
I have 3 tables, main, types and linked.
main has an id column and 32 type columns.
types has id, type
linked has id, main_id, type_id
I want to create an insert before trigger on the main table.
It needs to compare its 32 type columns to the values in the types table if the main table column has an 'X' for its value and insert the main_id and types_id into the linked table.
i have done a lot of searching, and it looks like a prepared statement would be the way to go, but i wanted to ask the experts.
The issue, is i dont want to write 32 IF statements, and even if i did, i need to query the types table to get the ID for that type, seems like a huge waist of resources.
Ideally i want to do this inside of my trigger:
BEGIN
DECLARE @types results_set -- (not sure if this is a valid type);
-- (iam sure my loop syntax is all wrong here)...
SET @types = (select * from types)
for i=0;i<types.records;i++ {
IF NEW.[i.type] = 'X' THEN
insert into linked (main_id,type_id) values (new.ID, i.id);
END IF;
}
END;
Anyway, This is what i was hoping to do, maybe there is a way to dynamically set the field name inside of a results loop, but i cant find a good example of this.
Thanks in advance
Tim
