After trigger select from other tables using joins

5 pts.
Tags:
LEFT OUTER JOIN statement
Oracle
Oracle triggers
Triggers
Is it possible to create a trigger on table A if there is an insert, update, or delete then fire the trigger but in addition to the changes in table A I want to capture values from table B,C,D,E.... even if those records had no action applied to them. I tried declaring variables from the other tables and then referencing them latter. The trigger complies fine but does not do anything. I'am I going about this the wrong way? If the select statement is removed the triggers fires for all actions but of course does not contain values form other tables. By the way I'm a newbie, so if the syntax makes no sense that's the reason. create or replace TRIGGER PMA_EH_ENTITY AFTER INSERT OR UPDATE OR DELETE ON EH_ENTITY FOR EACH ROW DECLARE VRecordAction P_TABLE.RECORDACTION%TYPE := NULL; VRecordName P_TABLE.RECORDNAME%TYPE := NULL; VPKey P_TABLE.PKEY%TYPE := NULL; VRecordData P_TABLE.RECORDDATA%TYPE := NULL; EP_JUVENILE_FLAG "EH_ENTITY_PERSON"."JUVENILE_FLAG"%TYPE := NULL; EP_ENTITY_ID "EH_PERSON_IDENTITY"."ENTITY_ID"%TYPE := NULL; PI_NAME_FAMILY "EH_PERSON_IDENTITY"."NAME_FAMILY"%TYPE := NULL; G_CODE_GENDER "EH_ENTITY_GENDER"."CODE_GENDER"%TYPE := NULL; RC_CODE_RACE "EH_ENTITY_RACE"."CODE_RACE"%TYPE := NULL; R_BOOKING_ID "EH_BOOKING"."BOOKING_ID"%TYPE := NULL; R_START_DATE "EH_BOOKING"."START_DATE"%TYPE := NULL; BEGIN VRecordName := 'PMA'; IF INSERTING OR UPDATING THEN IF INSERTING THEN VRecordAction := 'I'; ELSE VRecordAction := 'U'; END IF; SELECT "A1".ENTITY_ID,AC.JUVENILE_FLAG,A8.NAME_FAMILY,A2.CODE_GENDER,A5.CODE_RACE,R.BOOKING_ID,R.START_DATE INTO EP_ENTITY_ID,EP_JUVENILE_FLAG,PI_NAME_FAMILY,G_CODE_GENDER,RC_CODE_RACE,R_BOOKING_ID,R_START_DATE FROM "EH_ENTITY" "A1" LEFT OUTER JOIN EH_ENTITY_GENDER A2 ON (A1.ENTITY_ID = A2.ENTITY_ID) LEFT OUTER JOIN EH_ENTITY_PERSON AC ON (A1.ENTITY_ID = AC.ENTITY_ID) LEFT OUTER JOIN EH_ENTITY_RACE A5 ON (A1.ENTITY_ID = A5.ENTITY_ID) LEFT OUTER JOIN EH_PERSON_IDENTITY A8 ON (A1.ENTITY_ID = A8.ENTITY_ID) RIGHT JOIN EH_BOOKING R ON (A1.ENTITY_ID=R.ENTITY_ID); VPKey := '<EP_ENTITY_ID>'||(TO_CHAR(:new.ENTITY_ID))||'</EP_ENTITY_ID>'; VRecordData := REPLACE(REPLACE(REPLACE('<ENTITY_ID>' || REPLACE(REPLACE(RTRIM(LTRIM(:new.ENTITY_ID)),'>',''),'<','') || '</ENTITY_ID>' || '<EP_JUVENILE_FLAG>' || REPLACE(REPLACE(RTRIM(LTRIM(EP_JUVENILE_FLAG)),'>',' '),'<',' ') || '</EP_JUVENILE_FLAG>' || '<PI_NAME_FAMILY>' || REPLACE(REPLACE(RTRIM(LTRIM(PI_NAME_FAMILY)),'>',' '),'<',' ') || '</PI_NAME_FAMILY>' || '<G_CODE_GENDER>' || REPLACE(REPLACE(RTRIM(LTRIM(G_CODE_GENDER)),'>',' '),'<',' ') || '</G_CODE_GENDER>' || '<RC_CODE_RACE>' || REPLACE(REPLACE(RTRIM(LTRIM(RC_CODE_RACE)),'>',' '),'<',' ') || '</RC_CODE_RACE>' || '<R_BOOKING_ID>' || REPLACE(REPLACE(RTRIM(LTRIM(R_BOOKING_ID)),'>',' '),'<',' ') || '</R_BOOKING_ID>' || '<R_START_DATE>' || REPLACE(REPLACE(RTRIM(LTRIM(R_START_DATE)),'>',' '),'<',' ') || '</R_START_DATE>' ,chr(13),''),chr(10),''),chr(9),' '); ELSIF DELETING THEN VRecordAction := 'D'; VPKey := '<EP_ENTITY_ID>'||(TO_CHAR(:old.ENTITY_ID))||'</EP_ENTITY_ID>'; VRecordData := REPLACE(REPLACE(REPLACE('<ENTITY_ID>' || REPLACE(REPLACE(RTRIM(LTRIM(:old.ENTITY_ID)),'>',''),'<','') || '</ENTITY_ID>' || '<EP_JUVENILE_FLAG>' || REPLACE(REPLACE(RTRIM(LTRIM(EP_JUVENILE_FLAG)),'>',' '),'<',' ') || '</EP_JUVENILE_FLAG>' || '<PI_NAME_FAMILY>' || REPLACE(REPLACE(RTRIM(LTRIM(PI_NAME_FAMILY)),'>',' '),'<',' ') || '</PI_NAME_FAMILY>' || '<G_CODE_GENDER>' || REPLACE(REPLACE(RTRIM(LTRIM(G_CODE_GENDER)),'>',' '),'<',' ') || '</G_CODE_GENDER>' || '<RC_CODE_RACE>' || REPLACE(REPLACE(RTRIM(LTRIM(RC_CODE_RACE)),'>',' '),'<',' ') || '</RC_CODE_RACE>' || '<R_BOOKING_ID>' || REPLACE(REPLACE(RTRIM(LTRIM(R_BOOKING_ID)),'>',' '),'<',' ') || '</R_BOOKING_ID>' || '<R_START_DATE>' || REPLACE(REPLACE(RTRIM(LTRIM(R_START_DATE)),'>',' '),'<',' ') || '</R_START_DATE>' ,chr(13),''),chr(10),''),chr(9),' '); END IF; INSERT INTO P_TABLE (RECORDACTION, RECORDNAME, PKEY, RECORDDATA) VALUES (VRecordAction, VRecordName, VPKey, VRecordData); EXCEPTION -- If an exception is raised, exit gracefully WHEN OTHERS THEN NULL; END;

Answer Wiki

Thanks. We'll let you know when a new response is added.

The most likely reason your trigger is doing jack is because of this:

EXCEPTION
– If an exception is raised, exit gracefully
WHEN OTHERS
THEN
NULL;

Do not take this as an offence, but this is considered by the oracle community to be the stupidest piece of code anyone can write. Many people do it. I see it all the time and it whenever there is a problem this plays a role and here it is:

An error is happening in your trigger, but you cannot see the error because this makes it go away. Thus you have not clue there was an error and and cannot fix it.

Take this out of your trigger and let the trigger fail. That way you will see the error message for what is preventing the trigger from finishing all the work. Then you can fix it and be happy.

Good luck, Kevin

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following