Postgresql – How to pass a newly INSERTed row to a function

functionspostgresqltrigger

I have following tables:

 table1:       table2:
| uid | did | | uid | did |
|-----+-----| |-----+-----|
| 1   | 2   | | '1' | 2   |
| 4   | 2   | | '4' | 2   |
| 2   | 3   | | '2' | 3   |
| 1   | 8   | | '1' | 8   | 
...

I need to create a trigger that would copy every inserted row from table1 to table2, which is on a different schema.

So I made the following function:

CREATE OR REPLACE FUNCTION schema1."clone to table2"(uid numeric, did numeric)
 RETURNS void
 LANGUAGE sql
AS $function$
BEGIN;
        INSERT INTO schema2."table2" ("uid", "did")
        VALUES(uid::text, did);
END;
$function$
;
;

I have no clue how to pass this function properly to a trigger. After a bit of searching I found this:

CREATE TRIGGER on_insert
AFTER INSERT 
ON schema1.table1
FOR EACH ROW
EXECUTE PROCEDURE schema1."clone to table2"(new."uid", new."did")

However it throws a "syntax error near ".", Position: 127". So I suppose I got it completely wrong.

I know views are perfect solution for my problem but I unfortunately can not use them in my scenario. I just need these two tables to be always identical, with a difference that table2 needs to have uid as text type.

Best Answer

A trigger requires a trigger function. And those can only be written in PL/pgSQL.

So you need something like this:

CREATE OR REPLACE FUNCTION schema1.clone_to_table2()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
  INSERT INTO schema2."table2" ("uid, "did")
  VALUES(new.uid::text, new.did);
  return new;
END;
$function$
;

BEGIN does not require a ; at the end.

And then a trigger definition to go with that

CREATE TRIGGER on_insert
  AFTER INSERT ON schema1.table1
  FOR EACH ROW
  EXECUTE PROCEDURE schema1.clone_to_table2();

Related Solutions

Trigger or check constraint insert values based upon existence of others

If you query the table you're inserting into inside a trigger on that same table, you are likely to get "ORA-04091: table name is mutating, trigger/function may not see it." error. Check constraints cannot contain subqueries. Therefore your best option is to encapsulate logic for inserting data into a stored procedure and use that procedure instead of direct inserts.

I can assist you with writing the procedure, however, it seems to me that you have a logical flaw in your data model. Your example data shows that my_id is not a unique key for the table but rather that many rows may share the same value for my_id. So if one row with my_id = 1 has field_2 set to some value and for some other row field_2 is null, what is to be done? Don't you have a primary key in your table?

Anyways, if you want literally what you wrote in your post, you could do with something like

create or replace procedure my_practice_table_insert(p_my_id in number, p_field_2 in varchar2, p_field_3 in varchar2)
is
    l_cnt number;
begin
    if p_field_2 is null then
        select count(*) into l_cnt from my_practice_table where my_id = p_my_id and field_2 is not null;
    else
        select count(*) into l_cnt from my_practice_table where my_id = p_my_id and field_2 is null;
    end if;

    if l_cnt = 0 then
        insert into my_practice_table(my_id, field_2, field_3) values(p_my_id, p_field_2, p_field_3);
    end if;
end;

I didn't actually try out the procedure so it may have slight errors, but that's the general form it could take.

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

Trigger or check constraint insert values based upon existence of others

How to commit transaction on an after update event trigger

Related Question