Need help on Trigger Oracle 11g

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;
/
sam_2012Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
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
sdstuberCommented:
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
sam_2012Author Commented:
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
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:
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
sdstuberCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sam_2012Author Commented:
Yes iam commiting.
0
sam_2012Author Commented:
Yes i get the values
0
sdstuberCommented:
is the trigger enabled?
0
sam_2012Author Commented:
Yes, It is enabled.
0
slightwv (䄆 Netminder) Commented:
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
sam_2012Author Commented:
Sorry , its TEMP_ORGDATA.
0
sdstuberCommented:
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
sdstuberCommented:
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
slightwv (䄆 Netminder) Commented:
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
sam_2012Author Commented:
Sorry , Its working. I was inserting into another table . Thanks for your guidance.
0
sam_2012Author Commented:
There are two tables temp_org_data and temp_orgdata. I was inserting into temp_org_data instead of temp_orgdata.
0
sdstuberCommented:
>>> 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
slightwv (䄆 Netminder) Commented:
As I mentioned, you are not alone!
I've been there. Done that.
0
sam_2012Author Commented:
Thanks all for ur guidance.
0
sdstuberCommented:
why the penalty grade?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

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.