Re: needing help from the PL/SQL experts

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 07 Oct 1998 02:32:53 GMT
Message-ID: <361ad015.1559692_at_192.86.155.100>

A copy of this was sent to burwelm_at_my-dejanews.com (if that email address didn't require changing) On Tue, 06 Oct 1998 20:53:10 GMT, you wrote:

>OK... So I have a database trigger that needs to assign values to some
>columns. But only when the transaction statement is not assigning them.
>Specifically..
>
>date_created, created_by, date_modified, modified_by
>
>When the sql statement does not include these columns, my trigger needs to
>assign sysdate and user, but if the sql statement does include them then the
>trigger should do nothing.
>
>My trigger logic for all columns is modeled as follows:
> if :new.date_created is null then
> :new.date_created := sysdate;
> end if;
>
>The problem is that it is trully testing for the VALUE NULL rather then
>whether the sql statement includes that column. And it does not execute the
>logic appropriately.
>
>Any help out there from the PL/SQL experts.
>

there is no straightforward way to accomplish this but here are some ideas

1- use DEFAULT if you can. for example:

SQL> create table t1 ( date_created       date default sysdate,
  2                    created_by      varchar2(30) default USER,
  3                    date_modified   date default sysdate,
  4                    modified_by     varchar2(30) default USER,
  5                    data            int );

Table created.

SQL> insert into t1 ( data ) values ( 1) ; 1 row created.

SQL> insert into t1 values ( sysdate-100, 'someone', sysdate-99, 'else', 2 ); 1 row created.

SQL> select * from t1;

DATE_CREA CREATED_BY DATE_MODI MODIFIED_B DATA

--------- ---------- --------- ---------- ----------
06-OCT-98 TKYTE      06-OCT-98 TKYTE               1
28-JUN-98 someone    29-JUN-98 else                2

   that way, you won't need an INSERT trigger........

2- on the UPDATE trigger (for the date_modified, modified_by columns) you could do something like:

SQL> create trigger t1_trigger
  2 before update on t1
  3 for each row
  4 begin

  5      if NOT updating( 'Date_modified' ) then
  6          :new.date_modified := sysdate;
  7      end if;
  8      if NOT updating( 'Modified_by' ) then
  9          :new.modified_by := USER;
 10      end if;

 11 end;
 12 /

Trigger created.

SQL>
SQL> update t1 set date_modified = sysdate-100,

  2                modified_by = 'someone',
  3                data = 3

  4 where data = 1;

1 row updated.

SQL>
SQL> update t1 set data = 4
  2 where data = 2;

1 row updated.

SQL>
SQL> select * from t1;

DATE_CREA CREATED_BY DATE_MODI MODIFIED_B DATA

--------- ---------- --------- ---------- ----------
06-OCT-98 TKYTE      28-JUN-98 someone             3
28-JUN-98 someone    06-OCT-98 TKYTE               4

So, when we update and set the value of modified_by, date_modified -- it accepts it, if we do not supply a value, it sets it to our default from the trigger...

>Melissa
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.

Received on Wed Oct 07 1998 - 04:32:53 CEST