Tags: contains, database, intv_code, ltgt, mysql, old, oracle, row, sql, table, trigger, update, values, writing
Database Trigger old and new values
8,425 words with 8 Comments; publish: Tue, 12 Feb 2008 07:08:00 GMT; (25062.50, « »)
Hi,
I am writing a database update trigger on table A for each row trigger which contains WHEN
(NEW.INTV_CODE <> OLD.INTV_CODE)
In this same trigger i have to join trigger with other tables(B,C) and i have to selecting some columns of base table A which is not include in WHEN condition
What i have to select :new.column or :old.column of old tables to reference values which are not updated.
I think this information is enough to resolve problem. Please feel free to ask more explanations
http://oracle.itags.org/q_oracle_51441.html
All Comments
Leave a comment...
- 8 Comments
- >>In this same trigger i have to join trigger with other tables(B,C) and i have to selecting some columns of base table A which is not include in WHEN condition
Dont understand how you can join trigger to table?
>>What i have to select :new.column or :old.column of old tables to reference values which are not updated.
You can declare memory variable in declare part of trigger and select data into those variables accordingly and then you can use those variables whereever you need.
HTH
Ghulam
#1; Fri, 22 Feb 2008 23:39:00 GMT
- I mean, how to reference column of base table which is not in WHEN condition... that is other columns wont effect by the trigger.
I have to select :old.column or :new.column because there will no new values for these columns
Please suggest.
Atif
#2; Fri, 22 Feb 2008 23:40:00 GMT
- Atif,
Can you post the code of your trigger?
Please post in right format using [ pre ] and [ /pre ] before and after your code.
Thanks
Ghulam
#3; Fri, 22 Feb 2008 23:41:00 GMT
- I didn't Get It..Can you send the code example to better understand your problem#4; Fri, 22 Feb 2008 23:42:00 GMT
- CREATE OR REPLACE TRIGGER ST_AUR_SARADAP
AFTER UPDATE ON SATURN.SARADAP REFERENCING
NEW AS NEW OLD AS OLD FOR EACH ROW WHEN
(NEW.SARADAP_INTV_CODE <> OLD.SARADAP_INTV_CODE)
DECLARE
rec_swrofcc swrofcc%rowtype;
offer_state varchar2(2);
BEGIN
select sarappd_pidm , swraoid_ocasid , :old.saradap_majr_code_1 ,
:old.saradap_camp_code , decode(sarappd_apdc_code, 'A', 'A', 'AA', 'A', sarappd_apdc_code) ,
swraocc_sequence_number, saradap_full_part_ind ,
decode(saradap_intv_code,'Y',SWBRULE_ATTEND_HIGH_SCH_DATE, 'N', SWBRULE_NOT_ATND_HIGH_SCH_DATE) ,
decode(saradap_admt_code,11, 'Y','N') , swbrule_of_exp_date_hard , swbrule_of_exp_date_soft ,
swraocc_semester_level , swraocc_start_date ,
decode(substr(saradap_term_code_entry,-2),01,'FA', 02, 'WI', 03, 'SU')||substr(saradap_term_code_entry, 3, 2) ,
'OC' , decode(sarappd_apdc_code, 'A','I','U') ,
to_char(sysdate, 'YYYYMMDD') , to_char(sysdate, 'hhmiss')||'00' , 'GBTC'
into
rec_swrofcc.swrofcc_pidm, rec_swrofcc.swrofcc_ocas_appl_number, rec_swrofcc.swrofcc_program_code,
rec_swrofcc.swrofcc_campus_code, offer_state,
rec_swrofcc.swrofcc_sequence_number, rec_swrofcc.swrofcc_full_part_time,
rec_swrofcc.swrofcc_offer_active_date,
rec_swrofcc.swrofcc_late_admit, rec_swrofcc.swrofcc_offer_exp_hard_date, rec_swrofcc.swrofcc_offer_exp_soft_date,
rec_swrofcc.swrofcc_semester_level_offered, rec_swrofcc.swrofcc_start_date,
rec_swrofcc.swrofcc_term_identifier, rec_swrofcc.swrofcc_transaction_code, rec_swrofcc.swrofcc_transaction_type,
rec_swrofcc.swrofcc_transaction_date, rec_swrofcc.swrofcc_transaction_time, rec_swrofcc.swrofcc_college_code
from
sarappd, swraoid, saradap, swraocc, swbrule
where
swraocc_transaction_date = (SELECT max(swraocc_transaction_date)
FROM swraocc
WHERE swraocc_application_number = swraoid_ocasid
and swraocc_term_code_entry = saradap_term_code_entry
and swraocc_program_code = saradap_majr_code_1) and
sarappd_pidm in (49210,527888,49415,47564) and
sarappd_appl_no = saradap_appl_no and
sarappd_term_code_entry = saradap_term_code_entry and
sarappd_pidm = swraoid_pidm and
saradap_pidm = swraoid_pidm and
sarappd_apdc_code = 'A' and
sarappd_seq_no = (select max(sarappd_seq_no) from sarappd where
sarappd_pidm = saradap_pidm and
sarappd_term_code_entry = saradap_term_code_entry and
sarappd_appl_no = saradap_appl_no) and
swbrule_term_code = saradap_term_code_entry and
swbrule_majr_code = saradap_majr_code_1 and
SWRAOCC_APPLICATION_NUMBER = swraoid_ocasid
and swraocc_program_code = saradap_majr_code_1;
END;
#5; Fri, 22 Feb 2008 23:43:00 GMT
- First of all if you select from the same table as where you are updating you will get mutating trigger error
Otherwise
create or replace trigger ST_AUR_SARADAP
after update on SATURN.SARADAP
for each row
declare
-- local variables here
begin
IF :NEW.SARADAP_INTV_CODE <> :OLD.SARADAP_INTV_CODE
--Do your processing where they are Not equal
ELSE
--Do your processing where they are equal
END IF;
end ST_AUR_SARADAP;
#6; Fri, 22 Feb 2008 23:44:00 GMT
- This is my finalquery i m not including the base table in from clause.
Columns old and new values will be same, when those column not include in WHEN clause of trigger.
I mean in select clause i can use either :old.saradap_intv_code or :new.saradap_intv_code it does not matter ?
CREATE OR REPLACE TRIGGER ST_AUR_SARADAP
AFTER UPDATE ON SATURN.SARADAP REFERENCING
NEW AS NEW OLD AS OLD FOR EACH ROW WHEN
(NEW.SARADAP_INTV_CODE <> OLD.SARADAP_INTV_CODE)
DECLARE
rec_swrofcc swrofcc%rowtype;
offer_state varchar2(2);
BEGIN
select :old.saradap_majr_code_1 ,
:old.saradap_camp_code , decode(sarappd_apdc_code, 'A', 'A', 'AA', 'A', sarappd_apdc_code) ,
swraocc_sequence_number, :old.saradap_full_part_ind ,
decode(:old.saradap_intv_code,'Y',SWBRULE_ATTEND_HIGH_SCH_DATE, 'N', SWBRULE_NOT_ATND_HIGH_SCH_DATE) ,
decode(:old.saradap_admt_code,11, 'Y','N') , swbrule_of_exp_date_hard , swbrule_of_exp_date_soft ,
swraocc_semester_level , swraocc_start_date ,
decode(substr(:old.saradap_term_code_entry,-2),01,'FA', 02, 'WI', 03, 'SU')||substr(:old.saradap_term_code_entry, 3, 2) ,
'OC' , 'U' , to_char(sysdate, 'YYYYMMDD') , to_char(sysdate, 'hhmiss')||'00'
into
rec_swrofcc.swrofcc_pidm, rec_swrofcc.swrofcc_ocas_appl_number, rec_swrofcc.swrofcc_program_code,
rec_swrofcc.swrofcc_campus_code, offer_state,
rec_swrofcc.swrofcc_sequence_number, rec_swrofcc.swrofcc_full_part_time,
rec_swrofcc.swrofcc_offer_active_date,
rec_swrofcc.swrofcc_late_admit, rec_swrofcc.swrofcc_offer_exp_hard_date, rec_swrofcc.swrofcc_offer_exp_soft_date,
rec_swrofcc.swrofcc_semester_level_offered, rec_swrofcc.swrofcc_start_date,
rec_swrofcc.swrofcc_term_identifier, rec_swrofcc.swrofcc_transaction_code, rec_swrofcc.swrofcc_transaction_type,
rec_swrofcc.swrofcc_transaction_date, rec_swrofcc.swrofcc_transaction_time, rec_swrofcc.swrofcc_college_code
from
sarappd, swraoid, swraocc, swbrule
where
swraocc_transaction_date = (SELECT max(swraocc_transaction_date)
FROM swraocc
WHERE swraocc_application_number = swraoid_ocasid
and swraocc_term_code_entry = :old.saradap_term_code_entry
and swraocc_program_code = :old.saradap_majr_code_1) and
--sarappd_pidm in (49210,527888,49415,47564) and
sarappd_appl_no = :old.saradap_appl_no and
sarappd_term_code_entry = :old.saradap_term_code_entry and
sarappd_pidm = swraoid_pidm and
:old.saradap_pidm = swraoid_pidm and
sarappd_apdc_code in ('A', 'AA') and
sarappd_seq_no = (select max(sarappd_seq_no) from sarappd where
sarappd_pidm = :old.saradap_pidm and
sarappd_term_code_entry = :old.saradap_term_code_entry and
sarappd_appl_no = :old.saradap_appl_no) and
swbrule_term_code = :old.saradap_term_code_entry and
swbrule_majr_code = :old.saradap_majr_code_1 and
SWRAOCC_APPLICATION_NUMBER = swraoid_ocasid
and swraocc_program_code = :old.saradap_majr_code_1;
END;
#7; Fri, 22 Feb 2008 23:45:00 GMT
- So will that work
create or replace trigger ST_AUR_SARADAP
after update on SATURN.SARADAP
for each row
declare
-- local variables here
begin
IF :NEW.SARADAP_INTV_CODE <> :OLD.SARADAP_INTV_CODE
--Do your processing where they are Not equal
ELSE
--Do your processing where they are equal
END IF;
end ST_AUR_SARADAP
#8; Fri, 22 Feb 2008 23:46:00 GMT