PL/SQL 触发器从一个表上的 INSERT 更新另一个表 [英] PL/SQL Trigger to update another table from INSERT on one table
问题描述
我正在使用 SQL 和 Oracle 数据库,需要一些帮助 - 我很难理解触发器.
当我在表 A 中插入一行时,我需要一个触发器,以便它更新表 B 上的一行:特别是主键与刚刚添加到表 A 的行的相应外键匹配的行.>
例如,表 A 中的列 X 是引用表 B 中的列 Y(主键)的外键.当我向表 A 添加一行时,我需要表 B 的 Z 列在 X 列 = Y 列的行中为其数值添加 1.
基于我对触发器的有限理解,这是我迄今为止在 SQL 中得到的内容,以防万一(我意识到它不是很好,将其视为伪代码):
创建或替换触发器 test_trig在 tableA 上插入或更新之后每行开始更新表BSET columnZ = columnZ + 1WHERE tableA.columnX = tableB.columnY;END test_trig;/
谢谢
试试这个:
语法将是
创建或替换触发器 test_trig在 tableA 上插入或更新之后每行开始更新表BSET columnZ = columnZ + 1WHERE tableB.columnX = :NEW.columnX;END test_trig;/
:new.columnX 引用表 A columnX.
I'm using SQL and an Oracle database and need some help - triggers are something I struggle to understand.
I need a trigger for when I insert a row into Table A so that it updates a row on Table B: specifically the row whose primary key matches the corresponding foreign key of the row that just been added to Table A.
So for example column X in Table A is a foreign key that references column Y in Table B (the primary key). When I add a row to Table A I need column Z of Table B to have 1 added to its numeric value in the row where column X = column Y.
This is what I have been able to get so far in SQL based on my limited understanding of triggers, in case it helps (I realise it's not very good, treat it as pseudocode):
CREATE OR REPLACE TRIGGER test_trig
AFTER INSERT OR UPDATE ON tableA
FOR EACH ROW
BEGIN
UPDATE tableB
SET columnZ = columnZ + 1
WHERE tableA.columnX = tableB.columnY;
END test_trig;
/
Thanks
try this :
Syntax will be
CREATE OR REPLACE TRIGGER test_trig
AFTER INSERT OR UPDATE ON tableA
FOR EACH ROW
BEGIN
UPDATE tableB
SET columnZ = columnZ + 1
WHERE tableB.columnX = :NEW.columnX;
END test_trig;
/
:new.columnX reference the table A columnX.
这篇关于PL/SQL 触发器从一个表上的 INSERT 更新另一个表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!