trigger not firing when trigger event occurs through an application

When i manually insert a record into a table then my trigger executes by updating another table. However, when a record is inserted into that same table through a web service application the trigger doesn't fire off. How can I fix this?

trigger text

create or replace trigger PDF_FILE

AFTER insert ON table_A
FOR EACH ROW
BEGIN
update  table_B set file='Y' where :new.sti_id=table_B.id and :new.type=752;
END;
/
 

END;
sikyalaSenior Database AdministratorAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
I've never seen Hibernate so please bear with the lack of knowledge.

From what I read here:
http://media.techtarget.com/tss/static/articles/content/Hibernate/IntroductionToHibernate.pdf

Hibernate has it's own SQL language against it's own objects.

Scanning the code you posted, it still looks like it is dealing with files not Oracle database calls.

Look in the rest of the code and look for something building/connecting to an Oracle database.
0
 
gatorvipCommented:
Your trigger should fire after every insert but it's possible you don't see that because of your filter:

>>where :new.sti_id=table_B.id and :new.type=752;

So if table_b doesn't contain the sti_id value, or a type of 752, table_b will not be updated.

How you "fix" it depends on what you want to accomplish.
0
 
slightwv (䄆 Netminder) Commented:
Not sure how that is possible.

This is just a guess right now but are you running a replicate environment?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sdstuberCommented:
is the trigger disabled?

select STATUS from all_triggers where trigger_name = 'PDF_FILE'
0
 
sikyalaSenior Database AdministratorAuthor Commented:
the two criteria are being met

when someone logs into the application and adds a file to a record in table_B then something inserts a record into table_A. But the trigger doesn't update table_B

I verified that the ID value in table_B is the same as sti_id in table_A and the type does have a value of 752
0
 
sikyalaSenior Database AdministratorAuthor Commented:
yes the trigger is enabled
0
 
gatorvipCommented:
Do you have the code for the web service application?
0
 
sikyalaSenior Database AdministratorAuthor Commented:
still waiting for that info
0
 
slightwv (䄆 Netminder) Commented:
>>still waiting for that info

What info?

I'm still waiting for an answer to my question.
0
 
sikyalaSenior Database AdministratorAuthor Commented:
i was responding to gatorvip I am not familiar with what a replicate environment is
0
 
slightwv (䄆 Netminder) Commented:
>>i was responding to gatorvip

OH, sorry.

>>I am not familiar with what a replicate environment is

Two databases on two servers syncing up the data between them.  I was grasping at straws that maybe the web service was talking to a different database than you were and the other database didn't have the triggers.

It is a stretch but if you can insert into table_b and see the insert into table_a and update in table_b, the web service has to pretty much follow the same rules.
0
 
sikyalaSenior Database AdministratorAuthor Commented:
This is what the java developer told me

The API call creates a table_A object and adds the column data with methods calls.  Before it does this it makes sure that the file is stored in a data handler and is able to be saved in the system.  
0
 
slightwv (䄆 Netminder) Commented:
Creating java data table objects and saving in the system does not sound like a database insert call.

You need to specifically address the database call.  If you can get that code it would help but do not post sensitive code.
0
 
sdstuberCommented:
I recommend adding some logging within the app code and the trigger itself.  Turn on auditing of inserts for that table too.

Either something you think should be happening really isn't.

Or,  you have a bug.

So, first, validate all assumptions (even the obvious ones).

If everything checks out and the trigger still doesn't fire,  then contact Oracle support
0
 
sikyalaSenior Database AdministratorAuthor Commented:
here is the code. I tried to mask as much as possible

tableA target = new tableA();
        RefDataDao rdd = new RefDataDao();        // Attempt to lookup the requested attachment type; throw exception if
        // not found.
        WsRefData targetType = rdd.getRefData(fileType);
       
        if (targetType == null)
        {
            logger.info("RefData for \"sf298\" not found!");
            // TODO make new TemsWsFault [DFT]
            targetType = rdd.getRefData("stiDoc");
           
             if (targetType == null)
             {

                 logger.info("RefData for \"stiDoc\" not found!");
             }
        }

        // Construct tableA ID from information provided.
        tableAid id = new tableA();
       
        id.setStiId(record.getId());
        id.setType(targetType.getId());
       
        // Construct STI Attachment.
        target.setId(id);
        target.setWsRefData(targetType);
        target.setTableB(record);
        //TODO: (YL) add additional checks for sf298 (ra/cor)
        target.setFilePath(filePath);
        logger.info("Setting RA_SIG: " + raSig);
        target.setRaSignature(raSig == true ? "Y" : "N");
        logger.info("Setting RA_SIG: " + corSig);
        target.setCorSignature(corSig == true ? "Y" : "N");

        // Begin Hibernate session/transaction.
        Session session = HibernateUtilities.getSessionFactory().getCurrentSession();
       
        try
        {
        session.beginTransaction();

        // Save the new attachment to the database.
        session.saveOrUpdate(target);

        // Close Hibernate transaction.
        session.getTransaction().commit();
        }
        catch (HibernateException e)
        {
            session.getTransaction().rollback();
            logger.error("Hibernate error!", e);
        }

        return target;


0
 
sikyalaSenior Database AdministratorAuthor Commented:
the hibernate

