如何在更新触发器之前使用postgresql？这并不是只正确执行编译

Question

CREATE OR REPLACE FUNCTION get_issue_status_user_role() RETURNS TRIGGER AS
$issue_user_role$   
    DECLARE missue_id integer;
        mstatus integer;
        mcurr_user integer;
        mrole_descrp varchar;
        mcan_edit_tkts boolean;
        mqueue_id integer;
    BEGIN
    raise notice 'howdya';
    mcan_edit_tkts := False;
        -- Check roles for the logged in user 'before update' event
        -- get the queue id of the issue being edited. If user present
        -- in user_group_id for the queue id in the q_users_roles
        -- check the role(s) with status role of the current user

        --IF (TG_OP = 'UPDATE') THEN
        -- if OLD.description != NEW.description then
        missue_id := OLD.id;
        mcurr_user := NEW.updated_by;
        mqueue_id := NEW.queue_id;
        mstatus := OLD.status;
        mrole_descrp := (
   SELECT roles.description AS mrole_desc FROM rt_issues
   LEFT OUTER JOIN queues ON rt_issues.queue_id = queues.id
   LEFT OUTER JOIN rt_status ON rt_issues.status = rt_status.id
   LEFT OUTER JOIN q_users_roles ON queues.id = q_users_roles.queue_id
   LEFT OUTER JOIN roles ON q_users_roles.role_id = roles.id
   LEFT OUTER JOIN users_groups ON q_users_roles.user_group_id = users_groups.id
   LEFT OUTER JOIN users ON users_groups."user" = users.id
   WHERE rt_issues.id = missue_id AND
     rt_issues.status = mstatus AND
     users_groups."user" = mcurr_user);
        --end if;
        if mrole_descrp != 'can_change_status' then
                mcan_edit_tkts := False;
            else 
            mcan_edit_tkts := True;             
            end if;

    --END IF;    
    if mcan_edit_tkts then
        raise notice 'Edit permitted'; 
        RETURN NEW;
    else
        raise notice 'No permission to edit this ticket';
            RETURN Null; -- result is ignored since this is an AFTER trigger
    end if;
    END;
$issue_user_role$ LANGUAGE plpgsql;

drop trigger if exists issue_user_role on rt_issues;

CREATE TRIGGER issue_user_role BEFORE UPDATE OR INSERT ON rt_issues FOR EACH ROW EXECUTE PROCEDURE get_issue_status_user_role();

select语句从roles master返回与属于users\u组的q\u users\u roles表中的当前用户的角色关联的正在更新的队列的问题状态的匹配角色描述。在pythonapi调用中使用sqlalchemy-core执行时，sql提供了正确的输出（角色描述）。这是我的第一个扳机。语法错误在哪里

db1=# select id, first_name from users;
 id | first_name 
----+------------
  1 | ytxz
  2 | abcd
(2 rows)

db1=# select * from users_groups;
 id | user | group |
----+------+-------+
  2 |    2 |     1 |
  1 |    1 |     2 |
(2 rows)

db1=# select id, cc_user_ids, status, queue_id, updated_by from rt_issues where id=10; 
 id | cc_user_ids  | status | queue_id | updated_by 
----+-------------+--------------+--------+----------+---
 10 | not@quack.om |      2 |        1 |          2
(1 row)

db1=# select * from rt_status;
 id |     description     | role_id | queue_id | 
----+---------------------+---------+----------+
  2 | Initial check       |       1 |        1 |
  3 | Awaiting assignment |       1 |        1 |
  1 | New Issue           |       1 |        1 |
(3 rows)

db1=# select * from q_users_roles;
 id | queue_id | user_group_id | role_id |
----+----------+---------------+---------+
  9 |       16 |             1 |       2 | 
 25 |       21 |             1 |       2 | 
 26 |       24 |             1 |       2 | 
 16 |        1 |             1 |       1 | 
(4 rows)

db1=# select * from roles;
 id |     description      | xdata 
----+----------------------+-------
  1 | can_change_status    | 
  2 | can_create_tkts      | 
(2 rows)


