create or replace trigger Trigger_OGNotice_Insert before insert on OutgoingNotice for each row declare newid OutgoingNotice.OutgoingNoticeId%TYPE; curMonthMaxId OutgoingNotice.OutgoingNoticeId%TYPE; begin select MAX(og.OutgoingNoticeId) into curMonthMaxId from OutgoingNotice og where TO_CHAR(og.datecreated, 'YYYY-MM') = TO_CHAR(:new.datecreated, 'YYYY-MM') and og.CompanyId = :new.CompanyId group by CompanyId ,TO_CHAR(og.datecreated, 'YYYY-MM');
if curMonthMaxId is null then newid := CONCAT(TO_CHAR(:new.datecreated, 'YYYY-MM'), '-1'); else newid := CONCAT(:new.CompanyId,'-'||TO_CHAR(:new.datecreated, 'YYYY-MM')||'-'||TO_CHAR(TO_NUMBER(SUBSTR(curMonthMaxId, 11))+1)); end if;
before insert on OutgoingNotice
for each row
declare
newid OutgoingNotice.OutgoingNoticeId%TYPE;
curMonthMaxId OutgoingNotice.OutgoingNoticeId%TYPE;
begin
select MAX(og.OutgoingNoticeId) into curMonthMaxId from OutgoingNotice og
where TO_CHAR(og.datecreated, 'YYYY-MM') = TO_CHAR(:new.datecreated, 'YYYY-MM') and og.CompanyId = :new.CompanyId
group by CompanyId ,TO_CHAR(og.datecreated, 'YYYY-MM');
if curMonthMaxId is null then
newid := CONCAT(TO_CHAR(:new.datecreated, 'YYYY-MM'), '-1');
else
newid := CONCAT(:new.CompanyId,'-'||TO_CHAR(:new.datecreated, 'YYYY-MM')||'-'||TO_CHAR(TO_NUMBER(SUBSTR(curMonthMaxId, 11))+1));
end if;
:new.OutgoingNoticeId := newid;
end;