Win a copy of Escape Velocity: Better Metrics for Agile Teams this week in the Agile and Other Processes forum!
  • 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 ...
  • Campbell Ritchie
  • Liutauras Vilda
  • Tim Cooke
  • Paul Clapham
  • Jeanne Boyarsky
  • Ron McLeod
  • Frank Carver
  • Junilu Lacar
Saloon Keepers:
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Al Hobbs
  • Carey Brown
  • Piet Souris
  • Frits Walraven
  • fred rosenberger

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
Posts: 17347
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.
create sequence my_count_seq
start with 1
increment by 1
maxvalue 9999999
cache 20
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)
('jack(' || my_counter_seq.nextval || ')', 100).
Hope that helps.
How do they get the deer to cross at the signs? Or to read this tiny ad?
the value of filler advertising in 2021
    Bookmark Topic Watch Topic
  • New Topic