| more trigger help, please [message #2061] |
Wed, 19 June 2002 11:40  |
mrweatherbee
Messages: 13
Registered: June 2002
|
Junior Member |
|
|
hi guys,
thank you SOOOO much for some earlier advice on my trigger...it worked like a charm! :) i just need help with one more syntax thing w/ my trigger...basically the example i followed was todd's example:
Hopefully this will get you started (no variables needed):
create or replace trigger trg_name
before insert on t
for each row
begin
if :new.incident_date is not null then
select 'FY' || to_char(:new.incident_date, 'yy') || '-' || lpad(my_sequence.nextval, 4, '0') into :new.incident_number
from dual; end if;
end;
/
so basically the last piece i need is that the two digit year that is concatenated changes based on the fiscal year....so basically this code works fine if incident_date is from jan 1 to the end of the june, (the month is b/w jan and june) but if the incident date is from july 1 to the end of the year (the month is b/w july and dec), the two digit year needs to be the next year (YY + 1). hopefully that makes sense. anyways, if someone could let me know where i need to put this if statement, it would be a HUGE help...thank you guys so much again!
mr.w
|
|
|
|
| Re: more trigger help, please [message #2064 is a reply to message #2061] |
Wed, 19 June 2002 14:15  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
See if this handles the fiscal year for you:
select 'FY' || to_char(decode(sign(to_char(:new.incident_date, 'mm') - 7), -1, :new.incident_date, add_months(:new.incident_date, 12)), 'yy') || '-' || lpad(my_sequence.nextval, 4, '0')
into :new.incident_number
from dual; |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
