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;