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();