Postgresql – I can’t figure out how to call a function with a trigger, keeps saying function doesn’t exist

functionspostgresqltrigger

CREATE OR REPLACE FUNCTION calculate(nr char) RETURNS char AS $$
    BEGIN

      RETURN (SELECT COUNT(*) FROM patient_notitie WHERE pn_patient_nr = nr);

    END;
$$ LANGUAGE plpgsql;

This worked:

SELECT * FROM calculate('100001');

This is where it went wrong, when we try to create the trigger to call the function:

CREATE TRIGGER trigger_calculate AFTER INSERT ON patient_notitie
EXECUTE PROCEDURE calculate(pn_patient_nr);

It says the function doesn't exist:

ERROR: function calculate() does not exist SQL state: 42883

Best Answer

Before creating trigger, you need to create a trigger function, which has to be (quoting the manual):

A user-supplied function that is declared as taking no arguments and returning type trigger

Like:

CREATE OR REPLACE FUNCTION calculate_t_f()
  RETURNS trigger AS
$$
BEGIN
...
-- do something
-- maybe with with calculate(char)...
...
END
$$ LANGUAGE plpgsql;

Then you can create your trigger with that trigger function (not your original function):

CREATE TRIGGER trigger_calculate
AFTER INSERT ON patient_notitie
EXECUTE PROCEDURE calculate_t_f();

Related Solutions

Oracle Functions – Does Select Clause Function Evaluate Columns Before Where Clause?

The references in the function's WHERE clause are comparing with the identically named columns in its SELECT clause, rather than the argument list. Try changing it as follows (note the addition of ARG_ to the argument names and the predicates in the WHERE clause):

create or replace function        func_get_Open_Days_2
   ( ARG_SUBMITTED_BY_PERSON_ID in number,
  ARG_SUBMISSION_DATE in date,
  ARG_FORM_VERSION_DATE_TIME in timestamp,
  ARG_FORM_ID in number
   )
RETURN number

IS
   n1 number:=0;
   n2 number:=0;

   cursor records is
      SELECT 
        SUBMITTED_BY_PERSON_ID,
        SUBMISSION_DATE,
        FORM_ID,
        FORM_VERSION_DATE_TIME,
        AUDIT_DATE_TIME,
        STATUS_DATE,
        SUBMISSION_STATUS_CODE,
        CREATE_USER_ID
      from form_state_audit_trail t
      where
        t.submitted_by_person_id = ARG_SUBMITTED_BY_PERSON_ID and
        t.form_id = ARG_FORM_ID and
        t.submission_date = ARG_SUBMISSION_DATE and
        t.form_version_date_time = ARG_FORM_VERSION_DATE_TIME and 
        t.submission_status_code NOT IN (3469, 3462);
      order by t.status_date asc;

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

Oracle Functions – Does Select Clause Function Evaluate Columns Before Where Clause?

How to commit transaction on an after update event trigger

Related Question