I have two tables as follows:
Table A: Name, ID, value
Table B: ID, title
TableA.value is dependent on whether TableA.ID is present in TableB.ID.
I am trying to create a procedure and trigger so that whenever TableB is modified, the procedure is triggered to check if TableA.ID is in TableB.ID and sets TableA.value to 10.
I am using the following code and getting an error:
CREATE PROCEDURE update1
LANGUAGE SQL
AS $$
UPDATE tablA as a
SET a.value = 10
WHERE a.ID EXISTS ( SELECT b.ID
FROM tableB as b
WHERE a.ID = b.ID)
$$;
I am an SQL noob, and this is the first time I am trying to use a procedure.
UPDATE
I was able to create a procedure which does the job when I manually run it using CALL
However, it does not have a RETURNS TRIGGER block within it. Adding that returns the error Procedure cannot return triggers.
If I create a trigger as follows
CREATE TRIGGER b_trigger
AFTER UPDATE OR INSERT ON b.ID
FOR EACH STATEMENT
EXECUTE PROCEDURE update1();
However, this returns the following
ERROR: function columbia_deli.manager_discount must return type trigger
SQL state: 42P17
Try this using plpgsql Language:
Trigger Function:
CREATE OR REPLACE FUNCTION update1()
RETURNS trigger AS
$BODY$
begin
update "TableA" set value= 10 where id =new.id;
return NEW;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
Then create trigger on TableB for after update event
CREATE TRIGGER trig_tableb
AFTER UPDATE
ON "TableB"
FOR EACH ROW
EXECUTE PROCEDURE update1();