Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1euU3B-0000cO-Pz for pgsql-sql@arkaria.postgresql.org; Sat, 10 Mar 2018 02:15:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1euU3A-000730-GV for pgsql-sql@arkaria.postgresql.org; Sat, 10 Mar 2018 02:15:56 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1euU3A-00072q-1u for pgsql-sql@lists.postgresql.org; Sat, 10 Mar 2018 02:15:56 +0000 Received: from mail-it0-x241.google.com ([2607:f8b0:4001:c0b::241]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1euU37-0003oC-Cc for pgsql-sql@lists.postgresql.org; Sat, 10 Mar 2018 02:15:54 +0000 Received: by mail-it0-x241.google.com with SMTP id c11so4925344ith.4 for ; Fri, 09 Mar 2018 18:15:53 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:subject:to:message-id:date:user-agent:mime-version :content-language; bh=sG5Y+YBWWkGsSqL8xa6slNyNQLWC1XY+YgV7zpjBdVE=; b=h7oQiZ6oVGEv3C/OlYu4DY9MTyfRzmqtsob11B0uZlgEWcPrj5LhN5lkl5zQvSmhoJ IElSfSvKb9zDaopW2Ak9j9cZy5/IEjAewsZHP1INWy8CstZf4AcS3kE7//GMDNxKxan1 3tyLy3F6xwjzSZv462A7y8xL+8Gn5iwc72CASGC8CkE4J8CUkKmkLwiBbV/btt7QGvGP w8M1XUXLlPwYSweWuV/14GXjsnh72gc4j0WG3cWZ4neDHiYfsEbffEk1L+j7SHzUFmpP bAq1oZGvCik+1yk3SAPJin65cBiihYSnvSHMvC6D57zzt3flEZc6DNSY+bhEGb4OTpWu h3OQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:subject:to:message-id:date:user-agent :mime-version:content-language; bh=sG5Y+YBWWkGsSqL8xa6slNyNQLWC1XY+YgV7zpjBdVE=; b=A3N/SuwWPjVO5gEGAByY+HTrMyA5QQBIylHK5e1Zz0gseMrEQhsqvpaOzjKJkPTppr +bnKXZjkffS2dTOX5NGUDsfpyc9CjV8MWMyYtFoOOg2QTEQQ2F2tLdph2dBwOCaUrRah +W7AaE59sgL5AAeTiuTzYuJtqBGZpcWdDD1ESCJOeELm3CCcr2B7v8v4IGwk/U1TxoCb g0HvI9UMsosaMwWVVSDMxGZXJgXKYHseqwIM5lvqeoq5PLGCs9v9buql3CqdPINMaGg6 /TLN2+lE+WEPky5smbSKLDiCT+SRfQEhhOePJE5F2PR87m9CPTzBwcllMaEQa6j3vwWn cUoQ== X-Gm-Message-State: AElRT7GFt/1iNFJ3MXmK9nE8LwolnXkJbDftRvlGLLF6vTYawhiOOb9d r++wYwedmcKn+07atkMK4sw+MASX X-Google-Smtp-Source: AG47ELsZqeoSQHDOsG7at1gZURl9D8xFe8u8C4rXqA4ND6SnDIFXdHJsjLLX6wu/0vbSgPxPuS72rA== X-Received: by 10.36.101.10 with SMTP id u10mr109870itb.20.1520648150704; Fri, 09 Mar 2018 18:15:50 -0800 (PST) Received: from [10.0.1.180] (47-212-87-239.jsbrcmta03.res.dyn.suddenlink.net. [47.212.87.239]) by smtp.gmail.com with ESMTPSA id 15sm36916iti.9.2018.03.09.18.15.49 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Fri, 09 Mar 2018 18:15:49 -0800 (PST) From: Jason Aleski Subject: Trouble with single trigger using UUID as key, should I use two triggers? To: pgsql-sql@lists.postgresql.org Message-ID: <456d6fa7-0ade-4ceb-195b-77c37818c02f@gmail.com> Date: Fri, 9 Mar 2018 20:15:47 -0600 User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101 Thunderbird/52.6.0 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="------------FDBAABF6A38EA412A6616CDA" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------FDBAABF6A38EA412A6616CDA Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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(); --------------FDBAABF6A38EA412A6616CDA Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

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



--------------FDBAABF6A38EA412A6616CDA--