In one of my recent projects, I had to insert into a table where one column had to be unique and auto generated. Further I also had to maintain the data so that it was in serial with previously inserted data.
In oracle this can be accomplished by the combined use of Trigger and Sequence. I am going to describe the technique in this post.
Sequence: A sequence can generate unique number. User can limit the minimum and maximum value and also the starting value of the generated number. In other words a sequence is like a unique number generator whose nature can be controlled by a user. One sequence can be maintained per table so that we can provide unique and serial id for each table.
Trigger: A trigger can be fired before insert, update or delete operations in any table. This can be designed to help apply business logic while using data manipulations using insert, update and delete plsql programming.
Create a sequence
CREATE SEQUENCE SEQ_EMPLOYEE INCREMENT BY 1 START WITH 10000 MINVALUE 10000 MAXVALUE 99999 NOCYCLE NOCACHE NOORDER;
Create a Trigger on Employee Table
CREATE OR REPLACE TRIGGER TRIGGER_EMPLOYEE
BEFORE INSERT ON EMPLOYEE
FOR EACH ROW
select SEQ_EMPLOYEE.nextval into :new.EMPLOYEE_ID from dual;
Insert into Employee Table
INSERT INTO Employee (Name, Address, Post)
VALUES ('sangam','New Road-10', 'Software Developer')
Note: Since the trigger fetches unique number from sequence and inserts into Employee_id field, we need not to put this column in Insert statement as above.
Most of the times programmers fill the DataTable from database. This action fills the schema of the database table into the DataTable . We ...
The asp.net Eval() and Bind() expressions are heavily used in asp.net GridView and DetailsView data bound controls. The difference between ...
I have oracle database installed in my computer. My Operating system is XP. I wrote an application in asp.net. It connected to oracle databa...
Last time I got the following error: HTTP Error 500.19 - Internal Server Error The requested page cannot be accessed because the related ...
We do have a folder (and a number of sub folders) with a number of various files in those folders (and sub folders). Now we do need to list ...
Much often we open child windows from parent web page . In the child page we perform some activities, and later close the window. At the ver...
The default behaviour of asp.net gridview is that it hides both Gridview header and footer when no data is present. You can note the absence...