Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to use 'for statement' triggers in postgres?

Tags:

sql

postgresql

if we write a trigger FOR STATEMENT like below how can we access only updated rows in trigger procedure/function

CREATE FUNCTION func1()
RETURNS trigger AS $$
BEGIN
    --access only updated/inserted rows here???
    RETURN null;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trig1
AFTER UPDATE OR DELETE OR INSERT
ON tbl1 FOR STATEMENT
EXECUTE PROCEDURE func1();

I mean when there are multiple rows updated once like below

update tbl1 set col1=1 where col2 in (2,3,4,5,6)
like image 532
RAFIQ Avatar asked Dec 10 '25 09:12

RAFIQ


1 Answers

Now, it is possible to use an identifier for the table of inserted records in pg 10 :

CREATE TRIGGER transfer_insert
AFTER INSERT ON transfer
REFERENCING NEW TABLE AS inserted
FOR EACH STATEMENT
EXECUTE PROCEDURE check_transfer_balances_to_zero();

Extracted from pg 10 doc : https://www.postgresql.org/docs/10/static/sql-createtrigger.html

like image 53
jojo2000 Avatar answered Dec 12 '25 04:12

jojo2000



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!