db1=# SELECT roles.description AS mrole_desc FROM rt_issues LEFT OUTER JOIN queues ON rt_issues.queue_id = queues.id LEFT OUTER JOIN rt_status ON rt_issues.status = rt_status.id LEFT OUTER JOIN q_users_roles ON queues.id = q_users_roles.queue_id LEFT OUTER JOIN roles ON q_users_roles.role_id = roles.id LEFT OUTER JOIN users_groups ON q_users_roles.user_group_id = users_groups.id LEFT OUTER JOIN users ON users_groups."user" = users.id WHERE rt_issues.id = 10 AND rt_issues.status = 2 AND users_groups."user" = 1;
    mrole_desc     
-------------------
 can_change_status
(1 row)

db1=# SELECT roles.description AS mrole_desc FROM rt_issues LEFT OUTER JOIN queues ON rt_issues.queue_id = queues.id LEFT OUTER JOIN rt_status ON rt_issues.status = rt_status.id LEFT OUTER JOIN q_users_roles ON queues.id = q_users_roles.queue_id LEFT OUTER JOIN roles ON q_users_roles.role_id = roles.id LEFT OUTER JOIN users_groups ON q_users_roles.user_group_id = users_groups.id LEFT OUTER JOIN users ON users_groups."user" = users.id WHERE rt_issues.id = 10 AND rt_issues.status = 2 AND users_groups."user" = 2;
 mrole_desc 
------------
(0 rows)

Answer 1

如何创建触发器的一个关键问题是您立即执行

drop trigger if exists issue_user_role on rt_issues;

所以在这之后就不会有触发器执行了。你知道吗

另一个问题是，给定要强制执行的约束，您可能也希望在insert上触发触发器。你知道吗

我很难搞清楚你的代码到底要做什么。因此，这里不是直接回答您的问题，而是一个基本模式的触发器示例，以及如何以及何时触发的示例。表值test_table存储操作数（value）、一元运算（op_code）和result。触发器尝试确保存储的result对于给定的value和op_code总是正确的。你知道吗

架构

DROP TABLE IF EXISTS test_table;
DROP TABLE IF EXISTS test_operations;

CREATE TABLE test_operations (
    op_code TEXT PRIMARY KEY
);

INSERT INTO test_operations (op_code) VALUES
    ('double'),
    ('triple'),
    ('negative')
;

CREATE TABLE test_table (
    id bigserial PRIMARY KEY,
    op_code TEXT REFERENCES test_operations(op_code),
    value INTEGER NOT NULL,
    result INTEGER NOT NULL
    )
;

触发函数

CREATE OR REPLACE FUNCTION test_table_update_trigger() 
RETURNS TRIGGER AS $$
DECLARE
    expected_result INTEGER;
BEGIN   
    expected_result := (
        SELECT CASE NEW.op_code
            WHEN 'double' THEN NEW.value * 2
            WHEN 'triple' THEN NEW.value * 3
            WHEN 'negative' THEN -NEW.value
            END
    );

    IF NEW.result != expected_result
    THEN
        IF NEW.value BETWEEN -10 AND 10
        THEN
              silently ignore the update or insert
            RETURN NULL;
        ELSIF NEW.value >= 100
        THEN
              modify the update
            NEW.result = expected_result;
        ELSE
              abort the transaction
            RAISE EXCEPTION 
                'bad result (%)   expected % for % %', 
                NEW.result, expected_result, NEW.op_code, NEW.value;
        END IF;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

实际触发器

  remove old trigger if it exists
DROP TRIGGER IF EXISTS my_trigger ON test_table;

  best practice to create triggers after the function they use
CREATE TRIGGER my_trigger 
    BEFORE UPDATE OR INSERT 
    ON test_table
    FOR EACH ROW 
        EXECUTE PROCEDURE test_table_update_trigger();

数据

INSERT INTO test_table (op_code, value, result) VALUES
    ('double', 2, 4),
    ('double', 3, 6),
    ('double', 14, 28),
    ('triple', 2, 2),   this insert is ignored
      ('triple', 14, 14),   this would be an error
    ('triple', 120, 0),   this insert is corrected to have result of 360
    ('negative', 8, -8)
;

  this updates targets the first two 'double' rows, but only one row 
  is updated as the trigger returns NULL in one instance
UPDATE test_table
    SET 
        op_code = 'triple',
        result = 6
    WHERE
        op_code = 'double'
        AND value < 10   remove this clause to see an exception
;

如果您需要更多信息，PostgreSQL docs通常非常详细。你知道吗