CODEWITHSUNDEEP
databasepostgresqlfunctiontriggers
Shiwangini
Shiwangini

Reputation: 836

Before insert (statement level trigger) isn't working

Below statements I have executed:

create table tab1(id int , name varchar(10) , city varchar(10) , latest bool default true);
CREATE OR REPLACE FUNCTION public.updateoldrow()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
begin
update tab1 set latest ='0' where name = new.name and city = new.city and latest = '1';
RETURN NEW;
END;
$function$
;

create trigger trig_updateoldrow before
insert
on
public.tab1 for each statement execute procedure updateoldrow();

insert into tab1 values(1,'Shiwangini','xyz','true');
insert into tab1 values(2,'Shiwangini','xyz','true');

I have created a table + function (to update old record to 'false' with the same matching value criteria) + then created a statement level trigger(before insert) -> then inserted 2 test records into that. Now, when I run 'select * from tab1;' as per expectation first value with id=1 should be set to 'false'. However, it is not happening. I have also checked execution plan of insert statement, so it is calling trigger:

    explain (analyze true, verbose, costs, buffers)insert into tab1 
    values(2,'Shiwangini','xyz','true');

    Insert on public.tab1  (cost=0.00..0.01 rows=1 width=81) (actual time=0.113..0.113 rows=0 
    loops=1)
    Buffers: shared read=1 dirtied=1
  ->  Result  (cost=0.00..0.01 rows=1 width=81) (actual time=0.001..0.001 rows=1 loops=1)
    Output: 2, 'Shiwangini'::character varying(10), 'xyz'::character varying(10), true
    Planning Time: 0.020 ms
    Trigger trig_updateoldrow: time=0.060 calls=1
    Execution Time: 0.128 ms

However, if the same thing I'm trying to achieve with row level trigger- It is working properly and updating the old value.

Here, I'm unable to understand why statement level trigger isn't performing as expected. Any reason for this will be appreciated.

Upvotes: 0

Views: 275

Answers (1)

JGH
JGH

Reputation: 17836

The doc says

NEW

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

so the update can't work properly since the NEW value is null. You really need a row level trigger.

Upvotes: 1

Related Questions