| desparate for some trigger help! [message #2039] |
Tue, 18 June 2002 14:27  |
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: desparate for some trigger help! [message #2041 is a reply to message #2039] |
Tue, 18 June 2002 15:02  |
Amit Chauhan
Messages: 74
Registered: July 1999
|
Member |
|
|
Hi,
Try this :
DESC my_table
incident_number VARCHAR2(10)
incident_date DATE
CREATE OR REPLACE TRIGGER my_trigger
BEFORE INSERT OR UPDATE ON my_table
FOR EACH ROW
temp VARCHAR2(10) := '';
yy VARCHAR2(2) := '';
num NUMBER;
BEGIN
yy := TO_CHAR (:new.incident_date, 'YY');
SELECT my_sequence.NEXTVAL INTO num FROM DUAL;
temp := 'FY' || yy || '-' || TO_CHAR(num, '0000');
:new.incident_number := temp;
END;
/
Hope that helps.
Thanks
Amit
|
|
|
|
| Re: desparate for some trigger help! [message #2043 is a reply to message #2039] |
Tue, 18 June 2002 15:45 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
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;
/ |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
