CODEWITHSUNDEEP
postgresqltriggers
Tracker7
Tracker7

Reputation: 87

Trigger function can't update table

Here is my trigger function

CREATE OR REPLACE FUNCTION test_table_insert()
    RETURNS TRIGGER
AS $$
BEGIN
    IF NEW.id IS NULL THEN
        RAISE EXCEPTION 'id is null';
    END IF;
    UPDATE e_sub_agreement SET ro_id = NEW.id WHERE id = NEW.id;
    RETURN NEW;
END;
$$
    LANGUAGE plpgsql;

CREATE TRIGGER test_table_insert AFTER INSERT ON e_sub_agreement FOR EACH ROW EXECUTE PROCEDURE test_table_insert();

The problem is that it doesn't update table e_sub_agreement. I checked NEW value and everything is good. It returns with the new id. If I change where statement id = "some existing id in table", then it works. It changes ro_id to the new id. How is it possible? My guess is that data has not been inserted into table and trigger function can't find row with the given id. But it's not how trigger's after insert function works. What's the magic?

Upvotes: 0

Views: 377

Answers (1)

user330315
user330315

Reputation:

An AFTER trigger can not change anything. Running an additional UPDATE is also quite inefficient. Change this to a BEFORE trigger and assign the value you want:

CREATE OR REPLACE FUNCTION test_table_insert()
    RETURNS TRIGGER
AS $$
BEGIN
  IF NEW.id IS NULL THEN
    RAISE EXCEPTION 'id is null';
  END IF;
  NEW.ro_id := NEW.id;
  RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER test_table_insert 
  BEFORE INSERT ON e_sub_agreement 
  FOR EACH ROW EXECUTE PROCEDURE test_table_insert();

Note that the NOT NULL check is better done by defining the column as NOT NULL.

Upvotes: 1

Related Questions