Hibernate:
    select
        wsstiattac_.STI_ID,
        wsstiattac_.TYPE,
        wsstiattac_.COR_SIGNATURE as COR3_5_,
        wsstiattac_.FILE_PATH as FILE4_5_,
        wsstiattac_.RA_SIGNATURE as RA5_5_
    from
        table_A wsstiattac_
    where
        wsstiattac_.STI_ID=?
        and wsstiattac_.TYPE=?
Hibernate:
    insert
    into
        table_A
        (COR_SIGNATURE, FILE_PATH, RA_SIGNATURE, STI_ID, TYPE)
    values
        (?, ?, ?, ?, ?)
0
 
sikyalaSenior Database AdministratorAuthor Commented:
the developer found this error in the logs

Caused by: java.sql.BatchUpdateException: ORA-01008: not all variables bound
ORA-06512: at "WS_TEST.PDF_FILE", line 2
ORA-04088: error during execution of trigger 'WS_TEST.PDF_FILE'

                at oracle.jdbc.driver.DatabaseError.throwBatchUpdateException(DatabaseError.java:345)
                at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10844)
                at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:1723)
                at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
                at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
                ... 29 more

0
 
sdstuberCommented:
are you sure the trigger code is as you posted it above?
0
 
sikyalaSenior Database AdministratorAuthor Commented:
yes it is
0
 
sdstuberCommented:
what are the columns in table_A?
0
 
sikyalaSenior Database AdministratorAuthor Commented:
FILE_PATH                                          VARCHAR2(200)
 RA_SIGNATURE                                       VARCHAR2(1)
 COR_SIGNATURE                                      VARCHAR2(1)
 STI_ID                                    NOT NULL NUMBER
 TYPE                                      NOT NULL NUMBER
0
 
sdstuberCommented:
and B?
0
 
slightwv (䄆 Netminder) Commented:
Also make sure there is only one trigger in one schema?

select owner from dba_triggers where trigger_name='PDF_FILE';
0
 
sikyalaSenior Database AdministratorAuthor Commented:
ID                                        NOT NULL NUMBER
 PDF_EXISTS                                         VARCHAR2(1)
 PDF_NAME                                           VARCHAR2(200)
 TITLE                                              VARCHAR2(2000)
 SUBJECTKEYWORDS                                    VARCHAR2(4000)
 BCO_REPORT_ID                                      VARCHAR2(20)
 MAC_REPORT_ID                                      VARCHAR2(20)
 CATEGORY                                           VARCHAR2(70)
 TYPE                                               VARCHAR2(70)
0
 
sikyalaSenior Database AdministratorAuthor Commented:
the type field in table B is not mapped to table A
0
 
sdstuberCommented:
>>> update  table_B set file='Y'

you don't have a column called "file" in your table_B
0
 
slightwv (䄆 Netminder) Commented:
>>update  table_B set file='Y'

I don't see a file column in table_b?
0
 
sikyalaSenior Database AdministratorAuthor Commented:
sorry name change it is pdf_exists in table_B
0
 
sdstuberCommented:
so,  the code in the trigger above is NOT what you are actually running?   http:#37039290


what are the results of this...

select * from dba_errors where name = 'PDF_FILE'
0
 
slightwv (䄆 Netminder) Commented:
Then the trigger code posted 'isn't' what is running?

Tripple check the code from the database itself.

From sqlplus:
SQL> set long 1000000
SQL> select trigger_body from user_triggers where trigger_name='PDF_TEST';
0
 
sikyalaSenior Database AdministratorAuthor Commented:
the code is the same I just changed the table names and field names
select * from dba_errors where name = 'PDF_FILE'
no rows selected
0
 
sdstuberCommented:
is PDF_FILE the name of the trigger?  
0
 
sikyalaSenior Database AdministratorAuthor Commented:
no it isn't the actual name I gave it. But I queried the right trigger
0
 
sikyalaSenior Database AdministratorAuthor Commented:
and the results were the same as posted
0
 
sdstuberCommented:
also, have you added the other logging and auditting as mentioned above?

if so, what are the results?

if not, what other logs do you have available to track what's going on?
0
 
gatorvipCommented:
>>id.setStiId(record.getId());
>>        id.setType(targetType.getId());

Can you debug these two lines to see what exactly is sent to the Hibernate call?
0
 
gatorvipCommented:
Also, since I don't know Hibernate, it's entirely possible I'm misunderstanding this piece of code...

************
tableA target = new tableA();
...
// Construct tableA ID from information provided.
tableAid id = new tableA();
       
id.setStiId(record.getId());
id.setType(targetType.getId());

// Construct STI Attachment.
target.setId(id);
target.setWsRefData(targetType);
target.setTableB(record);

************

That, to me, looks like there are two instances of tableA initialized, then the first instance has its id field set to the second instance.  So, while tableAid has its STI_ID and TYPE (which are the PK for tableA) fields set explicitly, target does not (unless "setId" does it in the background). At the end, Hibernate uses "target" to insert, not "tableAid." So, again, check to see what values are actually sent here.
0
 
sdstuberCommented:
if you have auditting turned on and put logging into the trigger you'll be able to capture exactly what, when, how the table is inserted and when the trigger fires
0
 
sikyalaSenior Database AdministratorAuthor Commented:
ok
0
 
sikyalaSenior Database AdministratorAuthor Commented:
the java developer had to look at his code and figure out the problem and solution
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.