?
Solved

Need help on Trigger Oracle 11g

Posted on 2014-08-07
20
Medium Priority
?
480 Views
Last Modified: 2014-08-11
Hi ,

I have an table called CUSTOMER_ORGANIZATION_PROFILE , When ever I insert an new record , I must get the other customer details from CUSTOMER_ORGANIZATION and CLIENT_ORGANIZATION tables and also the customer types from CUSTOMER_ORGANIZATION_PROFILE into table called TEMP_ORG_DATA.
When I update the CUSTOMER_ORGANIZATION_PROFILE  on IS_CUSTOMER , IS_CLIENT, IS_BOTH , then I need to update the IS_CUSTOMER , IS_CLIENT, IS_BOTH in TEMP_ORG_DATA.
When I delete the CUSTOMER_ORGANIZATION_PROFILE for an organization_profile_id , then I need to delete the entry in TEMP_ORG_DATA.

Below is the trigger code , the issue is whenever I insert the data , no records are inserted into temp_orgdata table , the same is with update and delete. Any help is really appreciated.


CREATE OR REPLACE TRIGGER CRM_ORG_PROFILE_TEST_TRG
AFTER INSERT OR  DELETE OR UPDATE OF IS_CUSTOMER,IS_CLIENT,IS_BOTH ,PRODUCT_GROUP_ID
ON CUSTOMER_ORGANIZATION_PROFILE
FOR EACH ROW
BEGIN      
       
         IF INSERTING THEN
         INSERT  INTO TEMP_ORGDATA
         Select
                              co.CST_ORGANIZATION_ID,
                    co.ORGANIZATION_ID,
                    co.PARENT_ORGANIZATION_ID,
                    :NEW.PRODUCT_GROUP_ID,
                              co.IS_INTERNAL,
                    co.OWNER_USER_ID,  
                    co.STATUS_ID,
                    ilo.CLIENT_ORG_NAME,
                    NULL DOMAIN,
                    co.LOCK_VERSION,
                    co.NAME_EN,
                    co.TAX_ID,
                    co.ADDRESS1,
                    co.ADDRESS2,
                    co.STATE,
                    co.CITY,
                    co.ZIP,
                    co.SUMMARY,
                    co.COUNTRY_ID,
                    co.REGION_ID,
                    :NEW.IS_CUSTOMER,
                              :NEW.IS_CLIENT,
                              :NEW.IS_BOTH                    
       FROM CST_ORGANIZATION co LEFT OUTER JOIN CLIENT_ORGANIZATION ilo on (co.ORGANIZATION_ID=Ilo.ORGANIZATION_ID)
       Where co.organization_id=:NEW.organization_id;
       
         ELSIF UPDATING THEN
        
         UPDATE TEMP_ORGDATA
         SET IS_CUSTOMER=:NEW.IS_CUSTOMER,
         IS_CLIENT =:NEW.IS_CLIENT,
         IS_BOTH=:NEW.IS_BOTH
         Where organization_id=:OLD.organization_id and product_group_id=:OLD.PRODUCT_GROUP_ID;

            ELSIF DELETING THEN
            
            DELETE FROM TEMP_ORGDATA WHERE organization_id=:OLD.organization_id and product_group_id=:OLD.PRODUCT_GROUP_ID;
        
         END IF;
        
       
END CRM_ORG_PROFILE_TEST_TRG;
/
0
Comment
Question by:sam_2012
  • 9
  • 8
  • 3
20 Comments
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 990 total points
ID: 40246045
what does your select return for a given ID?

SELECT co.cst_organization_id,
       co.organization_id,
       co.parent_organization_id,
       :new.product_group_id,
       co.is_internal,
       co.owner_user_id,
       co.status_id,
       ilo.client_org_name,
       NULL domain,
       co.lock_version,
       co.name_en,
       co.tax_id,
       co.address1,
       co.address2,
       co.state,
       co.city,
       co.zip,
       co.summary,
       co.country_id,
       co.region_id
  FROM cst_organization co
       LEFT OUTER JOIN client_organization ilo ON (co.organization_id = ilo.organization_id)
 WHERE co.organization_id = SOME_NEW_ID   ---- fill in whatever value you'd use for your new record
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 990 total points
ID: 40246043
what does your select return for a given ID?

