Delete related records before delete trigger postgresql

Question

I need to make trigger, which will delete related rows in related table. Yes, I know about ON DELETE CASCADE, but the thing is I need to do it using trigger. I google it for several hours, but don't find solution for Postgesql. I use postgresql 9.4, pgAdmin III.

I have the following tables:

CREATE TABLE "Customer"
(
    "Id" serial NOT NULL,
    "ContactPerson" text NOT NULL,
    "Address" text NOT NULL,
    "PhoneNumber" "PhoneNumber" NOT NULL,
    CONSTRAINT "CustomerId (PK)" PRIMARY KEY ("Id")
)
WITH (
    OIDS=FALSE
);

CREATE TABLE "Order"
(
    "Id" serial NOT NULL,
    "OrderDate" date NOT NULL DEFAULT ('now'::text)::date,
    "Amount" integer NOT NULL DEFAULT 1,
    "CustomerId" integer NOT NULL,
    "GoodId" integer NOT NULL,
    CONSTRAINT "OrderId (PK)" PRIMARY KEY ("Id"),
    CONSTRAINT "CustomerId (FK)" FOREIGN KEY ("CustomerId")
        REFERENCES "Customer" ("Id") MATCH SIMPLE
        ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT "GoodId (FK)" FOREIGN KEY ("GoodId")
        REFERENCES "Good" ("Id") MATCH SIMPLE
        ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT "AmountCheck" CHECK ("Amount" > 0),
    CONSTRAINT "OrderDateCheck" CHECK ("OrderDate" <= 'now'::text::date)
)
WITH (
    OIDS=FALSE
);

I create trigger using this query:

CREATE FUNCTION DeleteCustomerWithOrders() RETURNS trigger AS $DeleteCustomerWithOrders$
    BEGIN
        DELETE FROM "Order"
        WHERE "Order"."CustomerId" = (SELECT "Id" FROM "Customer");
        RETURN "Customer";        
    END;
$DeleteCustomerWithOrders$ LANGUAGE plpgsql;

CREATE TRIGGER DeleteCustomerWithOrders BEFORE DELETE ON "Customer"
    EXECUTE PROCEDURE DeleteCustomerWithOrders();

The query runs well, but then I try to delete row from Customer, it gets an error:

(column "Customer" does not exists)

There is only examples of ON INSERT or UPDATE triggers in documentation, there they use RETURN NEW. Also I saw using OLD in sql-server. Nothing of it helps in my case. Also there may be errors in WHERE.

UPDATE

Thanks to Patrick. This solves first problem, but causes another one.

Error: record old does not assigned value. Detail: for not assigned record cortege structure is not defined.

Unrelated, but: you should really avoid those dreaded quoted identifiers. They are much more trouble in the long run then they are worth it. It also would be better if you post the error messages in English (set lc_messages=English) — user330315
– user330315, Commented Nov 24, 2016 at 11:13
lc_messages=English I should write it in script or in pgAdmin settings? Sorry, don't understand, what do you mean "dreaded quoted identifiers", my function name? — QuarK
– QuarK, Commented Nov 24, 2016 at 11:47

Patrick · Accepted Answer · 2016-11-24 03:20:47Z

4

You have two small problems with your code.

The trigger runs on table "Customer" but in the trigger function you refer to the row of this table being deleted with the pre-defined parameter OLD. Also, do not select the id from that row, but just refer to it (the sub-query actually retrieves all the ids from the table, which is wrong too).

The trigger function should look like this:

CREATE FUNCTION DeleteCustomerWithOrders() RETURNS trigger AS
$DeleteCustomerWithOrders$
BEGIN
    DELETE FROM "Order"
    WHERE "Order"."CustomerId" = OLD."Id";
    RETURN OLD;        
END;
$DeleteCustomerWithOrders$ LANGUAGE plpgsql;

The error you received comes from the statement RETURN "Customer";. The RETURN statement expects a trigger parameter but find an identifier. The system then interprets this as a SELECT statement that will return a trigger object but the statement is obviously incomplete: "Customer" is in the select list and thus interpreted as a column name, but there is no row source (FROM ...) to complete the statement and it can thus not resolve the column name.

edited Nov 24, 2016 at 3:20

answered Nov 24, 2016 at 3:09

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

QuarK Over a year ago

Unfortunately this solves problem on the screenshot, causes to another one. Please look update in first massage.

Patrick Over a year ago

There is nothing wrong with the trigger function, as far as I can tell. Are you maybe calling this function directly (e.g. not automatically on DELETE but manually)?

QuarK Over a year ago

No, I was deleting it using either table data view or DELETE FROM. In both case I got this error.

Collectives™ on Stack Overflow

Delete related records before delete trigger postgresql

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related