Обсуждение: Having a little trouble with TRIGGERS

Hi all,

My apologies if this has been answered - I haven't found a reference to
it...

I am using PostgreSQL 7.0.3 (installed from an RPM) on a RedHat 6.0 system.
I have created a database and are trying to create a trigger.  The purpose
of this trigger is to automatically add the current date to a few fields
(maintDate) in a table on an UPDATE, and the createDate on an INSERT.
i.e. If a user modifies a record, the current date is updated in the record.
Just a basic tracking method so I know WHO and WHEN a record was modified.

Here is the code I am using:

CREATE FUNCTION trigger_insert_update_tblUser()
RETURNS opaque
AS 'DECLARE
    trig_type    text;
    rec_count    integer;

    BEGIN
    -- Determine if this is an INSERT or an UPDATE
    IF (
        SELECT
            COUNT(*)
        FROM
            old) > 0
    THEN
        -- This is an update
        trig_type := "i";
    ELSE
        -- This is an insert
        trig_type := "u";
    END IF;

    -- Trigger logic after this line
    IF trig_type = "i" THEN
        -- Set the maint date
        NEW.maintDate = date(now());
    END IF;

    IF trig_type = "u" THEN
        -- Set the create and maint dates
        NEW.maintDate  := date(now());
        NEW.dateCreated := date(now());
    END IF;

    -- End of trigger logic
    RETURN NEW;
END;'
LANGUAGE 'plpgsql';

The problem is that when I try to insert a record into the table, I get the
following error:

ERROR:  CURRENT used in non-rule query.


I've been banging my head against a brick wall for the past few days.  I
*think* that it has something to do with a problem of "new" being aliased
somehow with "CURRENT", but I don't know!!!  Save me from insanity!

Any help would be much appreciated.

Thanks,

Brendan Green
bgreen@simtap.com.au

"Brendan Green" <bgreen@simtap.com.au> writes:
> The problem is that when I try to insert a record into the table, I get the
> following error:
> ERROR:  CURRENT used in non-rule query.
> I've been banging my head against a brick wall for the past few days.  I
> *think* that it has something to do with a problem of "new" being aliased
> somehow with "CURRENT", but I don't know!!!  Save me from insanity!

Actually I think CURRENT is an alias for OLD; it may be that the select
from a table named OLD is confusing the thing.

            regards, tom lane

> "Brendan Green" <bgreen@simtap.com.au> writes:
> > The problem is that when I try to insert a record into the table, I get the
> > following error:
> > ERROR:  CURRENT used in non-rule query.
> > I've been banging my head against a brick wall for the past few days.  I
> > *think* that it has something to do with a problem of "new" being aliased
> > somehow with "CURRENT", but I don't know!!!  Save me from insanity!
>
> Actually I think CURRENT is an alias for OLD; it may be that the select
> from a table named OLD is confusing the thing.

I just checked to make sure that CURRENT doesn't appear in the current
source as used in rules.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026