• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

how to write triggers

 
Ranch Hand
Posts: 70
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
hi all,
i want to write a trigger ona table. but dont know how to write it
reqts as follow:
table name: customer
column name: accountnumber which has to be unique, not null
i want a trigger such that on insert or update on the table, a value is appended to the accountnumber table which is a running serial number.
for example i enter accountnumber as "Toni" then the trigger should insert it as "Toni(1)".
it should read the last value and increment it by one.
for example in case i want to add another accountnumber "jack", it shud insert it as "jack(2)" as "toni(1)" is existing.
in short, increment the number in parenthesis and appended it to the accountnumber value.
Pls help me in this
Consider this as an SOS.. deadlines u know abt them ..rite..
Thanx in advance
Chhaya
 
ranger
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
First if this "Counter" is there for every record you should use a sequence.
Like
create sequence my_count_seq
start with 1
increment by 1
maxvalue 9999999
cache 20
nocycle
start with is the starting number
increment by is, well that is how it counts, it can me any number but 0 becauce then you don't have a counter but a constant
maxvalue is the max the counter goes to, if you do not include this in your statement max will be unlimited
cache is how many numbers Oracle will cache in the SGA, this means that getting the next number is a bit faster. However, if your server shutsdown or crashes, then those number will be lost. NOCACHE will not cache any numbers
NOCYCLE means that if it hits the MAX then it won't start over again, but that also means you can't get any more numbers.
To get the next value in the sequence, you use the my_counter_seq.nextval, you cna use the dual table to get it into a variable if you don't have it directly in the insert command.
So here is an example of an insert command
insert into my_table (account, value)
values
('jack(' || my_counter_seq.nextval || ')', 100).
Hope that helps.
Mark
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic