Postgresql – Passing Arguments from INSERT / UPDATE to Trigger

postgresql-9.2trigger

I need a help with a trigger, this trigger I need to pass a value that comes from the queue column in the asterisk.queue table.

CREATE TRIGGER gerarconf
AFTER INSERT OR UPDATE
ON asterisk.fila
FOR EACH ROW
EXECUTE PROCEDURE asterisk.registra_gerar_conf_fila('FILA','XXXXX');

In place of XXXX I need to pass the value of the queue column that came from the insert or update of the asterisk.queue table

Best Answer

Use NEW or OLD depending if you need the inserted or updated record.

Postgres documentation

When a PL/pgSQL function is called as a trigger, several special variables are created automatically in the top-level block. They are:

NEW
Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is unassigned in statement-level triggers and for DELETE operations.

OLD
Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is unassigned in statement-level triggers and for INSERT operations.

CREATE TRIGGER gerarconf
AFTER INSERT OR UPDATE
ON asterisk.fila
FOR EACH ROW
EXECUTE PROCEDURE asterisk.registra_gerar_conf_fila('FILA',NEW.column);

Related Solutions

After Update/Insert trigger sometimes fails to do its job

While testing with random data the above trigger worked perfectly for insertion. But as Mat said in the comment, while updating the DOCNUMBER column of INT_DOC the after update trigger failed to update that value in DOC_IDS table. Here is the output of the sample test:

SQL> INSERT INTO INT_DOC(DOCID,RECEIVEDDATE,SENDERFULLNAME,SUBJECT)
VALUES('BRTYRARA1',to_date('12.01.2015','DD.MM.YYYY'),'Ellie Johnson','YetAnotherSubject');   

1 row created.

SQL> select * from doc_ids;

no rows selected

SQL> update int_doc set confirmed=1,docnumber=1 where docid='BRTYRARA1';

1 row updated.

SQL> select * from doc_ids;

DOCID
----------
1

SQL> INSERT INTO INT_DOC(DOCID,RECEIVEDDATE,SENDERFULLNAME,SUBJECT,confirmed)
VALUES('AFFXRARA1',to_date('12.07.2015','DD.MM.YYYY'),'Jay Hons','YetAnotherSubject',1);

1 row created.

SQL> select * from doc_ids;

DOCID
----------
1

SQL> select docnumber from int_doc;

DOCNUMBER
----------
1


SQL> update int_doc set docnumber=2 where docid='AFFXRARA1';

1 row updated.

SQL> select * from doc_ids;

DOCID
----------
1
2

SQL> select docnumber from int_doc;

DOCNUMBER
----------
1
2

SQL> update INT_DOC set docnumber = 3 where docnumber = 1;

1 row updated.

SQL> select docnumber from int_doc;

DOCNUMBER
----------
3
2

SQL> select * from doc_ids;

DOCID
----------
1
2

Here i got the DOCNUMBER 3 in INT_DOC table but not in the DOC_IDS table.

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

After Update/Insert trigger sometimes fails to do its job

How to commit transaction on an after update event trigger

Related Question