SQL & PL/SQL :: How To Auto Increment Number - Create Sequence And Trigger
Nov 26, 2012
I am switching database from access to oracle 11g. I have create all the required tables, but I am stuck at one point. The previous person who created access database had auto increment with SG0101, SG0102,........ In oracle I know we can auto increment primary keys but only with the numbers not with characters.
So I have customerid which is a primary key and it automatically increments the number, but I have one more column with memberid where I am inserting all the ids that start with SG0101 bla bla.....
I already have 800 member ID's that start with SG, but that value doesnt automatically increment because I dont have any sequence or trigger to do that.
So how do I create a sequence and trigger that will automatically start value with SG and keeps auto incrementing?
I'm trying to create a sequence for a primary key to simply auto-increment by the default of 1. I have a sql script written to generate mt tables, and I'm not sure how to modify the script to include the sequence. I also just want the sequence for a specific column, ie, PK, not the PK in all tables.
Here's a snippet from my script:
create table image ( image_id int NOT NULL, source_id int NOT NULL, CONSTRAINT image_id_pk PRIMARY KEY (image_id), CONSTRAINT fk_source_id FOREIGN KEY (source_id) REFERENCES source(source_id) );
Would I add the create sequence statement right after the create table, and if so, how do I apply the sequence to only 1 table and a single column?
CREATE TABLE test (id NUMBER PRIMARY KEY, name VARCHAR2(30));
Step2
CREATE SEQUENCE test_sequence START WITH 1 INCREMENT BY 1;
Method1 Follow Step1 and Step2 and create a Trigger as below :
CREATE OR REPLACE TRIGGER test_trigger BEFORE INSERT ON test REFERENCING NEW AS NEW FOR EACH ROW BEGIN SELECT test_sequence.nextval INTO :NEW.ID FROM dual; END; /
Method 2 Follow Step1 and Step2 and directly have an insert statement as below:
INSERT INTO test (id, name) VALUES (test_sequence.nextval , 'Jon343');
Method 1, the Trigger is not getting created, however by following Method 2, I am able to generate auto-increment number.
I have a table abc with name and phone_number columns in it,and this table contains 100 records. Now I want to add a column say ID as primary key for this and it should be auto incremented and should have primary key for the first 100 records as well.
CREATE OR REPLACE TRIGGER audit_emp AFTER INSERT OR UPDATE OR DELETE ON Employee FOR EACH ROW declare my emp_audit%ROWTYPE; begin if inserting then my.action := 'I'; [code].......
It works alright but i need to enter a sequenced value to a column audit_id which is a column in the emp_audit table when a change takes place, but when i define the variable it gives be an error as bad bind variable so where can i define this column in this trigger so get a sequenced value every time i d or u occurs .
I have already done auto increment by making sequence and trigger. but now the problem is when i am trying to retrieve data from that table it returns all data perfectly but the primary key that is my auto increment value shows blank.I am facing this problem with join query, there 4 table left joined in my query. But when I remove join from my query then it shows that value.
But i need that value in my join query.So, what is the problem and what can I do?And other thing is when I apply this query in Oracle SQL Developer, it works perfect.
My Query: return $this->db->query("select * from TBL_EMPLOYEE_BASIC left join TBL_EMPLOYEE_DETAILS on TBL_EMPLOYEE_BASIC.EMPL_ID = TBL_EMPLOYEE_DETAILS.EMPL_ID left join TBL_EMPLOYEE_EDUCATION on TBL_EMPLOYEE_BASIC.EMPL_ID = TBL_EMPLOYEE_EDUCATION.EMPL_ID left join TBL_EMPLOYEE_EXPERIENCE on TBL_EMPLOYEE_BASIC.EMPL_ID = TBL_EMPLOYEE_EXPERIENCE.EMPL_ID where
i would like to create a sequence inside a dml trigger. Is it possible? I created a trigger and it is compiled sucessfully.
create or replace trigger tri_update_test after delete on test declare pragma autonomous_transaction;
[code]...
trigger created sucessfully.And i try to delete data from the test
delete from test where id=5;
Output:
ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "SCOTT.TRI_UPDATE_TEST", line 4 ORA-04088: error during execution of trigger 'SCOTT.TRI_UPDATE_TEST'
i would like to create a sequence inside a dml trigger. Is it possible? I created a trigger and it is compiled sucessfully.
create or replace trigger tri_update_test after delete on test declare pragma autonomous_transaction; begin execute immediate 'create sequence t_unqid start with 1 increment by 1 nocache '
end ;trigger created successfully. And i try to delete data from the test delete from test where id=5;
Output:
ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "SCOTT.TRI_UPDATE_TEST", line 4 ORA-04088: error during execution of trigger 'SCOTT.TRI_UPDATE_TEST'
i need to be increasing the sequence no by 1 for every calender date.For example lets say if i receive 5 dumps of data for 24/09/2013 it should be as below. For next day the 25th the sequence no should again begin with 1.
I have three tables. One for projects, one for volunteers, and a bridge entity for the many to many relationship between the Project and Volunteer.
In Project table, I have a field called, Volunteers_currently_signed_up, which means the number of volunteers currently signed up to participate in a project.
When I add an entry to my bridge entity which is composed of Volunteer_ID and Project_ID, I want the Volunteers_currently_signed_up to increment by 1, where the Project_ID in the bridge entity corresponds to that in Project.
I have very very little PL/SQL, and this is my amateur attempt so far:
CREATE OR REPLACE trigger "BI_Volunteers_currently_signed_up" BEFORE INSERT OR UPDATE ON Volunteers_in_project for each row WHERE Volunteers_in_project.Project_ID=Project.Project_ID; begin Project.Volunteers_currently_signed_up += 1; end; /
I am trying to create a table that will increment my ID by one using the following commands:
/*Creates the log needed to increment ID*/ create sequence seq_log; CREATE TABLE MESSAGE_LOG_TABLE ( IDNUMBER(10)NOT NULLPRIMARY KEY,
[code]...
When I run the above my create sequence completes successfully but I get a ORA-00955: name is already used by an existing object error message on the create table. I have dropped all tables and sequences before running my command but I still get the same error message.
After it bombs out it appears that SQL+ want's more information for it begins to give me line numbers as if it is looking for a ";" to end the above command. I have to exit SQL+ and log back in to continue working.
I have 2 tables where the data is related.table ot_pack_list,ot_delv .ot_pack_list contains details of items information that are packed to be delivered sometimes what happens user may remove some items from this, and the summary column of dl_wt and dl_qty in ot_delv must get automatically changed whenever there is addition in ot_pack.
I have set up a schema using a DDL script in Oracle 10g. I am linking this through to a Visual Basic 2008 fron end. The connection is fine. My database of is for a car hire company project.
I need to know how to create an auto increment for a primary key field, like Access does.
For example here is my table structure for my Rental_Payment table.
-- TABLE RENTAL_PAYMENT CREATE TABLE RENTAL_PAYMENT ( PAYMENTNO NUMBER NOT NULL, CARDNO NUMBER NOT NULL,
I have the following select query that works perfectly fine. Returns 25 rows based on the descending order of the price.But, I want add one more expression to this list of columns in this query (apart from customer_id).
the expression should look like Cust-01 for the first customer from the below query all the way to Cust-25 for the last customer.But how can I can generate 01 to 25 in oracle?
select customer_id from (select customer_id from capitalPLAN where member_status = 'MEMBER' AND customer_id NOT in ('156','201','1385','2125','3906','165') order by price desc ) where rownum <= 25
I have created a trigger for after insert which updates a table when there is a row inserted in that table. The update is on a column which stores the application description along with the sequence number. Now my requirement is that sequence number should be unique only with in an application but not with in the table.Say the row entry can be as follows:
App_Desc Request_ID ----------------------- DEV 100 1 DEV 101 2 STG 100 3 STG 101 4
Here Request_ID is unique But the sequqnce thats created for DEV (100,101) should take an entry of 102 for the next entry for DEV and same applies for STG. So I have to use the same sequqnce for all the application.
1) Invoice Invoice_no number(8), Debtor varchar2(35), Invoice Date Date, Print Date Date, Currency varchar2(3), Total Amount Ex Number(11,2), Total Amount Vat Number(11,2), Total Amount Number(11,2), Status Varchar2(15),
The below trigger is running fine without any error ,but i wasn't able to perform the exact logic by the below trigger.IF, inserting multiple row then the SIVNUM field gets the same sequence number for each row , without incrementing the value for the next row.
Only the sequence value increases on the next set of row ow's inserted
Example (inserting 2 rows ) based on the below trigger:
I am having a problem with auto populating different fields based on inventory no. field.. This is a bug giving to me to work on and i not able to figure out how to populate the other fields.
How to set any triggers for the items to auto populate and i am suppose to finish this work today.
Ive a requirement which ive overlooked for a while now as below,
On my tabular form i have fields: Empid as Select List and when the value is picked from this Select List EmpID i want the associated/corresponding phone# displayed in its TEXT FIELD in that same row,
apex 4.1.1/Oracle 11gR2,
also i tried the below as a workaround but it doesnt fulfill my requirement, [URL]....
In our production, we have two nodes in the cluster. We use the sequence for one of the main table for primary key. Our application is expecting sequence number increments along with created date time stamp. Right now sequences are cached for each node and it creates problem for the application. We would not like to use NOCACHE option because it causes performance issue.
This is the current scenario -
Transaction #1 on Node 1 - Seq ID 1 - Time Stamp 12:01 Transaction #2 on Node 2 - Seq ID 51 - Time Stamp 12:02 Transaction #3 on Node 1 - Seq ID 2 - Time Stamp 12:03
When I try to query based on the time stamp, primary should also go up. To be very clear on what I would like to have, please consider the following example.Without using NOCACHE option, I need to have the data in the following order.
Transaction #1 on Node 1 - Seq ID 1 - Time Stamp 12:01 Transaction #2 on Node 2 - Seq ID 2 - Time Stamp 12:02 Transaction #3 on Node 1 - Seq ID 3 - Time Stamp 12:03
In other words, sequence number should always increment along with the time.
I am using Oracle 10G version. I need a code base for new Sequence Trigger.
Requirement : As per the request, before INSERT trigger will generate the sequence ID from AA001 to AA999 value. But once the sequence is reached to AA999, the next sequence value will be generated normal (start from AB001 etc..).
i have an understanding about Integrity constraint checking and the trigger execution sequence, which i decsribe below.
Integrity constraint are restiction on DML operation performed by the user. When a user deletes or updates or inserts a rows in a table then oracle performs certain checking to see that the data which is effecting the row abide certain rules. There are certain per-defined set or rules that can be applied on a table such as PRIMARY KEY, FORIEGN KEY, UNIQUE, CHECK, NOT NULL etc, user-defined rules can be applied on tables by using Triggers.
In both the cases the Integrity Constraint Checking is deferred until the complete execution of the statement. All rows are inserted first, then all rows are checked for constraint violations.
So when i see the trigger execution model the following steps are performed by oracle, Oracle uses the following execution model to maintain the proper firing sequence of multiple triggers and constraint checking..This is what the Oracle Documentation Library says [extract from Oracle Database Concepts 10g Release 1 (10.1)].
1. Run all BEFORE statement triggers that apply to the statement.
2. Loop for each row affected by the SQL statement. a.Run all BEFORE row triggers that apply to the statement. b.Lock and change row, and perform integrity constraint checking. (The lock is not released until the transaction is committed.) c.Run all AFTER row triggers that apply to the statement.
4.Run all AFTER statement triggers that apply to the statement.
As for step 3 here the checking of the constraints for the statement is performed which where defered till the complete execution of the statement, then what is done in step 2b? what constraints are checked there?
I have a following select statement where I am not generating a sequence in any of these WHERE clause, GROUP BY or ORDER BY clause, DISTINCT clause, along with a UNION or INTERSECT or MINUS or sub-query.
I have query like this
"SELECT AER_ID_TEMP.AER_ID, AER_ID_TEMP.D__PRNO, AER_ID_TEMP.D__PCNT, CAST((select char_value from aris_param_data where param_id = 101)||seq_record_id.nextval AS INT) as RECORD_ID FROM AER_ID_TEMP"
but still encountering a error "ORA-02287 sequence number not allowed"
Is there any way to get the last Sequence Number generated regardless the Sequence name? Something like dbinfo('sqlca.sqlerrd1') within Informix?
Imagine I have 2 sequences. Imagine that I run both several times until 20:00 hours. Imagine that at 21:00 hours I would like to know what was the last generated number created by any of the sequences. I don't wann know wich sequence generated the last NEXTVAL, I only want to know the value of the last NEXTVAL used.
Is it possible without mentionating the Sequence name?
