| Anti-unique key [message #7201] |
Wed, 28 May 2003 00:25  |
Kris Krawiec
Messages: 1
Registered: May 2003
|
Junior Member |
|
|
Hi
I've problem. I have cteated table:
CREATE TABLE KNTR
(CODE NUMBER,
NIP VAERCHAR2(12),
OPIS VARCHAR2(7)
)
/
There is one condition: It shouldn't exists records with this same value CODE and other value NIP.
For example:
(1, '505-400','PIERWSZY')
(1, '505-400','DRUGI')
(2, '670-600','TRZECI')
(2, '670-600','CZWATY')
but you couldn't INSERT
(2,'555-666','PIATY')
How does it to achieve ?
|
|
|
|
|
|
| Re: Anti-unique key [message #7209 is a reply to message #7201] |
Wed, 28 May 2003 06:43  |
Girimohan
Messages: 5
Registered: May 2003
|
Junior Member |
|
|
CREATE TABLE KNTR
(CODE NUMBER,
NIP VAERCHAR2(12),
OPIS VARCHAR2(7)
)
/
After creating the table create the following trigger
CREATE OR REPLACE TRIGGER TRG_KNTR
BEFORE INSERT OR UPDATE
ON KNTR
FOR EACH ROW
DECLARE
M_CODE KNTR.CODE%TYPE;
M_NAME KNTR.NIP%TYPE;
CURSOR C1 IS
SELECT CODE
FROM OTEST2
WHERE CODE = :NEW.CODE;
CURSOR C2 IS
SELECT NIP
FROM OTEST2
WHERE CODE = M_CODE;
BEGIN
IF INSERTING THEN
OPEN C1;
FETCH C1 INTO M_CODE;
IF C1%FOUND THEN
OPEN C2;
FETCH C2 INTO M_NAME;
CLOSE C2;
IF :NEW.CODE!=M_CODE OR :NEW.NIP!=M_NAME THEN
RAISE_APPLICATION_ERROR(-20098,'VALID COMBAINATION CHANGED');
END IF;
END IF;
ELSIF UPDATING THEN
IF :NEW.CODE != :OLD.CODE OR :NEW.NIP != :OLD.NIP THEN
RAISE_APPLICATION_ERROR(-20099,'CANT UPDATE CODE OR NIP');
END IF;
END IF;
END;
/
then try inserting records
INSERT INTO OTEST2 VALUES(1,'ABCD','ENG');
INSERT INTO OTEST2 VALUES(1,'ABCD','SCI');
INSERT INTO OTEST2 VALUES(1,'ABCD','SOC');
After inserting the above rows try inserting
the following row
INSERT INTO OTEST2 VALUES(1,'SDSD','ENG');
ERROR at line 1:
ORA-20098: VALID COMBAINATION CHANGED
ORA-06512: at "SCOTT.TRG_KNTR", line 21
ORA-04088: error during execution of trigger 'SCOTT.TRG_KNTR'
i hope this will solve ur problem
Regards
Girimohan.n
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
