Date: Thu, 17 Oct 2002 18:40:30 +0400
From: =?koi8-r?B?4cvVzM/XIOHMxcvTwc7E0g==?= <akulov@ien.ru>
Reply-To: =?koi8-r?B?4cvVzM/XIOEu9y4=?= <akulov@ien.ru>
Message-ID: <9736000265.20021017184030@ien.ru>
To: pgsql-sql@postgresql.org
Subject: Can I create working trigger on view
MIME-Version: 1.0
Content-Type: text/plain; charset=koi8-r
Content-Transfer-Encoding: 8bit

Hello!
Please, help me!

Can I create working trigger on view?

The problem is:
  I need a plpgsql function that execute on insert (or update,
  or delete) into view and knows the *OLD* and *NEW*.
  (Number of fields can be more than 16)
    
Something like this:

  Create Sequence id;
  Create Table t1(id1 int4, last_name varchar(32), time_create_t1);
  Create Table t2(id2 int4, first_name varchar(32), time_create_t2);
  Create View name as
    Select * from t1 a, t2 b where a.id1=b.id2;
  Create Rule rl_ins_nameas on INSERT to name do instead nothing;
  -- without it trigger is not allowed
  Create Function fn_ins_name() returns opaque as
  '
  Declare
    v_time_create timestamp;
  Begin
    v_tm_create:=current_timestamp;
    Insert into t1(id1, last_name, time_create_t1)
            values(nextval(''id''), new.last_name, v_time_create);
    Insert into t2(id2, first_name, time_create_t2)
            values(currval(''id''), new.first_name, v_time_create);
    return null;
  End;
  ' Language 'plpgsql';
  Create Trigger tr_ins_name before INSERT on now_cis_user
  for each row execute procedure fn_ins_name;
  
  This example is very simple. The trigger is not fired.

Sorry for my English.
--
Thanks for everybody.
Akulov Alexander.

e-mail:akulov@ien.ru