PL/SQL Trigger to update another table from INSERT on one table

Question

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

Answer 1

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.