Postgresql – Trigger function does not exist, but I am pretty sure it does

functionsplpgsqlpostgresqlstored-procedurestrigger

I have a procedure:

create or replace procedure pro_update_last_read(in input_sensor_id integer, in read_time timestamp)
as
$$
begin
    update sensor
    set last_read = read_time
    where sensor_id = input_sensor_id;
end;
$$ language plpgsql;

And a trigger that calls it:

create trigger tri_last_read
    after insert or update of report_time
    on report
execute procedure pro_update_last_read(sensor_id, report_time);

However, when creating the trigger, I get the error message:

 [42883] ERROR: function pro_update_last_read() does not exist

Why is this error happening?

Best Answer

You must use a function, not a procedure:

CREATE FUNCTION pro_update_last_read() RETURNS trigger ...

The trigger must be defined FOR EACH ROW, and you cannot pass columns to the trigger function.

The way you access the columns in the trigger function is via the NEW variable: NEW.sensor_id and NEW.report_time.

Related Solutions

Error calling stored procedure from trigger in Oracle Database 11g

Your trigger doesn't need the CALL keyword.

create or replace trigger TRYTABLE_BEF_UPD_ROW
before update or insert on TryTable
for each row
begin
    TryJavaHelperRun(:new.arg1, :new.arg2);
end;
/

Generally, you should never use CALL in a PL/SQL block-- just execute the procedure. I assume that CALL is some ancient leftover syntactic remnant from some ancient version of Oracle. It allows you to replace the entire PL/SQL block that is the trigger body with a single CALL statement, i.e.

create or replace trigger TRYTABLE_BEF_UPD_ROW
  before update or insert on TryTable
  for each row
  call TryJavaHelperRun(:new.arg1, :new.arg2)
/

Personally, I've never come across a case where there was any benefit to ditching the "normal" PL/SQL syntax in favor of using CALL in a trigger.

This assumes that you can call the TryJavaHelperRun procedure successfully in general.

How to commit transaction on an after update event trigger

Why do you expect B to be updated, when you inserted a row with a different id? Actually, your INSERT ALL does not insert anything into B.

The above works fine after fixing your trigger and sample data:

create or replace trigger updB  
after update on B  
for each row  
declare  
v varchar2(5);  
begin  
  v := :new.val;
end;  
/

This is how you reference the new val in the trigger, not with a select. Your original trigger would run into the infamous ORA-04091 "table is mutating" error.

SQL> insert all into A values ('a', 'false') into b values ('a', 'false') select * from dual;

2 rows created.

SQL> commit;

Commit complete.

SQL> update A set val = 'true';
Cek cek: false

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from a;

ID         VAL
---------- ----------
a          true

SQL> select * from b;

ID         VAL
---------- ----------
a          true

For the output:

Your updA trigger runs in an autonomous transaction. When you select val from A, your original update is still not finished (commited), you will see the old value. Fixing your trigger for A:

create or replace trigger updA  
after update on A  
for each row  
declare  
pragma autonomous_transaction;  
cek varchar2(5);  
begin  
commit;  
  cek := :new.val;  
dbms_output.put_line('Cek cek: ' || cek);  
update B set val = :new.val where id = :new.id;  
commit;  
end;  
/

Best Answer

Related Solutions

Error calling stored procedure from trigger in Oracle Database 11g

How to commit transaction on an after update event trigger

Related Question