CODEWITHSUNDEEP
postgresql
user2245713
user2245713

Reputation: 11

Is there any way to know what rows are changed in statement level trigger in postgreSQL?

I have to write a statement level trigger, in which I need to know how about a column if the values is changed within 24 hour. For example my table has these three column Col_1|Col2|Col3, I want to know if an update action has caused any changed in col_1 and want to store that value in a separate table. I don't want to use row level triggers as an update can cause changes to many rows in my table. I know in oracle we can use compound triggers and achieve the same thing with statement level trigger. But any suggestions for postgres users.

Thanks a lot!!

Upvotes: 1

Views: 365

Answers (1)

Denis de Bernardy
Denis de Bernardy

Reputation: 78581

On a row-level trigger, you can know, and in fact check for it before the trigger is event fired:

create trigger foo after update on foo
for each row
when (row(old.col1, old.col2, …) is distinct from row(new.col1, new.col2, …))
execute procedure foo_upd();

Statement-level triggers offer no such possibility, since they're fired on any relevant statement on the table.

However, in the odd and unusual cases where it's actually needed, you've two options:

  1. You can create a temporary table in a row-level trigger, fill it up when the row is changed as needed, and have a statement-level trigger take it from there.
  2. You can check if the txid of each row in the table. It's a hidden field that, if equal to the current transaction, implies that the row got touched. (Not necessarily updated; just touched.)

Upvotes: 1

Related Questions