SQL Create Trigger to Update Table under certain conditions

Question

I want to create a trigger where when a certain condition for a new table insert is met, both the table being inserted to and a corresponding table are updated. I want these tables to first be cleared and then have them repopulated using the functions I specify. So far I have this

CREATE FUNCTION test_fxn() RETURNS TRIGGER AS $test_fxn$
    BEGIN
       IF NEW.variable2 > x IS TRUE THEN
          RAISE EXCEPTION 'too long';
       END IF;
       IF NEW.variable2 < x IS TRUE THEN
INSERT INTO summary_table (variable1, variable2) VALUES (NEW.variable1, NEW.variable2);
          RAISE EXCEPTION 'correct';
      
       END IF;
    END;
$test_fxn$ LANGUAGE plpgsql;

CREATE TRIGGER test_fxn BEFORE INSERT OR UPDATE ON detailed_table
    FOR EACH ROW EXECUTE PROCEDURE test_fxn();

Despite the exceptions showing up, the newly inserted row isn't being copied into the summary_table like I'd like it to. In addition it doesn't seem to be showing up in the detailed_table either, however removing the INSERT INTO statement and executing with the trigger will raise the exceptions and execute as normal. Any tips to go about fixing this would be appreciated.

Triggers are highly vendor-specific - so please add a tag to specify whether you're using mysql, postgresql, sql-server, oracle or db2 - or something else entirely. — marc_s
– marc_s, Commented Oct 28, 2021 at 4:23
You are missing a return new; in your trigger function. An INSERT or UPDATE should result in the error "control reached end of trigger procedure without RETURN" — user330315
– user330315, Commented Oct 28, 2021 at 6:18
"Despite the exceptions showing up, the newly inserted row isn't being copied into the summary_table like I'd like it to" - the INSERT inside your trigger is part of the same transaction that fired the trigger. An exception forces that transaction to roll back - including the INSERT into summary_table. You are looking for "autonomous transaction" which aren't really supported in Postgres. Search this site for "autonomous transaction" and postgres - there are workarounds, e.g. using the dblink module — user330315
– user330315, Commented Oct 28, 2021 at 6:21

Laurenz Albe · Accepted Answer · 2021-10-28 06:59:38Z

1

Your problem is the RAISE EXCEPTION 'correct';. An exception causes an error in PostgreSQL, and that error causes the whole transaction including the effects of the trigger to be rolled back.

Use RAISE NOTICE for informational messages to the client.

answered Oct 28, 2021 at 6:59

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

M. G Over a year ago

I've added RETURN NEW and changed the exceptions to notices, yet the information still isn't being inserted into the summary_table as expected. Is there any further solution to this?

Laurenz Albe Over a year ago

If the notice is shown, the insert will have taken place. Perhaps you rolled the whole transaction back.

Collectives™ on Stack Overflow

SQL Create Trigger to Update Table under certain conditions

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related