PLPGSQL Cascading Triggers?

Question

I am trying to create a trigger, so that when ever I add a new record it adds another record in the same table. The session field will only take values between 1 and 4. So when I add a 1 in session I want it to add another record but with session 3 blocked. But the problem is that it leads to cascading triggers and it inserts itself again and again because the trigger is triggered when inserted.

I have for example a simple table:

CREATE TABLE example
(
id      SERIAL PRIMARY KEY
,name   VARCHAR(100) NOT NULL
,session   INTEGER
,status VARCHAR(100)
);

My trigger function is:

CREATE OR REPLACE FUNCTION add_block() RETURNS TRIGGER AS $$

BEGIN

INSERT INTO example VALUES (NEW.id + 1, NEW.name, NEW.session+2, 'blocked');

RETURN NULL;
END;
$$ LANGUAGE 'plpgsql';

Trigger is:

CREATE TRIGGER add_block
AFTER INSERT OR UPDATE
ON example
FOR EACH ROW
EXECUTE PROCEDURE add_block();

I get error:

SQL statement "INSERT INTO example VALUES ( $1 +1,  $2 ,  $3 + 2, $4)"
PL/pgSQL function "add_block" line 37 at SQL statement

This error repeats itself so many times that I can't see the top.

How would I solve this?

EDIT:

CREATE TABLE block_rules
(
id      SERIAL PRIMARY KEY
,session   INTEGER
,block_session   INTEGER
);

This table holds the block rules. So if a new record is inserted into the EXAMPLE table with session 1 then it blocks session 3 accordingly by inserting a new record with blocked status in the same (EXAMPLE) table above (not block_rules). Same for session 2 but it blocks session 4.

The block_rules table holds the rules (or pattern) to block a session by. It holds

id | session | block_session
------------------------------
1  | 1       | 3
2  | 2       | 4
3  | 3       | 2

How would I put that in the WHEN statement of the trigger going with Erwin Branstetter's answer below?

Thanks

There might be a more elegant solution. Which version of PostgreSQL do you use? Did I understand you correctly that you only want to fire the trigger when NEW.session is 1 or 2, but not for 3 or 4? And can session be NULL? — Erwin Brandstetter
– Erwin Brandstetter, Commented Mar 7, 2012 at 23:42
"into the example table" - which one? Or do you mean the example table? "by inserting a new record" - into which of the two tables? The role of table block_rules is not clear to me, either. Please clarify. — Erwin Brandstetter
– Erwin Brandstetter, Commented Mar 13, 2012 at 16:04
Please answer this question: How do you discern auto-inserted rows in table example from others? Do only auto-inserted rows have status = blocked? And can we then skip the trigger? — Erwin Brandstetter
– Erwin Brandstetter, Commented Mar 13, 2012 at 16:23
Yes only rows with status = blocked are inserted with the blocked session on the example table. Isn't the trigger needed to INSERT the next record automatically? The WHEN statement on the trigger is what I can't crack. — Neeran
– Neeran, Commented Mar 13, 2012 at 16:31

Erwin Brandstetter · Accepted Answer · 2012-03-13 16:37:55Z

New answer to edited question

This trigger function adds blocked sessions according to the information in table block_rules.

~~I assume that the tables are linked by id - information is missing in the question.~~
I now assume that the block rules are general rules for all sessions alike and link by session. The trigger is only called for non-blocked sessions and inserts a matching blocked session.

Trigger function:

CREATE OR REPLACE FUNCTION add_block()
  RETURNS TRIGGER AS
$BODY$
BEGIN
    INSERT INTO example (name, session, status)
    VALUES (NEW.name
           ,(SELECT block_session
             FROM   block_rules
             WHERE  session = NEW.session)
           ,'blocked');

    RETURN NULL;
END;
$BODY$ LANGUAGE plpgsql;

Trigger:

CREATE TRIGGER add_block
AFTER INSERT -- OR UPDATE
ON example
FOR EACH ROW
WHEN (NEW.status IS DISTINCT FROM 'blocked')
EXECUTE PROCEDURE add_block();

Answer to original question

There is still room for improvement. Consider this setup:

CREATE OR REPLACE FUNCTION add_block()
  RETURNS TRIGGER AS
$BODY$
BEGIN

INSERT INTO example (name, session, status)
VALUES (NEW.name, NEW.session + 2, 'blocked');

RETURN NULL;
END;
$BODY$ LANGUAGE plpgsql;


CREATE TRIGGER add_block
AFTER INSERT -- OR UPDATE
ON example
FOR EACH ROW
WHEN (NEW.session < 3)
-- WHEN (status IS DISTINCT FROM 'blocked') -- alternative guess at filter
EXECUTE PROCEDURE add_block();

Major points:

For PostgreSQL 9.0 or later you can use a WHEN condition in the trigger definition. This would be most efficient. For older versions you use the same condition inside the trigger function.
There is no need to add a column, if you can define criteria to discern auto-inserted rows. You did not tell, so I assume that only auto-inserted rows have session > 2 in my example. I added an alternative WHEN condition for status = 'blocked' as comment.
You should always provide a column list for INSERTs. If you don't, later changes to the table may have unexpected side effects!
Do not insert NEW.id + 1 in the trigger manually. This won't increment the sequence and the next INSERT will fail with a duplicate key violation.
id is a serial column, so don't do anything. The default nextval() from the sequence is inserted automatically.
Your description only mentions INSERT, yet you have a trigger AFTER INSERT OR UPDATE. I cut out the UPDATE part.
The keyword plpgsql doesn't have to be quoted.

What would I do if I had session 1 blocks 3, session 2 blocks 4 etc and I have a table of rules dictating that?
@Jimmy: Sorry, I can't make sense of that. It's probably best if you edit your original question with more information.

strkol · Accepted Answer · 2012-03-07 14:01:56Z

1

OK so can't you just add another column, something like this:

ALTER TABLE example ADD COLUMN trig INTEGER DEFAULT 0;

CREATE OR REPLACE FUNCTION add_block() RETURNS TRIGGER AS $$
BEGIN
    IF NEW.trig = 0 THEN
        INSERT INTO example VALUES (NEXTVAL('example_id_seq'::regclass), NEW.name, NEW.session+2, 'blocked', 1);
    END IF;
    RETURN NULL;
 END;
 $$ LANGUAGE 'plpgsql';

it's not great, but it works :-)

edited Mar 7, 2012 at 14:01

answered Mar 7, 2012 at 12:59

strkol

2,02817 silver badges12 bronze badges

Comments

strkol · Accepted Answer · 2012-03-07 12:06:07Z

0

CREATE OR REPLACE FUNCTION add_block() RETURNS TRIGGER AS $$
BEGIN
    SET SESSION session_replication_role = replica;
    INSERT INTO example VALUES (NEXTVAL('example_id_seq'::regclass), NEW.name, NEW.session+2, 'blocked');
    SET SESSION session_replication_role = origin;
    RETURN NULL;
 END;
 $$ LANGUAGE 'plpgsql';

answered Mar 7, 2012 at 12:06

strkol

2,02817 silver badges12 bronze badges

1 Comment

Neeran Over a year ago

I get ERROR: permission denied to set parameter "session_replication_role". I am only allowed limited access to this database and not super user. Is there a way without setting permissions?

Collectives™ on Stack Overflow

PLPGSQL Cascading Triggers?

3 Answers 3

New answer to edited question

Answer to original question

Major points:

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

New answer to edited question

Answer to original question

Major points:

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related