SQL 中的触发器让我不知所措
有一个包含所有数据的主表,第二个表仅用于支持第一个表。我需要一个触发器来从第二个表中获取值,这取决于第一个表中的值。
first_table
id | name | surname | age | category |
--------------------------------------
1 | John | Duck | 30 | |
second_table
id | age | category |
---------------------
1 | 30 | adult |
2 | 12 | kid |
触发器将把列category
从第二个表自动填充到第一个表并使用年龄。
我正试图在第一个表中添加一个触发器:
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
有什么建议吗?
结果:
first_table
id | name | surname | age | category |
--------------------------------------
1 | John | Duck | 30 | adult | <- value from second_table
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
当我将firebird更改为sqlite时,这起作用:
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;