Search Postgresql Archives

A trigger question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux