Home » SQL & PL/SQL » SQL & PL/SQL » trigger issue (10.2.0.1, Windows 2003)
trigger issue [message #343692] Wed, 27 August 2008 17:31 Go to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
Hi all,

I created the below trigger to insert a record into table BILL when a field is updated in table CALL. Sometimes, for some reason the record is not getting inserted into table BILL. For troubleshooting this, I need to have the trigger send an error message to another table may be. Is this possible ?? PLease give me your suggestions.


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;



Thank you very much.
Re: trigger issue [message #343694 is a reply to message #343692] Wed, 27 August 2008 17:47 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
> Sometimes, for some reason the record is not getting inserted into table BILL.

Based upon what criteria do you conclude "record is not getting inserted into table BILL."

> For troubleshooting this, I need to have the trigger send an error message to another table may be.


Based upon what criteria will the decision be made that an error has occurred?
Re: trigger issue [message #343702 is a reply to message #343692] Wed, 27 August 2008 19:02 Go to previous messageGo to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
After updating the table CALL, we do not see the record in the table BILL.

And there are few other triggers created already similar to this for big tables(with 50 columns- update trigger condition for each column)and we are not sure if they are inserting all the records to the corresponding table.

We have a table called 'LOG' with date,msg,errmsg. Application team would like to have the trigger throw the error messages into this table, if it fails inserting the record into table BILL.

IF the record doesn't get inserted, is it possible to get the error message into the table 'LOG'?

Thank you for all your help.
Re: trigger issue [message #343705 is a reply to message #343692] Wed, 27 August 2008 19:47 Go to previous messageGo to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
Is it possible to track the errors into a table by using
LOG ERRORS [INTO [schema.]table] [('simple_expression')]

I am going to try it out now. Hope it will work.
Re: trigger issue [message #343711 is a reply to message #343705] Wed, 27 August 2008 21:15 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
There is a nice complete example of the LOG ERRORS clause here:

http://www.oracle-base.com/articles/10g/DmlErrorLogging_10gR2.php

However, there may not be an error raised. It just mean that a row is not getting inserted because it does not meet the criteria. I would suspect that your problem is in the following line of code:

IF UPDATING('name') and (:new.name != :old.name)

You need to be careful about putting things in quotes and causing them to be case sensitive and you also need to allow for null values. What if :new.name is null or :old.name is null? What happens to your comparison? You might went to use NVL or add additional IF conditions to allow for the null values.

Re: trigger issue [message #343920 is a reply to message #343692] Thu, 28 August 2008 08:02 Go to previous messageGo to next message
plshelp
Messages: 205
Registered: January 2007
Senior Member
Barbara, thank you for your reply. It stricked my mind for the null values. THat could be the reason.

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;


I added the NVL for :new.name and :old.name. I couldnt find any examples of using NVL in the update triggers.. Please give me your suggestions. Thank you very much
Re: trigger issue [message #344026 is a reply to message #343692] Thu, 28 August 2008 11:26 Go to previous messageGo to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
I'm still not successful in finding how I can allow the null values for the fields.

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;


It didnt work. Please help me, I will appreciate it. THanks a lot.
Re: trigger issue [message #344052 is a reply to message #344026] Thu, 28 August 2008 12:39 Go to previous messageGo to next message
tyler_durden
Messages: 14
Registered: August 2008
Location: http://tinyurl.com/63fmwx
Junior Member
Quote:

I'm still not successful in finding how I can allow the null values for the fields.
...
It didnt work. Please help me, I will appreciate it. THanks a lot.



Remove the local variable and the SELECT statement from your trigger. You can use "USER" in your INSERT statement directly.

Put the "END" keyword at the end followed by the sql terminator.

Other than this, your INSERT statement could still fail due to datatype mismatch or something else. We can't say anything about that since we don't know the structure of, or any constraints, triggers etc. on the table - BILL itself.

tyler_durden
Re: trigger issue [message #344134 is a reply to message #344026] Thu, 28 August 2008 17:11 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
You need to be more specific than saying that it "didn't work". Did the trigger compile? Were you able to insert into the call table and update the call table? Did it insert some of the rows into the bill table? What were the update statements for which the rows were updated in call, but not inserted into bill? Please see the complete example below. Note that updates that result in errors, like the violation of the unique constraint below will not be inserted into the bill table or logged. Inserts into the bill table that fail, like the one below because the value of the data is wider than the column will be logged.

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>

Re: trigger issue [message #345274 is a reply to message #343692] Tue, 02 September 2008 15:12 Go to previous messageGo to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
Barbara,

Thank you for the example.

The trigger compiled fine and there are about 30 columns in CALL table I've created the update trigger condition for. When I update the fields in CALL table, almost all of them are getting inserted into BILL, except the fields with 'DATE' datatype.

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 the old value for call_dt is null and when I update the field with the date, it gets updated in CALL. But the record is not getting inserted in BILL table. If the old value for call_dt has a date, when I update the field with a new date, it gets updated in CALL and it gets inserted in BILL table.
Not sure how I can make this work.

Any help is appreciated. Thank you
Re: trigger issue [message #345292 is a reply to message #345274] Tue, 02 September 2008 17:24 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Try this in your trigger:

  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


[Updated on: Tue, 02 September 2008 17:25]

Report message to a moderator

Re: trigger issue [message #345439 is a reply to message #343692] Wed, 03 September 2008 10:38 Go to previous message
sant_new
Messages: 165
Registered: June 2008
Senior Member
This worked. Thanks a lot
Previous Topic: records within a quarter
Next Topic: Update/Select
Goto Forum:
  


Current Time: Wed Dec 07 09:01:25 CST 2016

Total time taken to generate the page: 0.26164 seconds