| From: | "Chansup Byun" <byun1067(at)gmail(dot)com> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Cc: | byun1067(at)gmail(dot)com |
| Subject: | A trigger question |
| Date: | 2006-12-19 03:18:43 |
| Message-ID: | 5deb54410612181918l3c712601ke706b30464ebc766@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Reece Hart | 2006-12-19 04:24:08 | Re: Anyone? Best way to authenticate postgres against |
| Previous Message | semi-ambivalent | 2006-12-19 03:14:54 | Re: Changes in 8.2's PHP behaviour? |