Re: After delete trigger problem - Mailing list pgsql-general

Hi,

here is a complete example. With my Windows PostgreSQL 8.3.3 installation
this example leads to exception, because master has been deleted before the
child.

Teemu


--DROP TABLE master;
--DROP TABLE child;
--DROP FUNCTION fn_checkmaster()

-- The master table
CREATE TABLE master
(
  foo smallint NOT NULL DEFAULT 0,
  CONSTRAINT master_pkey PRIMARY KEY (foo)
)
WITH (OIDS=FALSE);
ALTER TABLE master OWNER TO postgres;

-- A child table to the master
CREATE TABLE child
(
  foo smallint NOT NULL DEFAULT 0,
  hoo smallint NOT NULL DEFAULT 0,
  CONSTRAINT child_pkey PRIMARY KEY (foo,hoo),
  CONSTRAINT child_foo_fkey FOREIGN KEY (foo)
      REFERENCES master (foo) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (OIDS=FALSE);
ALTER TABLE tilitysraha OWNER TO postgres;

-- Function which checks the master table
CREATE OR REPLACE FUNCTION fn_checkmaster() RETURNS trigger AS
$BODY$
DECLARE
  fcount integer;
BEGIN
  -- Want to check something from the master table
  SELECT count(*) INTO fcount FROM master WHERE master.foo = old.foo;
  -- Nothing found
  IF fcount = 0 THEN
    RAISE EXCEPTION 'Master not found anymore!';
  END IF;

  RETURN old;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE COST 100;
ALTER FUNCTION fn_checkmaster() OWNER TO postgres;

-- Trigger at the child table
CREATE TRIGGER "AFTER_DELETE_CHILD"
  AFTER DELETE
  ON child
  FOR EACH ROW
  EXECUTE PROCEDURE fn_checkmaster();

-- This example leads to an exception
INSERT INTO master (foo) VALUES (1);
INSERT INTO child (foo,hoo) VALUES (1,1);
DELETE FROM master WHERE foo=1;

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Teemu Juntunen" <teemu.juntunen@e-ngine.fi>
Cc: "PostgreSQL" <pgsql-general@postgresql.org>
Sent: Saturday, November 08, 2008 7:01 AM
Subject: Re: [GENERAL] After delete trigger problem


> "Teemu Juntunen" <teemu.juntunen@e-ngine.fi> writes:
>> Also according to the manual BEFORE DELETE trigger should launch before
>> casading delete, so I changed the trigger
>
>> CREATE TRIGGER "BTD_Y" BEFORE DELETE ON chlid  FOR EACH ROW EXECUTE
>> PROCEDURE fn_td_y();
>
>> with no help.
>
> In that case your problem is not about whether you are firing before the
> RI action happens; you've got some other bug instead.  It's hard to see
> what from the limited details you provided, though.  Can you put
> together a complete example?
>
> regards, tom lane
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general