How to create trigger to take value from another table depends of value from first table-CodePudding

I'm lost with triggers in SQL

Have one main table with all data, second table is only for support of first table. I need a trigger to get value from second table depends of value from first table.

first_table

 id | name | surname | age | category |
 --------------------------------------
 1  | John | Duck    | 30  |          |

second_table

id | age | category |
---------------------
1  | 30  | adult    |
2  | 12  | kid      |

Trigger will autofill column category from second table to first table and use age.

I'm trying to add a trigger to the first table:

CREATE TRIGGER addCategory
AFTER UPDATE ON first_table FOR EACH ROW
BEGIN 
    DECLARE age int;
    SELECT category INTO table_one FROM table_two WHERE category = category;
    UPDATE table_one set category = NEW.category WHERE age = age;
END

Any tips?

result:

first_table

 id | name | surname | age | category |
 --------------------------------------
 1  | John | Duck    | 30  |  adult   |  <- value from second_table

CodePudding user response：

CREATE TRIGGER so_70683210
ACTIVE BEFORE UPDATE ON first_table POSITION 0
AS
BEGIN
  SELECT category
    FROM second_table
   WHERE second_table.age = NEW.age
    INTO NEW.category;
END

CodePudding user response：

when i change firebird to sqlite this works:

 CREATE TRIGGER tr
 AFTER INSERT ON first_table
 BEGIN
 UPDATE first_table SET category = (SELECT CATEGORY FROM second_table 
 WHERE second_table.AGE = first_table.AGE) WHERE CATEGORY = NEW.CATEGORY;
 END;