Kakateyudu Posted September 10, 2012 Report Posted September 10, 2012 [color=#ff0000][size=4]Kinda Vunna trigger READY_DATE value null nunchi not null ki change avinappude trigger fire avvali and that to notnull nunchi notnull ki change avvina avvakudadhu.... konchem help seyyandi vayya aa condition ela pettalo kinda trigger lo.......[/size][/color] [color=#333333]create or replace[/color] [color=#333333]TRIGGER TRI_P_KAKA_OUTBOUND AFTER INSERT OR DELETE OR UPDATE ON KAKA_OUTBOUND[/color] [color=#333333]REFERENCING NEW AS NEW OLD AS OLD[/color] [color=#333333]FOR EACH ROW[/color] [color=#333333]DECLARE[/color] [color=#333333]updating_key_fields EXCEPTION;[/color] [color=#333333]BEGIN[/color] [color=#333333]IF INSERTING THEN[/color] [color=#333333]INSERT INTO P_KAKA_OUTBOUND VALUES ([/color] [color=#333333]:NEW.TRANS_ID,[/color] [color=#333333]:NEW.OBJ_NAME,[/color] [color=#333333]:NEW.READY_DATE,[/color] [color=#333333]NULL,[/color] [color=#333333]P_KAKA_OUTBOUND_SEQ.NEXTVAL,[/color] [color=#333333]0,[/color] [color=#333333]SYSTIMESTAMP,[/color] [color=#333333]1,[/color] [color=#333333]NULL,[/color] [color=#333333]NULL,[/color] [color=#333333]'N',[/color] [color=#333333]-1,[/color] [color=#333333]NULL);[/color] [color=#333333]END IF;[/color] [color=#333333]IF UPDATING THEN[/color] [color=#333333]IF UPDATING('TRANS_ID') THEN[/color] [color=#333333]RAISE updating_key_fields;[/color] [color=#333333]END IF;[/color] [color=#333333]INSERT INTO NIX_STAGE_CGG.CEG_P_KAKA_OUTBOUND VALUES ([/color] [color=#333333]:OLD.TRANS_ID,[/color] [color=#333333]:NEW.OBJ_NAME,[/color] [color=#333333]:NEW.RECORD_ACTION_ID,[/color] [color=#333333]:NEW.READY_DATE,[/color] [color=#333333]NULL,[/color] [color=#333333]P_KAKA_OUTBOUND_SEQ.NEXTVAL,[/color] [color=#333333]0,[/color] [color=#333333]SYSTIMESTAMP,[/color] [color=#333333]4,[/color] [color=#333333]NULL,[/color] [color=#333333]NULL,[/color] [color=#333333]'N',[/color] [color=#333333]-1,[/color] [color=#333333]NULL);[/color] [color=#333333]END IF;[/color] [color=#333333]IF DELETING THEN[/color] [color=#333333]INSERT INTO NIX_STAGE_CGG.CEG_P_KAKA_OUTBOUND VALUES ([/color] [color=#333333]:OLD.TRANS_ID,[/color] [color=#333333]:OLD.OBJ_NAME,[/color] [color=#333333]:OLD.RECORD_ACTION_ID,[/color] [color=#333333]:OLD.READY_DATE,[/color] [color=#333333]NULL,[/color] [color=#333333]P_KAKA_OUTBOUND_SEQ.NEXTVAL,[/color] [color=#333333]0,[/color] [color=#333333]SYSTIMESTAMP,[/color] [color=#333333]3,[/color] [color=#333333]NULL,[/color] [color=#333333]NULL,[/color] [color=#333333]'N',[/color] [color=#333333]-1,[/color] [color=#333333]NULL);[/color] [color=#333333]END IF;[/color] [color=#333333]EXCEPTION[/color] [color=#333333]WHEN updating_key_fields THEN[/color] [color=#333333]raise_application_error(-20300, 'ActiveDB Error: cannot update key fields of source table.');[/color] [color=#333333]END TRI_P_KAKA_OUTBOUND;[/color] [color=#333333] [/color][color=#333333] [/color][color=#333333] [/color]
Kakateyudu Posted September 10, 2012 Author Report Posted September 10, 2012 [quote name='chelsea' timestamp='1347285194' post='1302454565'] LTT [/quote] thanks baa
cherlapalli_jailer Posted September 10, 2012 Report Posted September 10, 2012 Naaku triggers antha grip ledu kaani try this Before update/insert try this CREATE OR REPLACE TRIGGER [color=#333333][font=helvetica, arial, sans-serif]TRI_P_KAKA_OUTBOUND_B4[/font][/color] BEFORE UPDATE ON [color=#333333][font=helvetica, arial, sans-serif]P_KAKA_OUTBOUND [/font][/color] REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW IF (nvl(:OLD.date) != nvl(:NEW.date)) and : THEN -- do what ever u want here END IF;
cherlapalli_jailer Posted September 10, 2012 Report Posted September 10, 2012 before update check for null or what ever condition then proceed
padmasri123 Posted September 10, 2012 Report Posted September 10, 2012 create or replace TRIGGER TRI_P_KAKA_OUTBOUND AFTER INSERT OR DELETE OR UPDATE ON KAKA_OUTBOUND REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE updating_key_fields EXCEPTION; BEGIN IF (INSERTING and (:OLD.READY_DATE IS NULL AND :NEW.READY_DATE IS NOT NULL)) THEN INSERT INTO P_KAKA_OUTBOUND VALUES ( :NEW.TRANS_ID, :NEW.OBJ_NAME, :NEW.READY_DATE, NULL, P_KAKA_OUTBOUND_SEQ.NEXTVAL, 0, SYSTIMESTAMP, 1, NULL, NULL, 'N', -1, NULL); END IF; IF (UPDATING and(:OLD.READY_DATE IS NULL AND :NEW.READY_DATE IS NOT NULL)) THEN IF UPDATING('TRANS_ID') THEN RAISE updating_key_fields; END IF; INSERT INTO NIX_STAGE_CGG.CEG_P_KAKA_OUTBOUND VALUES ( :OLD.TRANS_ID, :NEW.OBJ_NAME, :NEW.RECORD_ACTION_ID, :NEW.READY_DATE, NULL, P_KAKA_OUTBOUND_SEQ.NEXTVAL, 0, SYSTIMESTAMP, 4, NULL, NULL, 'N', -1, NULL); END IF; IF DELETING THEN INSERT INTO NIX_STAGE_CGG.CEG_P_KAKA_OUTBOUND VALUES ( :OLD.TRANS_ID, :OLD.OBJ_NAME, :OLD.RECORD_ACTION_ID, :OLD.READY_DATE, NULL, P_KAKA_OUTBOUND_SEQ.NEXTVAL, 0, SYSTIMESTAMP, 3, NULL, NULL, 'N', -1, NULL); END IF; EXCEPTION WHEN updating_key_fields THEN raise_application_error(-20300, 'ActiveDB Error: cannot update key fields of source table.'); END TRI_P_KAKA_OUTBOUND; Iam not sure but give a try
cherlapalli_jailer Posted September 10, 2012 Report Posted September 10, 2012 [quote name='CHANAKYA' timestamp='1347287336' post='1302454662'] [/quote] sesinamu bayya Thread starter response chebitha further ga try cheyya vachu Kaani no response from him
Kakateyudu Posted September 10, 2012 Author Report Posted September 10, 2012 [quote name='cherlapalli_jailer' timestamp='1347287396' post='1302454670'] sesinamu bayya Thread starter response chebitha further ga try cheyya vachu Kaani no response from him [/quote] bhayya try sestunna meeting ki poyya will reply
Kakateyudu Posted September 10, 2012 Author Report Posted September 10, 2012 [quote name='cherlapalli_jailer' timestamp='1347285781' post='1302454589'] Naaku triggers antha grip ledu kaani try this Before update/insert try this CREATE OR REPLACE TRIGGER [color=#333333][font=helvetica, arial, sans-serif]TRI_P_KAKA_OUTBOUND_B4[/font][/color] BEFORE UPDATE ON [color=#333333][font=helvetica, arial, sans-serif]P_KAKA_OUTBOUND [/font][/color] REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW IF (nvl(:OLD.date) != nvl(:NEW.date)) and : THEN -- do what ever u want here END IF; [/quote] mama ready_date null to any value change ithe trigger avvali and non null to non null ithe avvakudadhu and paina not equal to ithe pettav mama condition nuvvu... if last value 2 and current value 3 ithe trigger avtadi kada kani manaki avvakudadhu.. non null to non null trigger avvakudadhu.. null to non null ye avvali
cherlapalli_jailer Posted September 10, 2012 Report Posted September 10, 2012 [quote name='Kakateeyudu©' timestamp='1347289053' post='1302454773'] mama ready_date null to any value change ithe trigger avvali and non null to non null ithe avvakudadhu and paina not equal to ithe pettav mama condition nuvvu... if last value 2 and current value 3 ithe trigger avtadi kada kani manaki avvakudadhu.. non null to non null trigger avvakudadhu.. null to non null ye avvali [/quote] ante Aa if condition mariste saripotundi more or less i think that is how u can proceed ani u can change like this OLD:ready_date==null && NEW:ready_date!=null Then proceed
padmasri123 Posted September 10, 2012 Report Posted September 10, 2012 [quote name='padmasri123' timestamp='1347285916' post='1302454595'] create or replace TRIGGER TRI_P_KAKA_OUTBOUND AFTER INSERT OR DELETE OR UPDATE ON KAKA_OUTBOUND REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE updating_key_fields EXCEPTION; BEGIN IF (INSERTING and (:OLD.READY_DATE IS NULL AND :NEW.READY_DATE IS NOT NULL)) THEN INSERT INTO P_KAKA_OUTBOUND VALUES ( :NEW.TRANS_ID, :NEW.OBJ_NAME, :NEW.READY_DATE, NULL, P_KAKA_OUTBOUND_SEQ.NEXTVAL, 0, SYSTIMESTAMP, 1, NULL, NULL, 'N', -1, NULL); END IF; IF (UPDATING and(:OLD.READY_DATE IS NULL AND :NEW.READY_DATE IS NOT NULL)) THEN IF UPDATING('TRANS_ID') THEN RAISE updating_key_fields; END IF; INSERT INTO NIX_STAGE_CGG.CEG_P_KAKA_OUTBOUND VALUES ( :OLD.TRANS_ID, :NEW.OBJ_NAME, :NEW.RECORD_ACTION_ID, :NEW.READY_DATE, NULL, P_KAKA_OUTBOUND_SEQ.NEXTVAL, 0, SYSTIMESTAMP, 4, NULL, NULL, 'N', -1, NULL); END IF; IF DELETING THEN INSERT INTO NIX_STAGE_CGG.CEG_P_KAKA_OUTBOUND VALUES ( :OLD.TRANS_ID, :OLD.OBJ_NAME, :OLD.RECORD_ACTION_ID, :OLD.READY_DATE, NULL, P_KAKA_OUTBOUND_SEQ.NEXTVAL, 0, SYSTIMESTAMP, 3, NULL, NULL, 'N', -1, NULL); END IF; EXCEPTION WHEN updating_key_fields THEN raise_application_error(-20300, 'ActiveDB Error: cannot update key fields of source table.'); END TRI_P_KAKA_OUTBOUND; Iam not sure but give a try [/quote] idi work avvaledha
karna11 Posted September 10, 2012 Report Posted September 10, 2012 antha expert kadhu but try sesthunna
Recommended Posts