Hi-

Newbie to postgresql. I download and complied 8.15 on RH 9. I wrote a
trigger that records the actions of customer table into a customer
history table. INSERT and UPDATE work fine but it's prevents any
deletions in the customer table. It records a deletion in the customer
history table but psql says zero rows deleted in the customer table. If
I disable the trigger, a deletion works fine. I tried deleting the
entire if/then/else block in the function and it still prevents deletions.

What am I doing wrong?

thanks,
Melvin

CREATE OR REPLACE FUNCTION customer_trigger() returns trigger as $$

BEGIN
IF (TG_OP = 'INSERT') THEN
NEW.last_update := CURRENT_DATE;
NEW.user_name := USER;
ELSIF (TG_OP = 'UPDATE') THEN
NEW.last_update := CURRENT_DATE;
NEW.user_name := USER;
INSERT INTO customer_history (customer_number, lname, fname,
street, city, state, zip,
credit_limit, balance,
last_update, user_name, action)
VALUES (OLD.customer_number, OLD.lname, OLD.fname,
OLD.street, OLD.city, OLD.state, OLD.zip,
OLD.credit_limit, OLD.balance, CURRENT_DATE, USER,
'UPDATE');
ELSIF (TG_OP = 'DELETE') THEN
INSERT INTO customer_history (customer_number, lname, fname,
street, city, state, zip,
credit_limit, balance,
last_update, user_name, action)
VALUES (OLD.customer_number, OLD.lname, OLD.fname,
OLD.street, OLD.city, OLD.state, OLD.zip,
OLD.credit_limit, OLD.balance, CURRENT_DATE, USER,
'DELETE');
END IF;
return NULL;
END;
$$ language plpgsql;

CREATE TRIGGER tg_all_customer
BEFORE UPDATE OR INSERT OR DELETE ON customer
FOR EACH ROW EXECUTE PROCEDURE customer_trigger();

At 2006-11-05 21:30 Melvin Toy said:

Your trigger returns NULL, and is fired BEFORE the actual operation.
That means skipping the operation for the current row.
http://www.postgresql.org/docs/8.1/s...GER-DEFINITION