| desperate for trigger help! [message #39131] |
Tue, 18 June 2002 14:40  |
mrweatherbee
Messages: 13
Registered: June 2002
|
Junior Member |
|
|
Hi guys, i'm fairly new to oracle, so am having trouble writting this trigger and the syntax i need to be using. basically, i wanna create an incident number (format FYXX-XXXX) everytime that my table is updated. the first two XX's need to be the last two years of the fiscal year of the incident date. the last four digits are just a sequential number (i've created a sequence already that increments by 1).
so basically, what i need is that when a user enters inserts a record with an incident date into my table, i need to pull out the date, and get the two digit fiscal year, and then concatenate FY, the fiscal year, -, and the nextval in the sequence (with leading zeros) and then insert this concatenated incident # into a column in the same table. any help you guys could give me would be GREATLY appreciated...thank you SOOOO much!
|
|
|
|
| Re: desperate for trigger help! [message #39132 is a reply to message #39131] |
Tue, 18 June 2002 15:42  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Hopefully this will get you started:
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;
/ |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
