Oracle 9i Before insert trigger is not working [message #334045] |
Tue, 15 July 2008 05:19  |
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 #334057 is a reply to message #334045] |
Tue, 15 July 2008 06:05   |
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   |
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   |
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 #334070 is a reply to message #334062] |
Tue, 15 July 2008 06:47   |
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 #334091 is a reply to message #334045] |
Tue, 15 July 2008 07:34   |
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  |
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
|
|
|