Home » SQL & PL/SQL » SQL & PL/SQL » Oracle 9i Before insert trigger is not working
Oracle 9i Before insert trigger is not working [message #334045] Tue, 15 July 2008 05:19 Go to next message
alibsd
Messages: 9
Registered: November 2007
Junior Member
We have a "before insert" trigger on an Oracle 9i for inserting
empty_blob() into a NOT NULL column of a table. Until today
everything was fine and it was OK, but suddenly, that trigger
stops working and it seems that it is not even fired.

I restart the database (and even the server machine), but
nothing happened. Below is the body of the trigger,
LETTER_CONTENT is the NOT NULL column. If I try to insert
a new record into tbl_oa_letter_text with NULL value for
letter_content, rationally empty_blob() must be assigned to
letter_content by that trigger, so no constraint would
not be violated, but I got the "ORA-01400: Cannot insert NULL
into ..." error.

CREATE OR REPLACE TRIGGER TRG_BIR_LTXT_BLOB
BEFORE INSERT
ON TBL_OA_LETTER_TEXT 
FOR EACH ROW
begin
  :new.LETTER_CONTENT:=empty_blob();
end;
/


Any ideas why this trigger does not work.
Regards

Re: Oracle 9i Before insert trigger is not working [message #334050 is a reply to message #334045] Tue, 15 July 2008 05:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Without knowing what you ACTUALLY did noone can answer.
Copy and paste what you did when you have the error.
Also check if your trigger is enable.

Regards
Michel
Re: Oracle 9i Before insert trigger is not working [message #334057 is a reply to message #334045] Tue, 15 July 2008 06:05 Go to previous messageGo to next message
alibsd
Messages: 9
Registered: November 2007
Junior Member
Hi Michel, you are right

The table is
CREATE TABLE TBL_OA_LETTER_TEXT
(
  PK_LETTER_TEXT           NUMBER(12)           NOT NULL,
  FK_LTTR                  NUMBER(12)           NOT NULL,
  LETTER_CONTENT           BLOB                 NOT NULL,
  EXT                      VARCHAR2(5 BYTE),
  FORMAT                   VARCHAR2(6 BYTE)     NOT NULL,
  FK_DOT                   NUMBER(12),
  NAME                     VARCHAR2(256 BYTE)   NOT NULL,
  LETTER_TEMPLATE_CONTENT  BLOB,
  CONTENT_SIZE             NUMBER(10)           NOT NULL,
  CHANGE_DATE              DATE                 NOT NULL,
  FK_ARCHIVE               NUMBER(12)
)


And what I run is:
SQL> INSERT INTO tbl_oa_letter_text
(pk_letter_text, fk_lttr, format, name, content_size)
VALUES (999900002396, 999900004235, 'blah', 'blah blah', 3333);

INSERT INTO tbl_oa_letter_text (pk_letter_text, fk_lttr, format, name, content_size) VALUES (999900002396, 999900004235,
'df', 'fffff', 3333)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into
("OAPRD"."TBL_OA_LETTER_TEXT"."LETTER_CONTENT")


Thanks
Re: Oracle 9i Before insert trigger is not working [message #334060 is a reply to message #334057] Tue, 15 July 2008 06:18 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:
Also check if your trigger is enable

You missed to provide the above information which @Michael has requested. Please find the query to do the same.

Could you post the output of the following query ?

select owner_name, trigger_name, trigger_type, table_name, status
from all_triggers where owner = 'OAPRD' and table_name = 'TBL_OA_LETTER_TEXT';

Regards

Raj

[Updated on: Tue, 15 July 2008 06:20]

Report message to a moderator

Re: Oracle 9i Before insert trigger is not working [message #334062 is a reply to message #334045] Tue, 15 July 2008 06:28 Go to previous messageGo to next message
alibsd
Messages: 9
Registered: November 2007
Junior Member
OWNER                          TRIGGER_NAME                   TRIGGER_TYPE
------------------------------ ------------------------------ ----------------
TABLE_NAME                     STATUS
------------------------------ --------
OAPRD                          TRG_AIDR_LTXT_SET_LTTR_ATTACH  AFTER EACH ROW
TBL_OA_LETTER_TEXT             ENABLED

OAPRD                          TRG_BD_LETTER_TEXT_ARCHIVE     BEFORE EACH ROW
TBL_OA_LETTER_TEXT             ENABLED

OAPRD                          TRG_BIR_LTXT_BLOB              BEFORE EACH ROW
TBL_OA_LETTER_TEXT             ENABLED


OWNER                          TRIGGER_NAME                   TRIGGER_TYPE
------------------------------ ------------------------------ ----------------
TABLE_NAME                     STATUS
------------------------------ --------
OAPRD                          TRG_BIU_LTTR_TXT_ST_DT         BEFORE EACH ROW
TBL_OA_LETTER_TEXT             ENABLED

OAPRD                          TRG_BIU_LTTR_TXT_ST_UP         BEFORE EACH ROW
TBL_OA_LETTER_TEXT             ENABLED
Re: Oracle 9i Before insert trigger is not working [message #334066 is a reply to message #334062] Tue, 15 July 2008 06:43 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why do you have four before row triggers?

What are the triggering events for the others? Disable all the other triggers and see what happens.
Re: Oracle 9i Before insert trigger is not working [message #334070 is a reply to message #334062] Tue, 15 July 2008 06:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
Until today
everything was fine and it was OK, but suddenly, that trigger
stops working and it seems that it is not even fired


Sorry, but no - that's not the way things work.
The trigger did not just stop working - somebody changed something in your database, or in your application.

Can you post the trigger code, and also the results of this query, which will let us know of the triggers have changed lately.
select t.trigger_name, o.created, o.last_ddl_time, o.timestamp, o.status
from   all_triggers t,
       all_objects  o
where  t.owner = 'OAPRD' 
and    t.table_name = 'TBL_OA_LETTER_TEXT';
and    o.owner = t.owner
and    o.object_name = t.trigger_name;
Re: Oracle 9i Before insert trigger is not working [message #334077 is a reply to message #334070] Tue, 15 July 2008 06:56 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
JRowbottom wrote on Tue, 15 July 2008 13:47
Quote:
Until today
everything was fine and it was OK, but suddenly, that trigger
stops working and it seems that it is not even fired


Sorry, but no - that's not the way things work.
The trigger did not just stop working - somebody changed something in your database, or in your application.
Laughing First thread I look at more closely in a few weeks and I'm pleased to see that things haven't changed a bit.
Re: Oracle 9i Before insert trigger is not working [message #334091 is a reply to message #334045] Tue, 15 July 2008 07:34 Go to previous messageGo to next message
alibsd
Messages: 9
Registered: November 2007
Junior Member
Here is the result of
select t.trigger_name, o.created, o.last_ddl_time, o.timestamp, o.status
from   all_triggers t,
       all_objects  o
where  t.owner = 'OAPRD' 
and    t.table_name = 'TBL_OA_LETTER_TEXT';
and    o.owner = t.owner
and    o.object_name = t.trigger_name;


TRIGGER_NAME                   CREATED   LAST_DDL_ TIMESTAMP           STATUS
------------------------------ --------- --------- ------------------- -------
TRG_AIDR_LTXT_SET_LTTR_ATTACH  06-MAR-07 15-JUL-08 2008-07-15:13:32:22 VALID
TRG_BD_LETTER_TEXT_ARCHIVE     14-JUL-08 15-JUL-08 2008-07-15:13:35:59 VALID
TRG_BIR_LTXT_BLOB              15-JUL-08 15-JUL-08 2008-07-15:16:13:27 VALID
TRG_BIU_LTTR_TXT_ST_DT         08-JAN-08 15-JUL-08 2008-07-15:13:32:22 VALID
TRG_BIU_LTTR_TXT_ST_UP         12-MAR-08 15-JUL-08 2008-07-15:13:32:22 VALID
Re: Oracle 9i Before insert trigger is not working [message #334099 is a reply to message #334045] Tue, 15 July 2008 07:44 Go to previous message
alibsd
Messages: 9
Registered: November 2007
Junior Member
SOLVED.....

By disabling TRG_BIU_LTTR_TXT_ST_DT, trigger TRG_BIR_LTXT_BLOB
works. It seems that these two triggers have some conflict
with each other, although they update two different columns.

I'm going to merge them into one trigger.

Thanks all of you for your help Smile
Previous Topic: Multilevel subtotal of columns
Next Topic: DB Link
Goto Forum:
  


Current Time: Sun Dec 04 06:51:16 CST 2016

Total time taken to generate the page: 0.82948 seconds