agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Jason Aleski <jason.aleski@gmail.com>
To: pgsql-sql@lists.postgresql.org
Subject: Trouble with single trigger using UUID as key, should I use two triggers?
Date: Fri, 9 Mar 2018 20:15:47 -0600
Message-ID: <456d6fa7-0ade-4ceb-195b-77c37818c02f@gmail.com> (raw)
I'm running into a foreign key constraint when running a BEFORE trigger
for audit purposes. My initial goal was to do this in one BEFORE
trigger, but I'm leaning towards executing using two different
triggers. I'm seeking a second opinion! My initial thought was
creating a trigger that runs before the data is written to the primary
table; specifically on updates, as I want to increment the version_count
before the data is written to the primary table. Because the audit_id
(UUID generated) has not been written to the primary table, I get a
foreign key constraint problem when it tries to first write to the audit
table. I understand the error is because audit_id doesn't exist in the
primary table. If I were using a SERIAL/SEQUENCE, I could use the
NEXTVAL function; but I need to use UUID the key and audit_id.
Knowing that, I think my only option is to create an AFTER trigger for
the INSERT. Then create a BEFORE trigger when doing UPDATES and
DELETES. Before I go this path, I through I'd see if anyone has any
additional options I should consider; or would this be the suggested
path? Thoughts?
-JA-
--Example INSERT
INSERT INTO departments (department_legacyid, department_name) VALUES
('ACT', 'Accounting');
INSERT INTO departments (department_legacyid, department_name) VALUES
('HRM', 'Human Resources');
INSERT INTO departments (department_legacyid, department_name) VALUES
('CSR', 'Customer Service');
--ERROR
ERROR: insert or update on table "departments_audit" violates foreign
key constraint "departments_audit_audit_id_fkey" DETAIL: Key
(audit_id)=(241451bd-796e-5add-95b0-18f6098bc08f) is not present in
table "departments". CONTEXT: SQL statement "INSERT INTO
departments_audit (audit_id, audit_lastaction, department_legacyid,
department_name ) VALUES (NEW.audit_id, 'INSERT',
NEW.department_legacyid, NEW.department_name )" PL/pgSQL function
process_departments_audit() line 10 at SQL statement SQL state: 23503
--Primary Table
CREATE TABLE IF NOT EXISTS departments (
row_id UUID NOT NULL DEFAULT
uuid_generate_v5(uuid_ns_dns(), 'location1.local'),
row_created TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT
CURRENT_TIMESTAMP,
audit_id UUID NOT NULL DEFAULT
uuid_generate_v5(uuid_ns_dns(), 'location1.local'),
version_count INTEGER NOT NULL DEFAULT 1,
version_timestamp TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT
CURRENT_TIMESTAMP,
department_legacyid VARCHAR(255) NOT NULL,
department_name VARCHAR(255) NOT NULL,
PRIMARY KEY (row_id)
);
--Creating index on audit_ID to ensure there is only one unique record
in primary table
CREATE UNIQUE INDEX departments_ref_idx ON departments(audit_id);
--Audit Table
CREATE TABLE IF NOT EXISTS departments_audit (
row_id UUID NOT NULL DEFAULT
uuid_generate_v5(uuid_ns_dns(), 'location1.local'),
row_created TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT
CURRENT_TIMESTAMP,
audit_id UUID NOT NULL DEFAULT
uuid_generate_v5(uuid_ns_dns(), 'location1.local'),
audit_lastaction CHARACTER VARYING DEFAULT 'NOT DEFINED',
version_count INTEGER NOT NULL DEFAULT 1,
version_timestamp TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT
CURRENT_TIMESTAMP,
department_legacyid VARCHAR(255) NOT NULL,
department_name VARCHAR(255) NOT NULL,
PRIMARY KEY (row_id),
FOREIGN KEY (audit_id) REFERENCES departments(audit_id)
);
-- Creating audit trigger for departments
CREATE OR REPLACE FUNCTION process_departments_audit() RETURNS TRIGGER
AS $departments_audit$
BEGIN
IF (TG_OP = 'DELETE') THEN
--Nothing yet
ELSEIF (TG_OP = 'UPDATE') THEN
--Nothing yet
NEW.version_count = OLD.version_count + 1;
ELSEIF (TG_OP = 'INSERT') THEN
INSERT INTO departments_audit (audit_id,
audit_lastaction,
department_legacyid,
department_name
)
VALUES (NEW.audit_id,
'INSERT',
NEW.department_legacyid,
NEW.department_name
);
END IF;
RETURN NULL;
END;
$departments_audit$ LANGUAGE plpgsql;
--Applying trigger BEFORE data is written to primary table
CREATE TRIGGER department_audit
BEFORE INSERT OR UPDATE OR DELETE ON departments
FOR EACH ROW EXECUTE PROCEDURE process_departments_audit();
Message-ID: <456d6fa7-0ade-4ceb-195b-77c37818c02f@gmail.com>
Permalink: ../456d6fa7-0ade-4ceb-195b-77c37818c02f@gmail.com/
Also on: postgresql.org/message-id/456d6fa7-0ade-4ceb-195b-77c37818c02f@gmail.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jason.aleski@gmail.com, pgsql-sql@lists.postgresql.org
Subject: Re: Trouble with single trigger using UUID as key, should I use two triggers?
In-Reply-To: <456d6fa7-0ade-4ceb-195b-77c37818c02f@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox