?
Solved

trigger not firing when trigger event occurs through an application

Posted on 2011-10-26
40
Medium Priority
?
1,285 Views
Last Modified: 2012-05-12
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;
0
Comment
Question by:sikyala
  • 18
  • 10
  • 8
  • +1
40 Comments
 
LVL 20

Expert Comment

by:gatorvip
ID: 37033230
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37033237
Not sure how that is possible.

This is just a guess right now but are you running a replicate environment?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37033311
is the trigger disabled?

select STATUS from all_triggers where trigger_name = 'PDF_FILE'
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Comment

by:sikyala
ID: 37033373
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
 

Author Comment

by:sikyala
ID: 37033377
yes the trigger is enabled
0
 
LVL 20

Expert Comment

by:gatorvip
ID: 37033655
Do you have the code for the web service application?
0
 

Author Comment

by:sikyala
ID: 37033664
still waiting for that info
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37033672
>>still waiting for that info

What info?

I'm still waiting for an answer to my question.
0
 

Author Comment

by:sikyala
ID: 37033689
i was responding to gatorvip I am not familiar with what a replicate environment is
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37033735
>>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
 

Author Comment

by:sikyala
ID: 37034600
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37034664
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 37035619
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
 

Author Comment

by:sikyala
ID: 37039055
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
 

Author Comment

by:sikyala
ID: 37039100
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
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 total points
ID: 37039190
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
 

Author Comment

by:sikyala
ID: 37039266
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 37039290
are you sure the trigger code is as you posted it above?
0
 

Author Comment

by:sikyala
ID: 37039319
yes it is
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37039379
what are the columns in table_A?
0
 

Author Comment

by:sikyala
ID: 37039397
FILE_PATH                                          VARCHAR2(200)
 RA_SIGNATURE                                       VARCHAR2(1)
 COR_SIGNATURE                                      VARCHAR2(1)
 STI_ID                                    NOT NULL NUMBER
 TYPE                                      NOT NULL NUMBER
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37039401
and B?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37039429
Also make sure there is only one trigger in one schema?

select owner from dba_triggers where trigger_name='PDF_FILE';
0
 

Author Comment

by:sikyala
ID: 37039464
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
 

Author Comment

by:sikyala
ID: 37039472
the type field in table B is not mapped to table A
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37039491
>>> update  table_B set file='Y'

you don't have a column called "file" in your table_B
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37039496
>>update  table_B set file='Y'

I don't see a file column in table_b?
0
 

Author Comment

by:sikyala
ID: 37039523
sorry name change it is pdf_exists in table_B
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37039541
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37039548
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
 

Author Comment

by:sikyala
ID: 37039598
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 37039627
is PDF_FILE the name of the trigger?  
0
 

Author Comment

by:sikyala
ID: 37039653
no it isn't the actual name I gave it. But I queried the right trigger
0
 

Author Comment

by:sikyala
ID: 37039657
and the results were the same as posted
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37039662
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
 
LVL 20

Expert Comment

by:gatorvip
ID: 37039669
>>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
 
LVL 20

Assisted Solution

by:gatorvip
gatorvip earned 1000 total points
ID: 37039766
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 37039812
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
 

Author Comment

by:sikyala
ID: 37063564
ok
0
 

Author Closing Comment

by:sikyala
ID: 37100313
the java developer had to look at his code and figure out the problem and solution
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses
Course of the Month16 days, 12 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question