SELECT co.cst_organization_id,
       co.organization_id,
       co.parent_organization_id,
       co.is_internal,
       co.owner_user_id,
       co.status_id,
       ilo.client_org_name,
       NULL domain,
       co.lock_version,
       co.name_en,
       co.tax_id,
       co.address1,
       co.address2,
       co.state,
       co.city,
       co.zip,
       co.summary,
       co.country_id,
       co.region_id
  FROM cst_organization co
       LEFT OUTER JOIN client_organization ilo ON (co.organization_id = ilo.organization_id)
 WHERE co.organization_id = SOME_NEW_ID   ---- fill in whatever value you'd use for your new record
0
 

Author Comment

by:sam_2012
ID: 40246137
I insert a new record in the CUSTOMER_ORGANIZATION_PROFILE  with values
CUSTOMER_PROFILE_ID , ORGANIZATION_ID , PRODUCT_GROUP_ID , IS_CUSTOMER, IS_CLIENT, IS_BOTH
101                                            234                                609                                 Y                      N                      N

Since Iam inserting this new record , I need an new record to be inserted into TEMP_ORGDATA,  For this I need the below data from
cst_organization and client_organization

cst_organization_id : 567
organization_id:234
parent_organization_id:987
 :new.product_group_id :609
is_internal :Y
owner_user_id:36578
status_id :2
client_org_name:Custom Sales Org
domain: NULL
lock_version:1
name_en:Custom Sales
tax_id :0
address1 : NULL
address2: NULL
state : TN
city:BLR
zip:23409
 summary:NULL
country_id:91
region_id:345
0
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!

 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 990 total points
ID: 40246157
If you run this query do you get the values above?


SELECT co.cst_organization_id,
       co.organization_id,
       co.parent_organization_id,
       co.is_internal,
       co.owner_user_id,
       co.status_id,
       ilo.client_org_name,
       NULL domain,
       co.lock_version,
       co.name_en,
       co.tax_id,
       co.address1,
       co.address2,
       co.state,
       co.city,
       co.zip,
       co.summary,
       co.country_id,
       co.region_id
  FROM cst_organization co
       LEFT OUTER JOIN client_organization ilo ON (co.organization_id = ilo.organization_id)
 WHERE co.organization_id = 234
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 990 total points
ID: 40246159
after you do the insert, are you doing a commit?  If not, you won't be able to see the new data in either table from other sessions
0
 

Author Comment

by:sam_2012
ID: 40246286
Yes iam commiting.
0
 

Author Comment

by:sam_2012
ID: 40246293
Yes i get the values
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 990 total points
ID: 40246300
is the trigger enabled?
0
 

Author Comment

by:sam_2012
ID: 40246308
Yes, It is enabled.
0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 510 total points
ID: 40246316
Likely just a typo but I've been bitten with similar table names before when doing development and I change object names during the process and forget to update ALL code.

Question references TEMP_ORG_DATA  as the table you need.

The trigger code uses TEMP_ORGDATA.
0
 

Author Comment

by:sam_2012
ID: 40246351
Sorry , its TEMP_ORGDATA.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 990 total points
ID: 40246387
the trigger syntax is valid - so it'll compile
the trigger is enabled - so it'll run
the query is valid - so the query doesn't error
the data is valid - so the query returns data

we're running out of options

are you getting errors that are being captured and hidden by an exception handler somewhere?

for example are there constraints being violated but you never see the error?
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 990 total points
ID: 40246390
the trigger syntax is valid - so it'll compile
the trigger is enabled - so it'll run
the query is valid - so the query doesn't error
the data is valid - so the query returns data

we're running out of options

are you getting errors that are being captured and hidden by an exception handler somewhere?

for example are there constraints being violated but you never see the error?
0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 510 total points
ID: 40246408
If you are doing the insert to test the trigger with sqlplus of similar tool, add a dbms_output as the very first line in the trigger.

Something like:
...
BEGIN      
   dbms_output.put_line('Made it here');    
          IF INSERTING THEN
...

Then set serveroutput on and try the insert.

This should help determine the trigger fired or not and help point you in the direction of where to look next.
0
 

Author Comment

by:sam_2012
ID: 40246526
Sorry , Its working. I was inserting into another table . Thanks for your guidance.
0
 

Author Comment

by:sam_2012
ID: 40246529
There are two tables temp_org_data and temp_orgdata. I was inserting into temp_org_data instead of temp_orgdata.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 990 total points
ID: 40246548
>>> I was inserting into temp_org_data instead of temp_orgdata.

I don't see how that's possible given the code you posted
0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 510 total points
ID: 40246546
As I mentioned, you are not alone!
I've been there. Done that.
0
 

Author Closing Comment

by:sam_2012
ID: 40254750
Thanks all for ur guidance.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40254754
why the penalty grade?
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses
Course of the Month16 days, 14 hours left to enroll

864 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