Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!f14g2000cwb.googlegroups.com!not-for-mail
From: "sb" <sonali.bhavsar@gmail.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: SQL joins on multiple tables in a trigger
Date: 12 Dec 2005 18:34:33 -0800
Organization: http://groups.google.com
Lines: 58
Message-ID: <1134441273.275586.123360@f14g2000cwb.googlegroups.com>
References: <1134355785.155390.116950@o13g2000cwo.googlegroups.com>
NNTP-Posting-Host: 68.42.0.35
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1134441278 8666 127.0.0.1 (13 Dec 2005 02:34:38 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Tue, 13 Dec 2005 02:34:38 +0000 (UTC)
In-Reply-To: <1134355785.155390.116950@o13g2000cwo.googlegroups.com>
User-Agent: G2/0.2
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322),gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: f14g2000cwb.googlegroups.com; posting-host=68.42.0.35;
   posting-account=2A-16w0AAACI-pKIPaszodyh35Yo49Pj
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:257619

The following trigger doe not work right, its trigger implementation as
I am modifying an exisitng trigger to add new fields to the triggered
table.
The new values bein added are profile_id and profile_desc.

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;