How to use postgresql before update trigger? This doesn't execute only compiles correctly

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();

The select statement returns a matching role description from the roles master for the issue status of the queue being updated associated with the role for the current user from the q_users_roles table belonging to the users_groups. The sql gives correct output (role description) when executed using sqlalchemy- core in python api call. This is my first trigger. Where is the syntax error

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 | [email protected] |      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)

@VaoTsun Adding the table data and relevent columns. Why would mcan_edit_tkts always yield a false value? — user956424
– user956424, Commented Jul 24, 2018 at 5:40
(a) the ordering of your migration statements appears wrong. You create the trigger only to drop it almost IMMEDIATELY and (b) you're trigger return values/comments do not match the type of trigger you are using it as. See the comment about the trigger being an "after" trigger instead of a before trigger. — Dunes
– Dunes, Commented Jul 24, 2018 at 6:32
@Dunes, I wish to have a "before" trigger. Can you correct the above? What is the migration statement? Where can I find more info? — user956424
– user956424, Commented Jul 24, 2018 at 6:41

Dunes · Accepted Answer · 2018-07-25 15:20:12Z

One of the key problems with how you create your trigger is you immediately do

drop trigger if exists issue_user_role on rt_issues;

So there won't be a trigger to execute after this.

A side problem is, that given the constraint you are trying to enforce, you probably want a trigger to fire on an insert too.

I had trouble figuring out exactly what you code is meant to be doing. So instead of directly answering your question here is an example trigger for a basic schema and examples of how and when it fires. There is a table value test_table which stores an operand (value), a unary operation (op_code) and the result. The trigger tries to ensure that the stored result is always correct for the given value and op_code.

Schema

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
    )
;

Trigger Function

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;

Actual Trigger

-- 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();

Data

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
;

If you need more information the PostgreSQL docs are usually quite detailed.

Collectives™ on Stack Overflow

How to use postgresql before update trigger? This doesn't execute only compiles correctly

1 Answer 1

Schema

Trigger Function

Actual Trigger

Data

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Schema

Trigger Function

Actual Trigger

Data

Comments

Your Answer

Sign up or log in

Post as a guest

Related