Re: Create Before Insert Trigger

v2cg4ss <[EMAIL PROTECTED]> writes:

> I'm trying to assign values automatically to a row prior to the row
> being inserted.  I have no problem doing this with MySQL or Oracle,
> etc...
>
> However in Derby I was trying issued the following statement and the
> last_update_uid is populated with the content of the value in the
> insert statement.  How can this be done in Derby?
>
> create trigger clients_trigger
>  no cascade before insert on clients
>  referencing new as newrow
>  for each row mode db2sql
>  values (newrow.last_update_uid = CURRENT_USER, newrow.last_update_ts
> = CURRENT_TIMESTAMP);

You could try something like this idiom:

> create table t ( id int generated always as identity, last_update_uid
  varchar(255) default current_user, last_update_ts timestamp default
  current_timestamp, i int);

> insert into t values (default, default, default, 1);
> insert into t values (default, default, default, 2);

and a trigger like this:

> create trigger clients_trigger  after update on t 
  referencing old as oldrow new as newrow for each row 
  update t set last_update_uid = CURRENT_USER, 
  last_update_ts = CURRENT_TIMESTAMP
  where id = oldrow.id and oldrow.i != newrow.i;

Hope this helps,
Dag