Hi Team,
I need to create trigger for below requirement after insert or update for each row.. i have created the trigger that not works fine ,,,
can you please help for this requirement for trigger...
Table Creation:
create table verify_activity (p_country varchar2(400),p_bank(200),v_nbr number(15) v_edate date,v_xdate date,v_user varchar3(25));
**Req:**New records came verify v_edate greater than v_xdate in existing records in table then only allow otherwise reject ..
p_country p_bank v_nbr v_edate v_xdate v_user
US, BAW, 12345, 2022-07-10, 2022-08-11, SNK
US, BAW, 12345, 2022-08-15, 2022-12-11, SNK --Allowed
US, BAW, 12345, 2022-08-09, 2022-08-11, SNK --not Allowed
code
CREATE OR REPLACE TRIGGER WWT.verify_activity_trg AFTER
INSERT OR UPDATE OF p_country,p_bank,V_NBR,V_EDATE,V_XDATE,V_USER
ON WWT.verify_activity
FOR EACH ROW
declare
VV_NBR number(10);
v_Edate date;
BEGIN
SELECT REPL_GROUP_NBR,EXP_DATE INTO V_NBR,v_Edate FROM UDT_MFC_OVERRIDE_INTL WHERE REPL_GROUP_NBR=:NEW.REPL_GROUP_NBR;
if v_xdate >:OLD.EXP_DATE THEN
insert into UDT_MFC_OVERRIDE_INTL values (:new.p_country,:new.p_bank,:new.V_NBR,:new.V_EDATE,:new.V_XDATE,:new.V_USER);
end if;
end;