Обсуждение: A trigger question

Hi,

I'm trying to set up a trigger to prevent any duplicate entries into
my table. But I couldn't figure out how to make my trigger work.

I have a PostgreSQL 8.1.5 installation.
I created the following table:

CREATE TABLE attendance (
attendance_id         serial,
service_date          timestamp DEFAULT NOW(),
service_type          varchar(18),
attendance_count     integer,
CONSTRAINT            attendance_pk primary key (attendance_id)
);

INSERT INTO attendance (service_date, service_type, attendance_count )
VALUES ('2006-05-01', 'First Service', 100);
INSERT INTO attendance (service_date, service_type, attendance_count )
VALUES ('2006-05-01', 'Second Service', 120);

Now I create the following:

CREATE FUNCTION history_check_fn() RETURNS trigger AS $history_check_fn$
    BEGIN
        -- Check that service_date and service_type are given
        IF NEW.service_date == OLD.service_date AND
           NEW.service_type == OLD.service_type THEN
            RAISE EXCEPTION 'Duplicate entry';
        END IF;
        RETURN;
    END;
$history_check_fn$ LANGUAGE plpgsql;

CREATE TRIGGER history_check_tr
  BEFORE INSERT OR UPDATE ON attendance
    FOR EACH ROW EXECUTE PROCEDURE history_check_fn();

When I tried to insert a duplicate entry [same service_date and
service_type], I got the following error:

INSERT INTO attendance (service_date, service_type, attendance_count )
VALUES ('2005-05-01', 'Second Service', 10);
ERROR:  record "old" is not assigned yet
DETAIL:  The tuple structure of a not-yet-assigned record is indeterminate.
CONTEXT:  PL/pgSQL function "history_check_fn" line 3 at if

I couldn't figure out what I'm doing incorrectly.
I appreciate if anyone can give me some advices.

Thanks,

- Chansup

Chansup Byun wrote:
> Hi,
>
> I'm trying to set up a trigger to prevent any duplicate entries into
> my table. But I couldn't figure out how to make my trigger work.
>
> I have a PostgreSQL 8.1.5 installation.
> I created the following table:
>
> CREATE TABLE attendance (
> attendance_id         serial,
> service_date          timestamp DEFAULT NOW(),
> service_type          varchar(18),
> attendance_count     integer,
> CONSTRAINT            attendance_pk primary key (attendance_id)
> );
>
> INSERT INTO attendance (service_date, service_type, attendance_count )
> VALUES ('2006-05-01', 'First Service', 100);
> INSERT INTO attendance (service_date, service_type, attendance_count )
> VALUES ('2006-05-01', 'Second Service', 120);
>
> Now I create the following:
>
> CREATE FUNCTION history_check_fn() RETURNS trigger AS $history_check_fn$
>    BEGIN
>        -- Check that service_date and service_type are given
>        IF NEW.service_date == OLD.service_date AND
>           NEW.service_type == OLD.service_type THEN
>            RAISE EXCEPTION 'Duplicate entry';
>        END IF;
>        RETURN;
>    END;
> $history_check_fn$ LANGUAGE plpgsql;
>
> CREATE TRIGGER history_check_tr
>  BEFORE INSERT OR UPDATE ON attendance
>    FOR EACH ROW EXECUTE PROCEDURE history_check_fn();
>
> When I tried to insert a duplicate entry [same service_date and
> service_type], I got the following error:
>
> INSERT INTO attendance (service_date, service_type, attendance_count )
> VALUES ('2005-05-01', 'Second Service', 10);
> ERROR:  record "old" is not assigned yet
> DETAIL:  The tuple structure of a not-yet-assigned record is
> indeterminate.
> CONTEXT:  PL/pgSQL function "history_check_fn" line 3 at if
>
> I couldn't figure out what I'm doing incorrectly.
> I appreciate if anyone can give me some advices.
>
I don't think OLD gets assigned for inserts. You should use TG_OP to
determine whether it is an insert or update calling the trigger.

http://www.postgresql.org/docs/8.2/static/plpgsql-trigger.html

However, you might simply be better off defining some unique indexes to
prevent duplicates.

-Jonathan

On Mon, Dec 18, 2006 at 22:18:43 -0500,
  Chansup Byun <byun1067@gmail.com> wrote:
> Hi,
>
> I'm trying to set up a trigger to prevent any duplicate entries into
> my table. But I couldn't figure out how to make my trigger work.

Couldn't you use a unique constraint to do this?

On 12/20/06, Bruno Wolff III <bruno@wolff.to> wrote:
> On Mon, Dec 18, 2006 at 22:18:43 -0500,
>  Chansup Byun <byun1067@gmail.com> wrote:
> > Hi,
> >
> > I'm trying to set up a trigger to prevent any duplicate entries into
> > my table. But I couldn't figure out how to make my trigger work.
>
> Couldn't you use a unique constraint to do this?
>
Yes, I found that from a private email. it works great.

Thanks,

- Chansup