0

I have problems with recursive trigger: the table that fires the trigger and give a specific error for that; the trigger inserts a record, that fires the trigger, that fires on the table, that fires the trigger,...

So, I must create a trigger that collects the date of insertion or modification of a row. The date field is the one that must be filled when there is a modification or insertion of a row. The problem is that it tells me "HINT: Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate. "

This came out when I added OR UPDATE in the trigger. I leave my code for your consideration. How can I improve my code to avoid it being a recursive trigger? thanks!

*table_0 is a table to registrer changes.


ALTER TABLE table_gas ADD COLUMN update_t VARCHAR(20);


CREATE TABLE table_0  (
    id_gas          INT NOT NULL,
    update_t        VARCHAR(20)
 );
 

CREATE OR REPLACE FUNCTION inserted() RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP='INSERT') THEN
        INSERT INTO table_0 VALUES(NEW.id_gas, TO_CHAR(NOW(), 'yyyy-mm-dd hh:mm'));
        UPDATE table_gas x
            SET update_t=TO_CHAR(NOW(), 'yyyy-mm-dd hh:mm')
            FROM table_0 y
            WHERE x.id_gas = y.id_gas;
            RETURN NEW;
            
    ELSEIF (TG_OP='UPDATE') THEN
        INSERT INTO table_0 VALUES(NEW.id_gas, TO_CHAR(NOW(), 'yyyy-mm-dd hh:mm'));
        UPDATE table_gas x
            SET update_t=TO_CHAR(NOW(), 'yyyy-mm-dd hh:mm')
            FROM table_0 y
            WHERE x.id_gas = y.id_gas;
            RETURN NEW;
            
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER insert_d
    BEFORE INSERT OR UPDATE ON table_gas
    FOR EACH ROW EXECUTE FUNCTION inserted();

    
INSERT INTO table_gas(id_gas, id_city, id_car)
          VALUES(34, 398,'28ha')


2
  • I've removed sql-server tag as it is not sql-server but postgresql.
    – Peter
    Dec 8, 2022 at 15:13
  • Why are you inserting another row in the trigger? And why are you storing timestamp values in varchar columns? That is huge mistake
    – user1822
    Dec 8, 2022 at 15:18

1 Answer 1

0

As you are using a BEFORE trigger (which is good) you can directly assign the values you want to the NEW record. There is no need to run an UPDATE.

I also don't see a reason to distinguish between update and insert inside the trigger function. You are doing the same thing in both cases.

You should also really fix your data types. Storing a timestamp in a varchar column is a really, really bad idea.

So the trigger function can be simplified to:

CREATE OR REPLACE FUNCTION inserted() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO table_0 VALUES(NEW.id_gas, now());
  new.update_t := now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Online example

3
  • And how do I update the table_gas table with the date of modification or insertion of a row? because I assumed that from what I record in the table_0 table, I should copy the date field and take it to the table_gas table Dec 8, 2022 at 15:54
  • You don't. As the trigger fires on table_gas the assignment new.update_t := now(); will do that.
    – user1822
    Dec 8, 2022 at 15:56
  • thanks! I am sorry, I am starting in Postgres. Thanks so much! Dec 8, 2022 at 16:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.