1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

duplicate entries in database on multiple save button press

Discussion in 'Oracle Forms and Reports' started by samina, Mar 11, 2013.

  1. samina

    samina Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    I have this form wherin i populate a grid in BLK_USERS with data from a database using a button.I allow user to select those data from the grid to which he needs to assign certain rights.This selected grid data together with other fields of rights on the form get inserted into another table through Save button(I have wriiten a procedure for it at button press which uses INSERT command).

    The problem is if I press save button twice the same data is inserted into the table twice ,if I press thrice then thrice and so on..

    Could any1 suggest a solution...
     
  2. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi Samina,

    take one item as XX_FLAG in the block. initial value as 'N' . Later when button pressed trigger code write the below code

    IF :)Blockname.XX_FLAG = 'N' ) THEN
    :Blockname.XX_FLAG := 'Y';
    ELSE
    <RAISE TRIGGER FAILURE> WIth Message
    END IF;

    Regards
    Sambasiva Reddy
    Samba.komma@gmail.com
     
  3. samina

    samina Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    but this would not solve the problem.
    As when the form is run again the same data will be populated in the grid and when user selects or deselects those rows to which he wants to assign rights what happens is that data already existed in the database ,so instead of being updated ,new rows are being entered...
    My question is how do I check first with data already existing in the database ,compare it with data in grid ,if same then update already existing if changes or else do nothing or if new data then insert new rows in the database
     
  4. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hai ,

    Do like below ....

    suppose you have two blocks parent_blk and grid_blk And you have primary key in parent_blk i.e like parent_blk.id1 .

    Use this as reference id in grid_blk.


    when_but_pressed

    --
    SELECT count (*)
    INTO ln_cnt
    FROM xx_grid_tbl
    WHERE ref_id = : parent_blk.id1 ;
    --

    IF (ln_cnt > 0 ) THEN
    < go_block >
    < query_block>
    ELSE
    INSERT into xx_grid_tbl ( : parent_blk.id1 ,....... );
    END IF;



    I think it will give idea Otherwise post the screen shots of the form so that i will analyze ....



    Regards
    Sambasiva Reddy
    samba.komma@gmail.com
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You cannot catch duplicates using a transactional method -- each transaction is isolated from the others so a pending insert won't be disallowed if it contains data already in the table being inserted into. You do need a primary key on the table -- no other trigger is necessary since the primary key is defined to be UNIQUE and thus will not allow duplicates. As an example:

    Code (SQL):
    SQL> CREATE TABLE dup_test(
    2 id NUMBER,
    3 DATA varchar2(40)
    4 );
     
    TABLE created.
     
    SQL>
    SQL> CREATE OR REPLACE TRIGGER chk_for_dups_trg
    2 BEFORE INSERT ON dup_test
    3 FOR each ROW
    4 DECLARE
    5 rowct NUMBER;
    6 BEGIN
    7 SELECT COUNT(*) INTO rowct FROM dup_test
    8 WHERE id = :NEW.id;
    9
    10 IF rowct <> 0 THEN
    11 raise_application_error(-20998, 'Duplicate key discovered.');
    12 END IF;
    13
    14 END;
    15 /
     
    TRIGGER created.
     
    SQL>
    SQL> SHOW errors
     
    No errors.
     
    SQL>
    SQL> BEGIN
    2 FOR i IN 1..100 loop
    3 INSERT INTO dup_test
    4 VALUES(i, 'Snorthorfer pingnoodle '||i);
    5 END loop;
    6
    7 commit;
    8
    9 END;
    10 /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> SELECT * FROM dup_test;
     
    ID DATA
    ---------- ----------------------------------------
    1 Snorthorfer pingnoodle 1
    2 Snorthorfer pingnoodle 2
    3 Snorthorfer pingnoodle 3
    4 Snorthorfer pingnoodle 4
    5 Snorthorfer pingnoodle 5
    6 Snorthorfer pingnoodle 6
    7 Snorthorfer pingnoodle 7
    8 Snorthorfer pingnoodle 8
    9 Snorthorfer pingnoodle 9
    10 Snorthorfer pingnoodle 10
    11 Snorthorfer pingnoodle 11
     
    ID DATA
    ---------- ----------------------------------------
    12 Snorthorfer pingnoodle 12
    13 Snorthorfer pingnoodle 13
    14 Snorthorfer pingnoodle 14
    15 Snorthorfer pingnoodle 15
    16 Snorthorfer pingnoodle 16
    17 Snorthorfer pingnoodle 17
    18 Snorthorfer pingnoodle 18
    19 Snorthorfer pingnoodle 19
    20 Snorthorfer pingnoodle 20
    21 Snorthorfer pingnoodle 21
    22 Snorthorfer pingnoodle 22
     
    ID DATA
    ---------- ----------------------------------------
    23 Snorthorfer pingnoodle 23
    24 Snorthorfer pingnoodle 24
    25 Snorthorfer pingnoodle 25
    26 Snorthorfer pingnoodle 26
    27 Snorthorfer pingnoodle 27
    28 Snorthorfer pingnoodle 28
    29 Snorthorfer pingnoodle 29
    30 Snorthorfer pingnoodle 30
    31 Snorthorfer pingnoodle 31
    32 Snorthorfer pingnoodle 32
    33 Snorthorfer pingnoodle 33
     
    ID DATA
    ---------- ----------------------------------------
    34 Snorthorfer pingnoodle 34
    35 Snorthorfer pingnoodle 35
    36 Snorthorfer pingnoodle 36
    37 Snorthorfer pingnoodle 37
    38 Snorthorfer pingnoodle 38
    39 Snorthorfer pingnoodle 39
    40 Snorthorfer pingnoodle 40
    41 Snorthorfer pingnoodle 41
    42 Snorthorfer pingnoodle 42
    43 Snorthorfer pingnoodle 43
    44 Snorthorfer pingnoodle 44
     
    ID DATA
    ---------- ----------------------------------------
    45 Snorthorfer pingnoodle 45
    46 Snorthorfer pingnoodle 46
    47 Snorthorfer pingnoodle 47
    48 Snorthorfer pingnoodle 48
    49 Snorthorfer pingnoodle 49
    50 Snorthorfer pingnoodle 50
    51 Snorthorfer pingnoodle 51
    52 Snorthorfer pingnoodle 52
    53 Snorthorfer pingnoodle 53
    54 Snorthorfer pingnoodle 54
    55 Snorthorfer pingnoodle 55
     
    ID DATA
    ---------- ----------------------------------------
    56 Snorthorfer pingnoodle 56
    57 Snorthorfer pingnoodle 57
    58 Snorthorfer pingnoodle 58
    59 Snorthorfer pingnoodle 59
    60 Snorthorfer pingnoodle 60
    61 Snorthorfer pingnoodle 61
    62 Snorthorfer pingnoodle 62
    63 Snorthorfer pingnoodle 63
    64 Snorthorfer pingnoodle 64
    65 Snorthorfer pingnoodle 65
    66 Snorthorfer pingnoodle 66
     
    ID DATA
    ---------- ----------------------------------------
    67 Snorthorfer pingnoodle 67
    68 Snorthorfer pingnoodle 68
    69 Snorthorfer pingnoodle 69
    70 Snorthorfer pingnoodle 70
    71 Snorthorfer pingnoodle 71
    72 Snorthorfer pingnoodle 72
    73 Snorthorfer pingnoodle 73
    74 Snorthorfer pingnoodle 74
    75 Snorthorfer pingnoodle 75
    76 Snorthorfer pingnoodle 76
    77 Snorthorfer pingnoodle 77
     
    ID DATA
    ---------- ----------------------------------------
    78 Snorthorfer pingnoodle 78
    79 Snorthorfer pingnoodle 79
    80 Snorthorfer pingnoodle 80
    81 Snorthorfer pingnoodle 81
    82 Snorthorfer pingnoodle 82
    83 Snorthorfer pingnoodle 83
    84 Snorthorfer pingnoodle 84
    85 Snorthorfer pingnoodle 85
    86 Snorthorfer pingnoodle 86
    87 Snorthorfer pingnoodle 87
    88 Snorthorfer pingnoodle 88
     
    ID DATA
    ---------- ----------------------------------------
    89 Snorthorfer pingnoodle 89
    90 Snorthorfer pingnoodle 90
    91 Snorthorfer pingnoodle 91
    92 Snorthorfer pingnoodle 92
    93 Snorthorfer pingnoodle 93
    94 Snorthorfer pingnoodle 94
    95 Snorthorfer pingnoodle 95
    96 Snorthorfer pingnoodle 96
    97 Snorthorfer pingnoodle 97
    98 Snorthorfer pingnoodle 98
    99 Snorthorfer pingnoodle 99
     
    ID DATA
    ---------- ----------------------------------------
    100 Snorthorfer pingnoodle 100
     
    100 ROWS selected.
     
    SQL>
    SQL> INSERT INTO dup_test
    2 VALUES(101, 'Snotrhorfer pingnoodle 101');
     
    1 ROW created.
     
    SQL>
    SQL> pause
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> SELECT * FROM dup_test;
     
    ID DATA
    ---------- ----------------------------------------
    1 Snorthorfer pingnoodle 1
    2 Snorthorfer pingnoodle 2
    3 Snorthorfer pingnoodle 3
    4 Snorthorfer pingnoodle 4
    5 Snorthorfer pingnoodle 5
    6 Snorthorfer pingnoodle 6
    7 Snorthorfer pingnoodle 7
    8 Snorthorfer pingnoodle 8
    9 Snorthorfer pingnoodle 9
    10 Snorthorfer pingnoodle 10
    11 Snorthorfer pingnoodle 11
     
    ID DATA
    ---------- ----------------------------------------
    12 Snorthorfer pingnoodle 12
    13 Snorthorfer pingnoodle 13
    14 Snorthorfer pingnoodle 14
    15 Snorthorfer pingnoodle 15
    16 Snorthorfer pingnoodle 16
    17 Snorthorfer pingnoodle 17
    18 Snorthorfer pingnoodle 18
    19 Snorthorfer pingnoodle 19
    20 Snorthorfer pingnoodle 20
    21 Snorthorfer pingnoodle 21
    22 Snorthorfer pingnoodle 22
     
    ID DATA
    ---------- ----------------------------------------
    23 Snorthorfer pingnoodle 23
    24 Snorthorfer pingnoodle 24
    25 Snorthorfer pingnoodle 25
    26 Snorthorfer pingnoodle 26
    27 Snorthorfer pingnoodle 27
    28 Snorthorfer pingnoodle 28
    29 Snorthorfer pingnoodle 29
    30 Snorthorfer pingnoodle 30
    31 Snorthorfer pingnoodle 31
    32 Snorthorfer pingnoodle 32
    33 Snorthorfer pingnoodle 33
     
    ID DATA
    ---------- ----------------------------------------
    34 Snorthorfer pingnoodle 34
    35 Snorthorfer pingnoodle 35
    36 Snorthorfer pingnoodle 36
    37 Snorthorfer pingnoodle 37
    38 Snorthorfer pingnoodle 38
    39 Snorthorfer pingnoodle 39
    40 Snorthorfer pingnoodle 40
    41 Snorthorfer pingnoodle 41
    42 Snorthorfer pingnoodle 42
    43 Snorthorfer pingnoodle 43
    44 Snorthorfer pingnoodle 44
     
    ID DATA
    ---------- ----------------------------------------
    45 Snorthorfer pingnoodle 45
    46 Snorthorfer pingnoodle 46
    47 Snorthorfer pingnoodle 47
    48 Snorthorfer pingnoodle 48
    49 Snorthorfer pingnoodle 49
    50 Snorthorfer pingnoodle 50
    51 Snorthorfer pingnoodle 51
    52 Snorthorfer pingnoodle 52
    53 Snorthorfer pingnoodle 53
    54 Snorthorfer pingnoodle 54
    55 Snorthorfer pingnoodle 55
     
    ID DATA
    ---------- ----------------------------------------
    56 Snorthorfer pingnoodle 56
    57 Snorthorfer pingnoodle 57
    58 Snorthorfer pingnoodle 58
    59 Snorthorfer pingnoodle 59
    60 Snorthorfer pingnoodle 60
    61 Snorthorfer pingnoodle 61
    62 Snorthorfer pingnoodle 62
    63 Snorthorfer pingnoodle 63
    64 Snorthorfer pingnoodle 64
    65 Snorthorfer pingnoodle 65
    66 Snorthorfer pingnoodle 66
     
    ID DATA
    ---------- ----------------------------------------
    67 Snorthorfer pingnoodle 67
    68 Snorthorfer pingnoodle 68
    69 Snorthorfer pingnoodle 69
    70 Snorthorfer pingnoodle 70
    71 Snorthorfer pingnoodle 71
    72 Snorthorfer pingnoodle 72
    73 Snorthorfer pingnoodle 73
    74 Snorthorfer pingnoodle 74
    75 Snorthorfer pingnoodle 75
    76 Snorthorfer pingnoodle 76
    77 Snorthorfer pingnoodle 77
     
    ID DATA
    ---------- ----------------------------------------
    78 Snorthorfer pingnoodle 78
    79 Snorthorfer pingnoodle 79
    80 Snorthorfer pingnoodle 80
    81 Snorthorfer pingnoodle 81
    82 Snorthorfer pingnoodle 82
    83 Snorthorfer pingnoodle 83
    84 Snorthorfer pingnoodle 84
    85 Snorthorfer pingnoodle 85
    86 Snorthorfer pingnoodle 86
    87 Snorthorfer pingnoodle 87
    88 Snorthorfer pingnoodle 88
     
    ID DATA
    ---------- ----------------------------------------
    89 Snorthorfer pingnoodle 89
    90 Snorthorfer pingnoodle 90
    91 Snorthorfer pingnoodle 91
    92 Snorthorfer pingnoodle 92
    93 Snorthorfer pingnoodle 93
    94 Snorthorfer pingnoodle 94
    95 Snorthorfer pingnoodle 95
    96 Snorthorfer pingnoodle 96
    97 Snorthorfer pingnoodle 97
    98 Snorthorfer pingnoodle 98
    99 Snorthorfer pingnoodle 99
     
    ID DATA
    ---------- ----------------------------------------
    100 Snorthorfer pingnoodle 100
    101 Snotrhorfer pingnoodle 101
    101 Snorthorfer pingnoodle 101
     
    102 ROWS selected.
     
    SQL>
    Two inserts were attempted, from two different sessions and both were allowed to succeed since the trigger didn't 'see' the other pending insert. [The pause allowed me to run both sessions.]

    Let's drop the trigger and add a proper primary key to the table and try this exercise again:


    Code (SQL):
    SQL>
    SQL> DROP TRIGGER chk_for_dups_trg;
     
    TRIGGER dropped.
     
    SQL>
    SQL> TRUNCATE TABLE dup_test;
     
    TABLE truncated.
     
    SQL>
    SQL> ALTER TABLE dup_test ADD CONSTRAINT dup_test_pk PRIMARY KEY(id);
     
    TABLE altered.
     
    SQL>
    SQL> BEGIN
    2 FOR i IN 1..100 loop
    3 INSERT INTO dup_test
    4 VALUES(i, 'Snorthorfer pingnoodle '||i);
    5 END loop;
    6
    7 commit;
    8
    9 END;
    10 /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> SELECT * FROM dup_test;
     
    ID DATA
    ---------- ----------------------------------------
    1 Snorthorfer pingnoodle 1
    2 Snorthorfer pingnoodle 2
    3 Snorthorfer pingnoodle 3
    4 Snorthorfer pingnoodle 4
    5 Snorthorfer pingnoodle 5
    6 Snorthorfer pingnoodle 6
    7 Snorthorfer pingnoodle 7
    8 Snorthorfer pingnoodle 8
    9 Snorthorfer pingnoodle 9
    10 Snorthorfer pingnoodle 10
    11 Snorthorfer pingnoodle 11
     
    ID DATA
    ---------- ----------------------------------------
    12 Snorthorfer pingnoodle 12
    13 Snorthorfer pingnoodle 13
    14 Snorthorfer pingnoodle 14
    15 Snorthorfer pingnoodle 15
    16 Snorthorfer pingnoodle 16
    17 Snorthorfer pingnoodle 17
    18 Snorthorfer pingnoodle 18
    19 Snorthorfer pingnoodle 19
    20 Snorthorfer pingnoodle 20
    21 Snorthorfer pingnoodle 21
    22 Snorthorfer pingnoodle 22
     
    ID DATA
    ---------- ----------------------------------------
    23 Snorthorfer pingnoodle 23
    24 Snorthorfer pingnoodle 24
    25 Snorthorfer pingnoodle 25
    26 Snorthorfer pingnoodle 26
    27 Snorthorfer pingnoodle 27
    28 Snorthorfer pingnoodle 28
    29 Snorthorfer pingnoodle 29
    30 Snorthorfer pingnoodle 30
    31 Snorthorfer pingnoodle 31
    32 Snorthorfer pingnoodle 32
    33 Snorthorfer pingnoodle 33
     
    ID DATA
    ---------- ----------------------------------------
    34 Snorthorfer pingnoodle 34
    35 Snorthorfer pingnoodle 35
    36 Snorthorfer pingnoodle 36
    37 Snorthorfer pingnoodle 37
    38 Snorthorfer pingnoodle 38
    39 Snorthorfer pingnoodle 39
    40 Snorthorfer pingnoodle 40
    41 Snorthorfer pingnoodle 41
    42 Snorthorfer pingnoodle 42
    43 Snorthorfer pingnoodle 43
    44 Snorthorfer pingnoodle 44
     
    ID DATA
    ---------- ----------------------------------------
    45 Snorthorfer pingnoodle 45
    46 Snorthorfer pingnoodle 46
    47 Snorthorfer pingnoodle 47
    48 Snorthorfer pingnoodle 48
    49 Snorthorfer pingnoodle 49
    50 Snorthorfer pingnoodle 50
    51 Snorthorfer pingnoodle 51
    52 Snorthorfer pingnoodle 52
    53 Snorthorfer pingnoodle 53
    54 Snorthorfer pingnoodle 54
    55 Snorthorfer pingnoodle 55
     
    ID DATA
    ---------- ----------------------------------------
    56 Snorthorfer pingnoodle 56
    57 Snorthorfer pingnoodle 57
    58 Snorthorfer pingnoodle 58
    59 Snorthorfer pingnoodle 59
    60 Snorthorfer pingnoodle 60
    61 Snorthorfer pingnoodle 61
    62 Snorthorfer pingnoodle 62
    63 Snorthorfer pingnoodle 63
    64 Snorthorfer pingnoodle 64
    65 Snorthorfer pingnoodle 65
    66 Snorthorfer pingnoodle 66
     
    ID DATA
    ---------- ----------------------------------------
    67 Snorthorfer pingnoodle 67
    68 Snorthorfer pingnoodle 68
    69 Snorthorfer pingnoodle 69
    70 Snorthorfer pingnoodle 70
    71 Snorthorfer pingnoodle 71
    72 Snorthorfer pingnoodle 72
    73 Snorthorfer pingnoodle 73
    74 Snorthorfer pingnoodle 74
    75 Snorthorfer pingnoodle 75
    76 Snorthorfer pingnoodle 76
    77 Snorthorfer pingnoodle 77
     
    ID DATA
    ---------- ----------------------------------------
    78 Snorthorfer pingnoodle 78
    79 Snorthorfer pingnoodle 79
    80 Snorthorfer pingnoodle 80
    81 Snorthorfer pingnoodle 81
    82 Snorthorfer pingnoodle 82
    83 Snorthorfer pingnoodle 83
    84 Snorthorfer pingnoodle 84
    85 Snorthorfer pingnoodle 85
    86 Snorthorfer pingnoodle 86
    87 Snorthorfer pingnoodle 87
    88 Snorthorfer pingnoodle 88
     
    ID DATA
    ---------- ----------------------------------------
    89 Snorthorfer pingnoodle 89
    90 Snorthorfer pingnoodle 90
    91 Snorthorfer pingnoodle 91
    92 Snorthorfer pingnoodle 92
    93 Snorthorfer pingnoodle 93
    94 Snorthorfer pingnoodle 94
    95 Snorthorfer pingnoodle 95
    96 Snorthorfer pingnoodle 96
    97 Snorthorfer pingnoodle 97
    98 Snorthorfer pingnoodle 98
    99 Snorthorfer pingnoodle 99
     
    ID DATA
    ---------- ----------------------------------------
    100 Snorthorfer pingnoodle 100
     
    100 ROWS selected.
     
    SQL>
    SQL> INSERT INTO dup_test
    2 VALUES(101, 'Snotrhorfer pingnoodle 101');
     
    1 ROW created.
     
    SQL>
    SQL> pause
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> SELECT * FROM dup_test;
     
    ID DATA
    ---------- ----------------------------------------
    1 Snorthorfer pingnoodle 1
    2 Snorthorfer pingnoodle 2
    3 Snorthorfer pingnoodle 3
    4 Snorthorfer pingnoodle 4
    5 Snorthorfer pingnoodle 5
    6 Snorthorfer pingnoodle 6
    7 Snorthorfer pingnoodle 7
    8 Snorthorfer pingnoodle 8
    9 Snorthorfer pingnoodle 9
    10 Snorthorfer pingnoodle 10
    11 Snorthorfer pingnoodle 11
     
    ID DATA
    ---------- ----------------------------------------
    12 Snorthorfer pingnoodle 12
    13 Snorthorfer pingnoodle 13
    14 Snorthorfer pingnoodle 14
    15 Snorthorfer pingnoodle 15
    16 Snorthorfer pingnoodle 16
    17 Snorthorfer pingnoodle 17
    18 Snorthorfer pingnoodle 18
    19 Snorthorfer pingnoodle 19
    20 Snorthorfer pingnoodle 20
    21 Snorthorfer pingnoodle 21
    22 Snorthorfer pingnoodle 22
     
    ID DATA
    ---------- ----------------------------------------
    23 Snorthorfer pingnoodle 23
    24 Snorthorfer pingnoodle 24
    25 Snorthorfer pingnoodle 25
    26 Snorthorfer pingnoodle 26
    27 Snorthorfer pingnoodle 27
    28 Snorthorfer pingnoodle 28
    29 Snorthorfer pingnoodle 29
    30 Snorthorfer pingnoodle 30
    31 Snorthorfer pingnoodle 31
    32 Snorthorfer pingnoodle 32
    33 Snorthorfer pingnoodle 33
     
    ID DATA
    ---------- ----------------------------------------
    34 Snorthorfer pingnoodle 34
    35 Snorthorfer pingnoodle 35
    36 Snorthorfer pingnoodle 36
    37 Snorthorfer pingnoodle 37
    38 Snorthorfer pingnoodle 38
    39 Snorthorfer pingnoodle 39
    40 Snorthorfer pingnoodle 40
    41 Snorthorfer pingnoodle 41
    42 Snorthorfer pingnoodle 42
    43 Snorthorfer pingnoodle 43
    44 Snorthorfer pingnoodle 44
     
    ID DATA
    ---------- ----------------------------------------
    45 Snorthorfer pingnoodle 45
    46 Snorthorfer pingnoodle 46
    47 Snorthorfer pingnoodle 47
    48 Snorthorfer pingnoodle 48
    49 Snorthorfer pingnoodle 49
    50 Snorthorfer pingnoodle 50
    51 Snorthorfer pingnoodle 51
    52 Snorthorfer pingnoodle 52
    53 Snorthorfer pingnoodle 53
    54 Snorthorfer pingnoodle 54
    55 Snorthorfer pingnoodle 55
     
    ID DATA
    ---------- ----------------------------------------
    56 Snorthorfer pingnoodle 56
    57 Snorthorfer pingnoodle 57
    58 Snorthorfer pingnoodle 58
    59 Snorthorfer pingnoodle 59
    60 Snorthorfer pingnoodle 60
    61 Snorthorfer pingnoodle 61
    62 Snorthorfer pingnoodle 62
    63 Snorthorfer pingnoodle 63
    64 Snorthorfer pingnoodle 64
    65 Snorthorfer pingnoodle 65
    66 Snorthorfer pingnoodle 66
     
    ID DATA
    ---------- ----------------------------------------
    67 Snorthorfer pingnoodle 67
    68 Snorthorfer pingnoodle 68
    69 Snorthorfer pingnoodle 69
    70 Snorthorfer pingnoodle 70
    71 Snorthorfer pingnoodle 71
    72 Snorthorfer pingnoodle 72
    73 Snorthorfer pingnoodle 73
    74 Snorthorfer pingnoodle 74
    75 Snorthorfer pingnoodle 75
    76 Snorthorfer pingnoodle 76
    77 Snorthorfer pingnoodle 77
     
    ID DATA
    ---------- ----------------------------------------
    78 Snorthorfer pingnoodle 78
    79 Snorthorfer pingnoodle 79
    80 Snorthorfer pingnoodle 80
    81 Snorthorfer pingnoodle 81
    82 Snorthorfer pingnoodle 82
    83 Snorthorfer pingnoodle 83
    84 Snorthorfer pingnoodle 84
    85 Snorthorfer pingnoodle 85
    86 Snorthorfer pingnoodle 86
    87 Snorthorfer pingnoodle 87
    88 Snorthorfer pingnoodle 88
     
    ID DATA
    ---------- ----------------------------------------
    89 Snorthorfer pingnoodle 89
    90 Snorthorfer pingnoodle 90
    91 Snorthorfer pingnoodle 91
    92 Snorthorfer pingnoodle 92
    93 Snorthorfer pingnoodle 93
    94 Snorthorfer pingnoodle 94
    95 Snorthorfer pingnoodle 95
    96 Snorthorfer pingnoodle 96
    97 Snorthorfer pingnoodle 97
    98 Snorthorfer pingnoodle 98
    99 Snorthorfer pingnoodle 99
     
    ID DATA
    ---------- ----------------------------------------
    100 Snorthorfer pingnoodle 100
    101 Snotrhorfer pingnoodle 101
     
    101 ROWS selected.
     
    SQL>
    SQL>
    101 records are now in the table, and the second session insert waited on the pending transaction from session 1 and, when the commit from session 1 was issued session 2 saw this:

    Code (SQL):
    INSERT INTO dup_test
    *
    ERROR at line 1:
    ORA-00001: UNIQUE CONSTRAINT (BING.DUP_TEST_PK) violated
     
    It doesn't matter where you put such a trigger it won't catch pending inserts as the values are not visible by any session other than the one performing the insert.

    The only way around this reliably is to use a primary key.