Re: How to add joins in triggers and cascading triggers

Thanks,
This is how
CREATE OR REPLACE TRIGGER "BOL_DEV1".TG_BOL_CDUSER_PROFILE_AR_MOD4 AFTER INSERT OR UPDATE ON CDUSER_PROFILE --and AFTER INSERT OR UPDATE of profile_id OR DELETE ON tb_bol_user_profile
FOR EACH ROW
DECLARE

  v_authlvl            AUTH_LEVEL.AUTH_LEVEL%TYPE;
  v_old_authlvl        AUTH_LEVEL.AUTH_LEVEL%TYPE;
  v_addpriv   		   CDUSER_PROFILE.ADD_PRIV%TYPE;
  v_old_addpriv  	   CDUSER_PROFILE.ADD_PRIV%TYPE;
  v_Profile_Id 		   TB_BOL_USER_PROFILE.PROFILE_ID%Type;
  v_Profile_Desc 	   TB_BOL_PSPROFILE.DESCRIPTION%Type;

CURSOR c_AllData IS
  SELECT A.PROFILE_ID v_Profile_Id, B.DESCRIPTION v_Profile_Desc   FROM TB_BOL_USER_PROFILE A, TB_BOL_PSPROFILE B   WHERE A.USER_ID = :new.USER_ID

                  AND A.PROFILE_ID = B.PROFILE_ID; BEGIN

	 IF INSERTING THEN
	 	 IF (v_Profile_Id = NULL) then
  	 	 	FOR v_AllData IN c_AllData LOOP
		 	 INSERT INTO TB_BOL_USER_AUDIT
   		  	 VALUES(:new.user_id, :new.auth_level, NULL, sysdate,
          	 :new.last_name, :new.first_name, :new.add_priv, NULL,
		 	 :new.audit_trail_id, 'a', :new.department, :new.manager,
			 :new.phone_no, :new.email, :new.cost_center, :new.company_title,
		     NULL, NULL, NULL, NULL);
		     end loop;
	 	 Else
	 	   FOR v_AllData IN c_AllData LOOP
		   INSERT INTO TB_BOL_USER_AUDIT
   		   VALUES(:new.user_id, :new.auth_level, NULL, sysdate,
           :new.last_name, :new.first_name, :new.add_priv, NULL,


		   :new.audit_trail_id, 'a', :new.department, :new.manager,

		   :new.phone_no, :new.email, :new.cost_center, :new.company_title,

		    v_AllData.v_profile_id, v_AllData.v_profile_id,
v_AllData.v_Profile_Desc,
		   v_AllData.v_Profile_Desc);
		 end loop;
		 end if;
	 end if;

EXCEPTION
    WHEN OTHERS THEN
        raise_application_error(-20001, 'User Audit could not be created.');

        pr_log_errors('tg_bol_cduser_profile_ar_mod');

END TG_BOL_CDUSER_PROFILE_AR_MOD4;
my SQL looks,ifyou could give me some more insights. Received on Mon Dec 12 2005 - 20:37:02 CST