Home » SQL & PL/SQL » SQL & PL/SQL » Trigger to handle primary key violation (Oracle 10g)
| Trigger to handle primary key violation [message #565554] |
Wed, 05 September 2012 04:15  |
mortifikator
Messages: 4
Registered: September 2010
|
Junior Member |
|
|
Hi everyone,
I am trying to handle PK violation error on a certain table, on INSERT, my best guess is I should use a trigger.
Since I am not so familiar with triggers, I would kindly ask you to help me out. The basic idea is this:
The table consists of 7 columns, and 6 of them are PK, and the seventh one is "amount". I want to handle PK violation in such way that, if it occurs during INSERT, then instead of inserting a new row, it should just update the "amount".
It sounds pretty simple, I hope the solution is as simple, unfortunately I was not able to google anything that I could use.
Thanks in advance,
BR
|
|
|
|
|
|
| Re: Trigger to handle primary key violation [message #565580 is a reply to message #565554] |
Wed, 05 September 2012 06:42  |
 |
dariyoosh
Messages: 538
Registered: March 2009
Location: France
|
Senior Member |
|
|
Hello there,
mortifikator wrote on Wed, 05 September 2012 11:15... I am trying to handle PK violation error on a certain table, on INSERT, my best guess is I should use a trigger ...
It could be done by a trigger, not with an ordinary trigger but with an INSTEAD OF trigger which in the context of your problem has to be defined on a view of your table and not directly on your table.
mortifikator wrote on Wed, 05 September 2012 11:15
The table consists of 7 columns, and 6 of them are PK, and the seventh one is "amount". I want to handle PK violation in such way that, if it occurs during INSERT, then instead of inserting a new row, it should just update the "amount".
Here is an example:
DROP TABLE testtable;
CREATE TABLE testtable
(
col1 VARCHAR2(10) NOT NULL,
col2 VARCHAR2(10) NOT NULL,
col3 VARCHAR2(10) NOT NULL,
col4 VARCHAR2(10) NOT NULL,
col5 VARCHAR2(10) NOT NULL,
col6 VARCHAR2(10) NOT NULL,
amount NUMBER(5) -- So this is the column that has to be updated whenever there is unique constraint violation
);
ALTER TABLE testtable ADD CONSTRAINT PK_TESTTABLE
PRIMARY KEY(col1, col2, col3, col4, col5, col6);
INSERT ALL
INTO testtable(col1,col2,col3,col4,col5,col6,amount)
VALUES ('v11', 'v12', 'v13', 'v14', 'v15', 'v16',12)
INTO testtable(col1,col2,col3,col4,col5,col6,amount)
VALUES ('v21', 'v22', 'v23', 'v24', 'v25', 'v26',13)
INTO testtable(col1,col2,col3,col4,col5,col6,amount)
VALUES ('v31', 'v32', 'v33', 'v34', 'v35', 'v36',14)
INTO testtable(col1,col2,col3,col4,col5,col6,amount)
VALUES ('v41', 'v42', 'v43', 'v44', 'v45', 'v46',15)
INTO testtable(col1,col2,col3,col4,col5,col6,amount)
VALUES ('v51', 'v52', 'v53', 'v54', 'v55', 'v56',16)
INTO testtable(col1,col2,col3,col4,col5,col6,amount)
VALUES ('v61', 'v62', 'v63', 'v64', 'v65', 'v66',17)
SELECT * FROM DUAL;
As you provided neither some sample data nor your table definition, I just put in the table some data an example
SQL> SELECT * FROM testtable;
COL1 COL2 COL3 COL4 COL5 COL6 AMOUNT
---------- ---------- ---------- ---------- ---------- ---------- ----------
v11 v12 v13 v14 v15 v16 12
v21 v22 v23 v24 v25 v26 13
v31 v32 v33 v34 v35 v36 14
v41 v42 v43 v44 v45 v46 15
v51 v52 v53 v54 v55 v56 16
v61 v62 v63 v64 v65 v66 17
6 rows selected.
vij means a value in the row i and the column j (just as example)
Now you have to define the view and the INSTEAD OF trigger on that view
CREATE OR REPLACE VIEW v_testtable AS
SELECT col1, col2, col3, col4, col5, col6, amount
FROM testtable;
-- Now you have to define an INSTEAD OF TRIGGER
-- based on the view that you have just defined.
CREATE OR REPLACE TRIGGER tr_v_testtable
INSTEAD OF INSERT
ON v_testtable
FOR EACH ROW
DECLARE
pk_violation_exception EXCEPTION;
-- Assign the oracle internal error code ORA-00001 to the user defined
-- exception in order to be able to capture it later within the EXCEPTION clause.
PRAGMA EXCEPTION_INIT(pk_violation_exception, -00001);
BEGIN
INSERT INTO testtable(col1,
col2,
col3,
col4,
col5,
col6,
amount)
VALUES (:NEW.col1,
:NEW.col2,
:NEW.col3,
:NEW.col4,
:NEW.col5,
:NEW.col6,
:NEW.amount);
EXCEPTION
WHEN pk_violation_exception THEN
DBMS_OUTPUT.PUT_LINE('ORA-00001 (pk_violation_exception) captured');
UPDATE testtable
SET amount = :NEW.amount
WHERE col1 = :NEW.col1 AND
col2 = :NEW.col2 AND
col3 = :NEW.col3 AND
col4 = :NEW.col4 AND
col5 = :NEW.col5 AND
col6 = :NEW.col6;
-- if neessary you can then update your log file.
END tr_v_testtable;
/
In order to do a test, let's try to INSERT a new row whose corresponding primary key column values are already in the table with an amount = 480 (just an example)
SQL> SELECT * FROM testtable;
COL1 COL2 COL3 COL4 COL5 COL6 AMOUNT
---------- ---------- ---------- ---------- ---------- ---------- ----------
v11 v12 v13 v14 v15 v16 12
v21 v22 v23 v24 v25 v26 13
v31 v32 v33 v34 v35 v36 14
v41 v42 v43 v44 v45 v46 15
v51 v52 v53 v54 v55 v56 16
v61 v62 v63 v64 v65 v66 17
6 rows selected.
SQL>
For example the first line in the above mentioned list (amount = 12) we just give it a new amount value (for example 480) and with exactly the same values for other columns (thus the same primary key)
SQL> INSERT INTO v_testtable(col1,col2,col3,col4,col5,col6,amount) VALUES ('v11','v12','v13','v14','v15','v16',480);
ORA-00001 (pk_violation_exception) captured
1 row created.
SQL>
And you can check that the UPDATE upon the amount column has been done (now the first line in the below list has the value 480 for the amount column)
SQL> SELECT * FROM testtable;
COL1 COL2 COL3 COL4 COL5 COL6 AMOUNT
---------- ---------- ---------- ---------- ---------- ---------- ----------
v11 v12 v13 v14 v15 v16 480
v21 v22 v23 v24 v25 v26 13
v31 v32 v33 v34 v35 v36 14
v41 v42 v43 v44 v45 v46 15
v51 v52 v53 v54 v55 v56 16
v61 v62 v63 v64 v65 v66 17
6 rows selected.
SQL>
So if you chose this solution, then you INSERT rows through your defined view and not directly INTO your table.
Regards,
Dariyoosh
[Updated on: Wed, 05 September 2012 06:45] Report message to a moderator |
|
|
|
| Re: Trigger to handle primary key violation [message #565586 is a reply to message #565554] |
Wed, 05 September 2012 06:51 |
mnitu
Messages: 159
Registered: February 2008
Location: Reims
|
Senior Member |
|
|
mortifikator wrote on Wed, 05 September 2012 11:15..I want to handle PK violation in such way that, if it occurs during INSERT, then instead of inserting a new row, it should just update the "amount".
Use Merge statement
|
|
|
|
|
|
|
|
| Re: Trigger to handle primary key violation [message #565594 is a reply to message #565580] |
Wed, 05 September 2012 07:48 |
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
dariyoosh wrote on Wed, 05 September 2012 12:42
DECLARE
pk_violation_exception EXCEPTION;
-- Assign the oracle internal error code ORA-00001 to the user defined
-- exception in order to be able to capture it later within the EXCEPTION clause.
PRAGMA EXCEPTION_INIT(pk_violation_exception, -00001);
ORA-00001 already has a predefined exception. See here
|
|
|
|
| Re: Trigger to handle primary key violation [message #565596 is a reply to message #565592] |
Wed, 05 September 2012 07:55 |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Ooops, I misread the topic. ORA-00001 can't be handled in BEFORE/AFTER trigger regardless of single/multi user environment since it is checked before trigger fires. It can be done in multi-user environment via view + INSTEAD OF trigger due to locking. In any case, as I already noted, OP should use MERGE.
SY.
[Updated on: Wed, 05 September 2012 07:58] Report message to a moderator |
|
|
|
| Re: Trigger to handle primary key violation [message #565597 is a reply to message #565596] |
Wed, 05 September 2012 07:58 |
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
|
It can be handled in an instead of trigger, which was dariyoosh's point. Whether or not it works in a multi-user environment is another matter, though I'm not sure how an instead of trigger would behave any differently to a procedure doing the same thing.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Aug 26 18:18:55 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
