Home » SQL & PL/SQL » SQL & PL/SQL » Before and After trigger issue (Oracle 11.1 on Windows)
Before and After trigger issue [message #448874] Thu, 25 March 2010 08:29 Go to next message
The Other Guy
Messages: 9
Registered: March 2010
Junior Member
I have searched the world through and can't find any other reference to similar behavior.

I have a row level BEFORE INSERT trigger on a table, that assigns the next Sequence value to the key field - no problem here. I also have a row level AFTER INSERT trigger (auto generated by code for auditing/logging purposes) that wants to use, amongst other field values, this key value assigned in the BI trigger. At the beginning of the AFTER INSERT trigger :NEW.KEY is null!!! Later on in the same trigger the value is suddenly available. Any one else experienced this?

Re: Before and After trigger issue [message #448876 is a reply to message #448874] Thu, 25 March 2010 08:31 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

Post your trigger script.
Re: Before and After trigger issue [message #448880 is a reply to message #448874] Thu, 25 March 2010 08:37 Go to previous messageGo to next message
The Other Guy
Messages: 9
Registered: March 2010
Junior Member
create or replace trigger Persons_TI01
before insert on Persons
for each row
begin
  :NEW.OBJ_ID := SQ_Persons.NEXTVAL;
end;

Create Or Replace Trigger PERSONS_TI00
After Insert on PERSONS
for each row
/*********************************************************************/
/******   DO NOT EDIT ******     TRACE AUTO GENERATED TRIGGER   ******/
/*********************************************************************/
declare audId Number(16) := 0;
begin
  --Log INSERT detail of INITIALS
  INST1.TRACER.Log(audId, 54,:new.OBJ_ID, 1,256, null,sys.anydata.convertVARCHAR2(:new.INITIALS));
  --Log INSERT detail of NAME
  INST1.TRACER.Log(audId, 54,:new.OBJ_ID, 1,257, null,sys.anydata.convertVARCHAR2(:new.NAME));
  --Log INSERT detail of NATURE_TYPE_ID
  INST1.TRACER.Log(audId, 54,:new.OBJ_ID, 1,254, null,sys.anydata.convertNUMBER(:new.NATURE_TYPE_ID));
  --Log INSERT detail of RECORD_DATE
  INST1.TRACER.Log(audId, 54,:new.OBJ_ID, 1,259, null,sys.anydata.convertTIMESTAMP(:new.RECORD_DATE));
  --Log INSERT detail of SOURCE_ID
  INST1.TRACER.Log(audId, 54,:new.OBJ_ID, 1,253, null,sys.anydata.convertNUMBER(:new.SOURCE_ID));
  --Log INSERT detail of SURNAME
  INST1.TRACER.Log(audId, 54,:new.OBJ_ID, 1,258, null,sys.anydata.convertVARCHAR2(:new.SURNAME));
  --Log INSERT detail of TITLE_ID
  INST1.TRACER.Log(audId, 54,:new.OBJ_ID, 1,255, null,sys.anydata.convertNUMBER(:new.TITLE_ID));
end;





CM: added [code] tags, please do so yourself next time, see the orafaq forum guide if you're not sure how.

[Updated on: Thu, 25 March 2010 08:39] by Moderator

Report message to a moderator

Re: Before and After trigger issue [message #448882 is a reply to message #448874] Thu, 25 March 2010 08:44 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't see any issue:
SQL> create table t (id integer, val integer);

Table created.

SQL> create sequence s;

Sequence created.

SQL> create or replace trigger b_i before insert on t for each row
  2  begin
  3    select s.nextval into :new.id from dual;
  4  end;
  5  /

Trigger created.

SQL> create or replace trigger a_i after insert on t for each row
  2  declare 
  3    v1 integer := :new.id;
  4    v2 integer;
  5  begin
  6    v2 := :new.id;
  7    dbms_output.put_line('v1='||v1||' - v2='||v2);
  8  end;
  9  /

Trigger created.

SQL> insert into t (val) values (100) ;
v1=1 - v2=1

1 row created.

SQL> /
v1=2 - v2=2

1 row created.

SQL> /
v1=3 - v2=3

1 row created.

Post evidence you have a problem.

Regards
Michel
Re: Before and After trigger issue [message #448886 is a reply to message #448874] Thu, 25 March 2010 08:59 Go to previous messageGo to next message
The Other Guy
Messages: 9
Registered: March 2010
Junior Member
The stored procedure being called (INST1.TRACER.Log) inserts the values into a table/column TRACE_LOGS.RECORD_KEY. As you can see from the code, the 3rd parameter is the :NEW.OBJ_ID assigned in the BEFORE INSERT trigger. All inserts fail, except the last one (TITLE_ID) with error : ORA-01400: cannot insert NULL into ("INST1"."TRACE_LOGS"."RECORD_KEY")
Re: Before and After trigger issue [message #448887 is a reply to message #448874] Thu, 25 March 2010 09:03 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
First thing to do is put some dbms_outputs in the after trigger like Michel did and see if the value is visible that way.
Re: Before and After trigger issue [message #448888 is a reply to message #448886] Thu, 25 March 2010 09:18 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How could you know the last one does not failt when the a previous one already failed as if it fails then it does not go to the next one?

Post the code of your INST1.TRACER.Log procedure as this is this one that generates the error.
And post the WHOLE session as I did not your interpretation of what happens in it.

Regards
Michel

[Updated on: Thu, 25 March 2010 09:19]

Report message to a moderator

Re: Before and After trigger issue [message #448965 is a reply to message #448874] Fri, 26 March 2010 01:14 Go to previous messageGo to next message
The Other Guy
Messages: 9
Registered: March 2010
Junior Member
I know the last one passes because the Log procedure has a fall-through as you will see in the code below. I'll be doing some manual tests with output today (as suggested by Michel and cookiemonster) as this code has only been called from a .NET app so far.
  procedure Log(logId     in out Number,
                tableId   number,
                recordKey number,
                logAction number,
                columnId  number,
                oldVal    anydata,
                newVal    anydata)
  as
  begin
    --if logId > 0, it means its detail only, so skip adding the header
    if logId = 0 then
      insert into TRACE_HEADER(ACTION_DATE, USER_NAME, TABLE_ID, RECORD_KEY,
        ACTION, MACHINE_NAME)
      values (current_timestamp, sys_context('USERENV', 'OS_USER'), tableId,
        recordKey, logAction, sys_context('USERENV', 'HOST'))
      returning OBJ_ID into logId;
    end if;

	--always insert detail
    insert into TRACE_DETAIL(OBJ_ID, COLUMN_ID, OLD_VALUE, NEW_VALUE)
    values (logId, columnId, oldVal, newVal);

  exception
    when OTHERS then --specifically do not roll back, add error only to ErrLog table
      LogErr(PACKAGE_NAME, 'Log ID', 1, Tracer.TRACE_LEVEL_ERROR, sqlerrm);
  end Log;
  

[EDITED by LF: applied [code] tags]

[Updated on: Fri, 26 March 2010 02:26] by Moderator

Report message to a moderator

Re: Before and After trigger issue [message #448973 is a reply to message #448965] Fri, 26 March 2010 01:41 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do it in SQL*Plus and post the whole session.
Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Before and After trigger issue [message #448978 is a reply to message #448973] Fri, 26 March 2010 01:46 Go to previous message
The Other Guy
Messages: 9
Registered: March 2010
Junior Member
My apologies for the ugly code I posted, it is formatted properly and indented properly too, but copy and pasting it didn't preserve my formatting and due to a couple of issues I needed to attend to this morning I just posted without previewing.
Previous Topic: Convert Object return type columns
Next Topic: SPOOL question: Adding quotes around field only if a comma exists within?
Goto Forum:
  


Current Time: Tue Sep 27 19:33:20 CDT 2016

Total time taken to generate the page: 0.13203 seconds