sql - 触发引发 : "ERROR: stack depth limit exceeded"

Question

我试图在插入一幅画后创建一个触发器，然后我想将其插入到 In_Gallery 表或 On_Loan 表中，但不能同时插入到两者中。当我尝试创建触发函数时，我不断收到错误:

ERROR: stack depth limit exceeded
HINT:  Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.

我不确定这有什么问题:

    CREATE OR REPLACE FUNCTION checkOnLoan()    
    RETURNS trigger AS
$$
    DECLARE    
       countGal numeric;
    BEGIN
            SELECT COUNT(*) INTO countGal FROM IN_GALLERY WHERE P_id = new.P_id;
            IF countGal = 0 THEN    
                INSERT INTO ON_LOAN VALUES (new.Certid, new.P_id, new.Insurer);
            ELSE
                RAISE EXCEPTION 'ALREADY IN GALLERY';
            END IF;
    RETURN new;
    END;
$$

LANGUAGE 'plpgsql';

CREATE TRIGGER OnLoan
    AFTER INSERT ON ON_LOAN
    FOR EACH ROW
    EXECUTE PROCEDURE checkOnLoan();

Answer 1

你INSERT再次在 AFTER INSERT触发器，导致触发器在这一秒内再次被触发 INSERT又是INSERT并重新触发触发器，依此类推。在某些时候，堆栈会因所有函数调用而耗尽，并且您会收到错误。

删除INSERT从触发功能和只是 RETURN new 。返回new会导致原来INSERT要完成的。不需要手册 INSERT在 AFTER INSERT 的触发函数中触发器。

喜欢:

CREATE OR REPLACE FUNCTION checkOnLoan()    
RETURNS trigger AS
$$
DECLARE    
    countGal numeric;
BEGIN
    SELECT COUNT(*) INTO countGal FROM IN_GALLERY WHERE P_id = new.P_id;
    IF countGal = 0 THEN    
        RETURN new;
    ELSE
        RAISE EXCEPTION 'ALREADY IN GALLERY';
    END IF;
END;
$$
LANGUAGE plpgsql;

以及其他触发功能的模拟。