What a trigger
-
We all like Oracle, don't we:
CREATE TRIGGER "LOGIN_ID_TRG" BEFORE INSERT ON "LOGIN" REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
DECLARE
v_newVal NUMBER(12) := 0;
v_incval NUMBER(12) := 0;
BEGIN
IF INSERTING AND :new.ID IS NULL THEN
SELECT Login_ID_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
IF v_newVal = 1 THEN
SELECT NVL(max(ID),0) INTO v_newVal FROM Login;
v_newVal := v_newVal + 1;
LOOP
EXIT WHEN v_incval>=v_newVal;
SELECT Login_ID_SEQ.nextval INTO v_incval FROM dual;
END LOOP;
END IF;
:new.ID := v_newVal;
END IF;
END;
/Does that gem do what it is expected to do? Hm, the first row inserted into the (empty) table gets the ID 1 (and is inserted into the table with ID 1), but Login_ID_SEQ.curr_val is already 2, and consequently the C# application communicating with that Oracle db received a wrong value (it calls
SELECT Login_ID_SEQ.CURRVAL FROM DUALin order to get the last insert id). By the way, the second row inserted receives ID 3. For the first value inserted into an empty table, theLOOPis executed once, and thusLogin_ID_SEQ.nextvaltwice. Setting the start value ofv_incvalto 1 did the trick. But I fear the trigger will fail if some when a row would be inserted with an ID different from null (fortunately, we had not activated the IdentityInsert property in our SQL Server database, and hence I am confident that our application won't do that). How did I find that gem? I used an Oracle tool to convert my SQL Server database. Then I wrote a script to set up all the tables, sequences, triggers, and some start values for my "schema" using the "Show SQL" feature of Oracle Enterprise Manager for each of these objects. Then I tested that with a fresh schema, and my application threw an error with the first login attempt. Thanks a lot, Oracle! -
We all like Oracle, don't we:
CREATE TRIGGER "LOGIN_ID_TRG" BEFORE INSERT ON "LOGIN" REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
DECLARE
v_newVal NUMBER(12) := 0;
v_incval NUMBER(12) := 0;
BEGIN
IF INSERTING AND :new.ID IS NULL THEN
SELECT Login_ID_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
IF v_newVal = 1 THEN
SELECT NVL(max(ID),0) INTO v_newVal FROM Login;
v_newVal := v_newVal + 1;
LOOP
EXIT WHEN v_incval>=v_newVal;
SELECT Login_ID_SEQ.nextval INTO v_incval FROM dual;
END LOOP;
END IF;
:new.ID := v_newVal;
END IF;
END;
/Does that gem do what it is expected to do? Hm, the first row inserted into the (empty) table gets the ID 1 (and is inserted into the table with ID 1), but Login_ID_SEQ.curr_val is already 2, and consequently the C# application communicating with that Oracle db received a wrong value (it calls
SELECT Login_ID_SEQ.CURRVAL FROM DUALin order to get the last insert id). By the way, the second row inserted receives ID 3. For the first value inserted into an empty table, theLOOPis executed once, and thusLogin_ID_SEQ.nextvaltwice. Setting the start value ofv_incvalto 1 did the trick. But I fear the trigger will fail if some when a row would be inserted with an ID different from null (fortunately, we had not activated the IdentityInsert property in our SQL Server database, and hence I am confident that our application won't do that). How did I find that gem? I used an Oracle tool to convert my SQL Server database. Then I wrote a script to set up all the tables, sequences, triggers, and some start values for my "schema" using the "Show SQL" feature of Oracle Enterprise Manager for each of these objects. Then I tested that with a fresh schema, and my application threw an error with the first login attempt. Thanks a lot, Oracle!Something I try to avoid whenever I'm writing a trigger is to not perform a select on the table that the trigger is being created for. That is usually a recipe for disaster. I understand the need for the trigger to use a SEQUENCE in order to set the value of :new.ID. But I don't understand the need to query the existing table to validate the value. That can all be done using CONSTRAINTS and other database constructs. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
-
We all like Oracle, don't we:
CREATE TRIGGER "LOGIN_ID_TRG" BEFORE INSERT ON "LOGIN" REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
DECLARE
v_newVal NUMBER(12) := 0;
v_incval NUMBER(12) := 0;
BEGIN
IF INSERTING AND :new.ID IS NULL THEN
SELECT Login_ID_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
IF v_newVal = 1 THEN
SELECT NVL(max(ID),0) INTO v_newVal FROM Login;
v_newVal := v_newVal + 1;
LOOP
EXIT WHEN v_incval>=v_newVal;
SELECT Login_ID_SEQ.nextval INTO v_incval FROM dual;
END LOOP;
END IF;
:new.ID := v_newVal;
END IF;
END;
/Does that gem do what it is expected to do? Hm, the first row inserted into the (empty) table gets the ID 1 (and is inserted into the table with ID 1), but Login_ID_SEQ.curr_val is already 2, and consequently the C# application communicating with that Oracle db received a wrong value (it calls
SELECT Login_ID_SEQ.CURRVAL FROM DUALin order to get the last insert id). By the way, the second row inserted receives ID 3. For the first value inserted into an empty table, theLOOPis executed once, and thusLogin_ID_SEQ.nextvaltwice. Setting the start value ofv_incvalto 1 did the trick. But I fear the trigger will fail if some when a row would be inserted with an ID different from null (fortunately, we had not activated the IdentityInsert property in our SQL Server database, and hence I am confident that our application won't do that). How did I find that gem? I used an Oracle tool to convert my SQL Server database. Then I wrote a script to set up all the tables, sequences, triggers, and some start values for my "schema" using the "Show SQL" feature of Oracle Enterprise Manager for each of these objects. Then I tested that with a fresh schema, and my application threw an error with the first login attempt. Thanks a lot, Oracle!Maybe Oracle is not such known here, so let me give you some more explanations. With SQL Server or MS Access, we often use "automatic IDs", that is an integer with the "Identity" property (SQL Server) set to true or the "New Values" property set to "increment". When you then insert a row, you do not care for the identifier in the table, the database generates that automatically, and from a program you can query it with
SELECT @@IDENTITY. Oracle cannot do that. You need aNUMBERcolumn, then a "sequence" which will feed the new numbers, and a trigger for the INSERT event, which will call the sequence'sNEXTVAL(next new number) and put that into the new row's ID column. From a program, you can query that value withSELECT MYSEQUENCE.CURRVAL FROM DUAL. The above trigger was automatically generated by Oracle for the migration of an SQL Server database. When you do a migration, (most) tables do already contain some rows, and hence the trigger must be adjusted to the existing values. That's is to be accomplished by the "IF v_newVal = 1 THEN" section. And that section's code is terrible, and - as proofed above - wrong when the table was still empty. Not only do they call the sequence'sNEXTVALas often as the maximum ID value of the table, in case of a previously empty table the ID value is set to 1 while the sequence's CURRVAL is already 2. -
Something I try to avoid whenever I'm writing a trigger is to not perform a select on the table that the trigger is being created for. That is usually a recipe for disaster. I understand the need for the trigger to use a SEQUENCE in order to set the value of :new.ID. But I don't understand the need to query the existing table to validate the value. That can all be done using CONSTRAINTS and other database constructs. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
As another poster has pointed out, this is a conversion tool. It is assuming that there will be data in the table already before this trigger is invoked, but the value of the sequence may not be correctly set to match the existing data. So, it tries to automatically adjust the sequence to keep it in line with any pre-existing values in the table, which is why it queries the table. Unfortunately, it does not consider the edge case of an empty table.
-
Maybe Oracle is not such known here, so let me give you some more explanations. With SQL Server or MS Access, we often use "automatic IDs", that is an integer with the "Identity" property (SQL Server) set to true or the "New Values" property set to "increment". When you then insert a row, you do not care for the identifier in the table, the database generates that automatically, and from a program you can query it with
SELECT @@IDENTITY. Oracle cannot do that. You need aNUMBERcolumn, then a "sequence" which will feed the new numbers, and a trigger for the INSERT event, which will call the sequence'sNEXTVAL(next new number) and put that into the new row's ID column. From a program, you can query that value withSELECT MYSEQUENCE.CURRVAL FROM DUAL. The above trigger was automatically generated by Oracle for the migration of an SQL Server database. When you do a migration, (most) tables do already contain some rows, and hence the trigger must be adjusted to the existing values. That's is to be accomplished by the "IF v_newVal = 1 THEN" section. And that section's code is terrible, and - as proofed above - wrong when the table was still empty. Not only do they call the sequence'sNEXTVALas often as the maximum ID value of the table, in case of a previously empty table the ID value is set to 1 while the sequence's CURRVAL is already 2.Thanks for that explanation, cause at first I thought that selecting a max value from a table to get a new id is a coding horror itself. But your statement explains nearly everything. (I had to use an Oracle-DB only one time and it was a "bittersweet" experience.)
Greetings Covean