On Fri, 2021-02-05 at 10:57 +0530, Jagmohan Kaintura wrote:
> When we do an implementation from Oracle to PostgreSQL in trigger for clause like :
> updating(column_name) ==> (TG_OP='UPDATE' and OLD.column_name IS DISTINCT FROM NEW.column_name)
> But this condition would mostly not be sufficient when we are updating any column and with similar value somehow.
> Like : column_name OLD values is 2 and New values updates is 2.
>
> In PG it would become (TG_OP="UPDATE' and 2 is DISTINCT FROM 2), Overall it becomes FALSE , so from conversion from
ORACLEto PostgreSQL it doesn't give a clear picture for exact implementation for
> these UPDATING clause.
>
> Now why I brought up this as I got into one of the implementations which made me crazy to implement.
>
> Scenario :
> Column c3 is right not updating to the same value of column, for some bypass implementation.
>
> update table
> c1 = 'abc',
> c2 ='xyz',
> c3=c3;
>
>
> Inside trigger we have implementation like :
>
> Oracle:
> --THis block is being placed at the start of Trigger to bypass all other trigger blocks whenever we are updating
columnc3 , along with other columns.
> IF UPDATING('C3') THEN
> RETURN;
> END IF;
>
> If this column c3 is not placed in the update clause, then trigger would go through and execute other statements.
>
> PostgreSQL:
> We use the same method as :
> IF TC_OP='UPDATE' and OLD.c3 is DISTINCT FROM NEW.C3 THEN
> RETURN NEW;
> END IF;
There is no way to determine inside the trigger function which columns were
mentioned in the SET clause of the UPDATE statement.
But you can do that in CREATE TRIGGER:
CREATE TRIGGER ... BEFORE UPDATE OF (c3) ON mytable ...
Of course that might mean that you cannot do conditional processing
in the trigger function, but you have to define several triggers
(which could perhaps share a trigger function).
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com