Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Update Trigger that will Not fire on certain fields (merged)
Oracle Update Trigger that will Not fire on certain fields (merged) [message #377202] Sun, 21 December 2008 13:10 Go to next message
ora_newbie
Messages: 19
Registered: August 2008
Junior Member
I received a new requirement and that is the trigger should not fire when changes are only to the AT_ID and/or AT_PR_TYP fields.

I am not sure how to go about this new challenge... searches mention COLUMNS_UPDATE function as well as an if statement like if not (update (AT_ID) OR UPDATE (AT_PR_TYP)) THEN
begin. But no matter my efforts, I am not sure which is more appropriate. Could someone provide hint for me to kick off this effort and I'll make an effort to find the solution, many thanks.

My table structure looks as follows:
CREATE TABLE A 
    (a_id NUMBER NOT NULL,
    a_nm VARCHAR2(200) NOT NULL, 
    a_acrynm_tx VARCHAR2(50), 
    a_ct_cd VARCHAR2(3), 
    g_id NUMBER,
    a_ex_dsc_tx CLOB, 
    a_dt DATE, 
    a_c_dt DATE, 
    typ_cd VARCHAR2(3),
    ar_typ_tx VARCHAR2(50),
    clsfctn_tx VARCHAR2(35), 
    a_d_id VARCHAR2(7), 
    a_d_cd VARCHAR2(8), 
    ar_nm VARCHAR2(50), 
    a_up_rmk_tx CLOB,
    a_rcd_zz DATE, 
    as_rcd_zz DATE, 
    a_dsc_tx CLOB,
    at_id varchar2(32),
    at_pr_typ NUMBER,  
    CONSTRAINT a_id_pk PRIMARY KEY (a_id));


CREATE TABLE S_A 
    (s_a_id NUMBER NOT NULL,
    a_id NUMBER NOT NULL, 
    a_nm VARCHAR2(200) NOT NULL, 
    a_acrynm_tx VARCHAR2(50), 
    a_ct_cd VARCHAR2(3), 
    g_id NUMBER,
    a_ex_dsc_tx CLOB, 
    a_dt DATE, 
    a_c_dt DATE, 
    typ_cd VARCHAR2(3),
    ar_typ_tx VARCHAR2(50),
    clsfctn_tx VARCHAR2(35), 
    a_d_id VARCHAR2(7), 
    a_d_cd VARCHAR2(8), 
    ar_nm VARCHAR2(50), 
    a_up_rmk_tx CLOB,
    a_rcd_zz DATE, 
    as_rcd_zz DATE, 
    a_dsc_tx CLOB,
    at_id varchar2(32),
    at_pr_typ NUMBER,
    a_chng_cd VARCHAR2(1),
    a_mod_c_dt DATE,
    a_d_src varchar2(5),
    a_chng_dsc_tx VARCHAR2(500),
    a_cnfrm_rcpt_flg varchar2(1),    
    CONSTRAINT s_a_id_pk PRIMARY KEY (s_a_id)) 
;


create or replace trigger S_U_A_ID_SEQ_TR 
after update on A 
for each row 

declare
  a_chng_cd_v 	s_a.a_chng_cd%type;
  a_mod_c_dt_v 	s_a.a_mod_c_dt%type;
  a_d_src_v		s_a.a_d_src%type;
  a_chng_dsc_tx_v  	s_a.a_chng_dsc_tx%type;
  a_cnfrm_rcpt_flg_v	s_a.a_cnfrm_rcpt_flg%type;

begin
 if updating then
	a_chng_cd_v  :='U';
        a_mod_c_dt_v    :=sysdate;
	a_d_src_v    :='XYZ';
        a_chng_dsc_tx_v := '';	
        a_cnfrm_rcpt_flg_v :='N';	

		if nvl(to_char(:old.A_ID),'null')!=nvl(to_char(:new.A_ID), 'null') then
		a_chng_dsc_tx_v := a_chng_dsc_tx_v || 'A_ID, ';
		end if;	
		if nvl (:old.A_NM, 'null')!=nvl(:new.A_NM, 'null') then
 		a_chng_dsc_tx_v := a_chng_dsc_tx_v || 'A_NM, ';
		end if;
		if nvl (:old.A_ACRYNM_TX, 'null')!=nvl(:new.A_ACRYNM_TX, 'null') then
 		a_chng_dsc_tx_v := a_chng_dsc_tx_v || 'A_ACRYNM_TX, ';
		end if;
		if nvl (:old.A_CT_CD,'null')!=nvl(:new.A_CT_CD,'null') then
 		a_chng_dsc_tx_v := a_chng_dsc_tx_v || 'A_CT_CD, ';
		end if;
		if nvl(to_char(:old.G_ID),'null')!=nvl(to_char(:new.G_ID), 'null') then
		a_chng_dsc_tx_v := a_chng_dsc_tx_v || 'G_ID, ';
		end if;	
		if nvl (:old.A_EX_DSC_TX,'null')!=nvl(:new.A_EX_DSC_TX,'null') then
 		a_chng_dsc_tx_v := a_chng_dsc_tx_v || 'A_EX_DSC_TX, ';
		end if;
     		if nvl (to_char(:old.A_DT,'MM/DD/YYYY'),'null')!=nvl(to_char(:new.A_DT,'MM/DD/YYYY'),'null') then
		a_chng_dsc_tx_v := a_chng_dsc_tx_v || 'A_DT, ';
		end if;
		if nvl (to_char(:old.A_C_DT,'MM/DD/YYYY'),'null')!=nvl(to_char(:new.A_C_DT,'MM/DD/YYYY'),'null') then
 		a_chng_dsc_tx_v := a_chng_dsc_tx_v || 'A_C_DT, ';
		end if;
		if nvl (:old.TYP_CD,'null')!=nvl(:new.TYP_CD,'null') then
 		a_chng_dsc_tx_v := a_chng_dsc_tx_v || 'TYP_CD, ';
		end if;
		if nvl (:old.AR_TYP_TX,'null')!=nvl(:new.AR_TYP_TX,'null') then
 		a_chng_dsc_tx_v := a_chng_dsc_tx_v || 'AR_TYP_TX, ';
		end if;
		if nvl (:old.CLSFCTN_TX,'null')!=nvl(:new.CLSFCTN_TX,'null') then
 		a_chng_dsc_tx_v := a_chng_dsc_tx_v || 'CLSFCTN_TX, ';
		end if;
		if nvl (:old.A_ID,'null')!=nvl(:new.A_ID,'null') then
 		a_chng_dsc_tx_v := a_chng_dsc_tx_v || 'A_ID, ';
		end if;
		if nvl (:old.A_D_CD,'null')!=nvl(:new.A_D_CD,'null') then
 		a_chng_dsc_tx_v := a_chng_dsc_tx_v || 'A_D_CD, ';
		end if;
		if nvl (:old.AR_NM,'null')!=nvl(:new.AR_NM,'null') then
 		a_chng_dsc_tx_v := a_chng_dsc_tx_v || 'AR_NM, ';
		end if;
		if nvl (:old.A_UP_RMK_TX,'null')!=nvl(:new.A_UP_RMK_TX,'null') then
 		a_chng_dsc_tx_v := a_chng_dsc_tx_v || 'A_UP_RMK_TX, ';
		end if;
		if nvl (to_char(:old.A_RCD_ZZ,'MM/DD/YYYY'),'null')!=nvl(to_char(:new.A_RCD_ZZ,'MM/DD/YYYY'),'null') then
 		a_chng_dsc_tx_v := a_chng_dsc_tx_v || 'A_RCD_ZZ, ';
		end if;
		if nvl (to_char(:old.AS_RCD_ZZ,'MM/DD/YYYY'),'null')!=nvl (to_char(:new.AS_RCD_ZZ,'MM/DD/YYYY'),'null') then
 		a_chng_dsc_tx_v := a_chng_dsc_tx_v || 'AS_RCD_ZZ, ';
		end if;
		if nvl (:old.A_DSC_TX,'null')!=nvl(:new.A_DSC_TX,'null') then
 		a_chng_dsc_tx_v := a_chng_dsc_tx_v || 'A_DSC_TX, ';
		end if;
		if nvl (:old.AT_ID,'null')!=nvl(:new.AT_ID, 'null') then
 		a_chng_dsc_tx_v := a_chng_dsc_tx_v || 'AT_ID, ';
		end if;
		if nvl(to_char(:old.AT_PR_TYP),'null')!=nvl(to_char(:new.AT_PR_TYP),'null') then
		a_chng_dsc_tx_v := a_chng_dsc_tx_v || 'AT_PR_TYP, ';
		end if;
  insert into s_a	
	Values 
	(S_A_ID_SEQ.NEXTVAL,
	:new.A_ID,
	:new.A_NM,
	:new.A_ACRYNM_TX,
	:new.A_CT_CD,
	:new.G_ID,
	:new.A_EX_DSC_TX,
	:new.A_DT,
	:new.A_C_DT,
	:new.TYP_CD,
	:new.AR_TYP_TX,
	:new.CLSFCTN_TX,
	:new.A_D_ID,
	:new.A_D_CD,
	:new.AR_NM,
	:new.A_UP_RMK_TX,
	:new.A_RCD_ZZ,
	:new.AS_RCD_ZZ,
	:new.A_DSC_TX,
	a_chng_cd_v,
        a_mod_c_dt_v,
	a_d_src_v,
	a_chng_dsc_tx_v,
	a_cnfrm_rcpt_flg_v);

end if;
END;
Re: Oracle Update Trigger that will Not fire on certain fields (merged) [message #377204 is a reply to message #377202] Sun, 21 December 2008 13:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at CREATE TRIGGER syntax, it contains a WHEN clause that allow you to speficy when the trigger fires.

And don't multipost your questions.

Regards
Michel
Re: Oracle Update Trigger that will Not fire on certain fields (merged) [message #377206 is a reply to message #377202] Sun, 21 December 2008 14:42 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
When you search for syntax, it is best to search Oracle online documentation and Oracle sites or include the word Oracle if searching from Google or some such thing. Otherwise, you get syntax that may work on other databases, such as MySQL or SQL Server, but not Oracle. For example, the column_update syntax does not exist in Oracle.

There are various ways that you can either specify that the trigger should only fire under certain conditions or evaluate whether certain conditions are true or not within the trigger. I have shown three of them below, in bold capitals. If you don't want the trigger to fire when only certain columns are updated, then you need to list all the rest of the columns for which you want an update to occur. The example below assumes that you want the trigger to fire when either col1 or col2 of some table a is updated, but not when only other columns are updated.

In this situation, my preference would probably be to use the first method, "after update of col1, col2 on a", because it evaluates it at the earliest possible point, saving the time of executing more code and you don't have to write extra code to handle the null values. However, the first and third methods will fire if there is an update to that column that does not actually change the value. So, if you want to handle that, then you will need to compare new and old values using either when or if and using nvl to handle null values.

create or replace trigger s_u_a_id_seq_tr
after update OF COL1, COL2 on a
for each row
WHEN (NEW.col1 <> OLD.col1 OR NEW.col2 <> OLD.col2)
begin
if updating ('COL1') OR updating ('COL2') then
...
end if;
end s_u_a_id_seq_tr;
/
Re: Oracle Update Trigger that will Not fire on certain fields (merged) [message #377559 is a reply to message #377206] Tue, 23 December 2008 08:38 Go to previous message
ora_newbie
Messages: 19
Registered: August 2008
Junior Member
Thanks Barbara!

I had (to my surprise) came up with the second solution based off of Michel guidance, thank you Michel.

option #2
Quote:
WHEN (NEW.col1 <> OLD.col1 OR NEW.col2 <> OLD.col2)


However, I did try the other two and noted that the first one...
option #1
Quote:
create or replace trigger s_u_a_id_seq_tr
after update OF COL1, COL2 on a
for each row


will not work if a column's data type is of clob, it seems you cannot add a column with this data type in the update of clause statement.

The last suggestion #3 works as expected

Quote:
begin
if updating ('COL1') OR updating ('COL2') then


Michel and Barbara, my sincere thanks for your help (again) and that you generously share your knowledge. Happy holidays to you and your family!
Previous Topic: Table join with distinct records
Next Topic: How can I use Full Outer Join More Than Two Table.
Goto Forum:
  


Current Time: Sun Dec 04 00:24:42 CST 2016

Total time taken to generate the page: 0.03749 seconds