CREATE OR REPLACE TRIGGER test_trg
after update on call
for each row
declare user varchar2(20);
begin
select getosuser into user from dual;
IF UPDATING('name') and (:new.name != :old.name) THEN
        insert into bill  (
        id
        ,chg_dt
        ,fname
        ,old_val
        ,new_val
        ,opr
        )
        VALUES
                (
                'call'
                ,sysdate
                ,'name'
                ,:old.name
                ,:new.name
                ,user
                );
END IF;

CREATE OR REPLACE TRIGGER test_trg
after update on call
for each row
declare user varchar2(20);
begin
select getosuser into user from dual;
IF UPDATING('name') and (nvl(:new.name) != nvl(:old.name)) THEN
        insert into bill  (
        id
        ,chg_dt
        ,fname
        ,old_val
        ,new_val
        ,opr
        )
        VALUES
                (
                'call'
                ,sysdate
                ,'name'
                ,:old.name
                ,:new.name
                ,user
                );
END IF;

CREATE OR REPLACE TRIGGER test_trg
after update on call
for each row
declare user varchar2(20);
begin
select getosuser into user from dual;
IF UPDATING('name') and (nvl(:new.name,' ') != nvl(:old.name,' ')) THEN
        insert into bill  (
        id
        ,chg_dt
        ,fname
        ,old_val
        ,new_val
        ,opr
        )
        VALUES
                (
                'call'
                ,sysdate
                ,'name'
                ,:old.name
                ,:new.name
                ,user
                );
END IF;

SCOTT@orcl_11g> CREATE TABLE call
  2    ( name	  VARCHAR2 (10) UNIQUE)
  3  /

Table created.

SCOTT@orcl_11g> CREATE TABLE bill
  2    ( id	  VARCHAR2 (4)
  3    , chg_dt   DATE
  4    , fname	  VARCHAR2 (5)
  5    , old_val  VARCHAR2 (7)
  6    , new_val  VARCHAR2 (7)
  7    , opr	  VARCHAR2 (30))
  8  /

Table created.

SCOTT@orcl_11g> CREATE TABLE bill_errors
  2   (ORA_ERR_NUMBER$				  NUMBER,
  3    ORA_ERR_MESG$				  VARCHAR2(2000),
  4    ORA_ERR_ROWID$				  ROWID,
  5    ORA_ERR_OPTYP$				  VARCHAR2(2),
  6    ORA_ERR_TAG$				  VARCHAR2(2000),
  7    ID					  VARCHAR2(4000),
  8    CODE					  VARCHAR2(4000),
  9    DESCRIPTION				  VARCHAR2(4000))
 10  /

Table created.

SCOTT@orcl_11g> CREATE OR REPLACE TRIGGER test_trg
  2    AFTER UPDATE ON call
  3    FOR EACH ROW
  4  BEGIN
  5    IF UPDATING('name') and (nvl(:new.name,' ') != nvl(:old.name,' ')) THEN
  6  	     insert into bill  (
  7  	     id
  8  	     ,chg_dt
  9  	     ,fname
 10  	     ,old_val
 11  	     ,new_val
 12  	     ,opr
 13  	     )
 14  	     VALUES
 15  		     (
 16  		     'call'
 17  		     ,sysdate
 18  		     ,'name'
 19  		     ,:OLD.name
 20  		     ,:NEW.name
 21  		     ,user
 22  		     )
 23  	 LOG ERRORS INTO bill_errors  ('INSERT') REJECT LIMIT UNLIMITED;
 24    END IF;
 25  END test_trg;
 26  /

Trigger created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> -- valid inserts and updates:
SCOTT@orcl_11g> INSERT INTO call VALUES ('name1')
  2  /

1 row created.

SCOTT@orcl_11g> UPDATE call SET name = 'name2' WHERE name = 'name1'
  2  /

1 row updated.

SCOTT@orcl_11g> UPDATE call SET name = '' WHERE name = 'name2'
  2  /

1 row updated.

SCOTT@orcl_11g> UPDATE call SET name = 'name3' WHERE name IS NULL
  2  /

1 row updated.

SCOTT@orcl_11g> INSERT INTO call VALUES ('name2')
  2  /

1 row created.

SCOTT@orcl_11g> COMMIT
  2  /

Commit complete.

SCOTT@orcl_11g> -- will not update call, so will not insert into bill or log in bill_errors:
SCOTT@orcl_11g> UPDATE call SET name = 'name3' WHERE name = 'name2'
  2  /
UPDATE call SET name = 'name3' WHERE name = 'name2'
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C0042373) violated


SCOTT@orcl_11g> COMMIT
  2  /

Commit complete.

SCOTT@orcl_11g> -- will update call, but cannot insert into bill, so will log in bill_errors:
SCOTT@orcl_11g> UPDATE call SET name = 'long_name4' WHERE name = 'name2'
  2  /

1 row updated.

SCOTT@orcl_11g> COMMIT
  2  /

Commit complete.

SCOTT@orcl_11g> SELECT * FROM call
  2  /

NAME
----------
name3
long_name4

SCOTT@orcl_11g> SELECT * FROM bill
  2  /

ID   CHG_DT    FNAME OLD_VAL NEW_VAL OPR
---- --------- ----- ------- ------- ------------------------------
call 28-AUG-08 name  name1   name2   SCOTT
call 28-AUG-08 name  name2           SCOTT
call 28-AUG-08 name          name3   SCOTT

SCOTT@orcl_11g> SELECT ora_err_mesg$ FROM bill_errors
  2  /

ORA_ERR_MESG$
----------------------------------------------------------------------------------------------------
ORA-12899: value too large for column "SCOTT"."BILL"."NEW_VAL" (actual: 10, maximum: 7)

SCOTT@orcl_11g>

CREATE OR REPLACE TRIGGER test_trg
after update on call
for each row
begin
IF UPDATING('name') and (nvl(:new.name,' ') != nvl(:old.name,' ')) THEN
        insert into bill  (
        id
        ,chg_dt
        ,fname
        ,old_val
        ,new_val
        )
        VALUES
                (
                'call'
                ,sysdate
                ,'name'
                ,:old.name
                ,:new.name
                )
LOG ERRORS INTO LOG  ('INSERT') REJECT LIMIT UNLIMITED;
END IF;
IF UPDATING('call_dt') and (nvl(:new.call_dt,' ') != nvl(:old.call_dt,' ')) THEN
        insert into bill  (
        id
        ,chg_dt
        ,fname
        ,old_val
        ,new_val
         )
        VALUES
                (
                'call'
                ,sysdate
                ,'call_dt'
                ,:old.call_dt
                ,:new.call_dt
                )
LOG ERRORS INTO LOG ('INSERT') REJECT LIMIT UNLIMITED;
END IF;

  IF UPDATING('name') and 
     (:new.name != :old.name 
      OR (:new.name IS NULL AND :old.name IS NOT NULL) 
      OR (:old.name IS NULL AND :new.name IS NOT NULL)) THEN