CONSTRAINTS AND TRIGGERS
o
INSERT command creating a key violation
o
UPDATE command creating a key violation
o
INSERT command creating a referential integrity violation
o
DELETE command creating a referential integrity violation
o
UPDATE command creating a referential integrity violation
o
INSERT command creating a check constraint violation
o
UPDATE command creating a check constraint violation
-
Triggers
INSERT command creating a key violation
=======================================
SQL>
set echo on
SQL>
@viols
SQL>
-- Unique key violation
SQL>
SQL>
INSERT INTO Photo
VALUES(100,'Plate','C',8,'1/4',150,'Y','cunningham_amaryllis.jpg',300);
INSERT
INTO Photo VALUES(100,'Plate','C',8,'1/4',150,'Y','cunningham_amaryllis.jpg',300)
*
ERROR
at line 1:
ORA-00001:
unique constraint (RHM.SYS_C00127855) violated
UPDATE command creating a key violation
=======================================
SQL>
-- Unique key violation
SQL>
SQL>
UPDATE Author SET bio = 'American photographer, passionate advocate of
photography as an art, and a pioneer exhibitor of modern art in the United
States.' WHERE name = 'Andre Kertesz' AND dob = '02-JUL-1894';
UPDATE
Author SET bio = 'American photographer, passionate advocate of photography as
an art, and a pioneer exhibitor of modern art in the United States.' WHERE name
= 'Andre Kertesz' AND dob = '02-JUL-1894'
*
ERROR
at line 1:
ORA-00001:
unique constraint (RHM.SYS_C00127871) violated
INSERT command creating a referential integrity violation
=========================================================
SQL>
-- Referential integrity violation
SQL>
SQL>
INSERT INTO Includes VALUES (1000, 10);
INSERT
INTO Includes VALUES (1000, 10)
*
ERROR at
line 1:
ORA-02291:
integrity constraint (RHM.SYS_C00127894) violated - parent key not
found
DELETE command creating a referential integrity violation
=========================================================
SQL>
-- Referential integrity violation
SQL>
SQL>
DELETE FROM Transaction WHERE idn = '1';
DELETE
FROM Transaction WHERE idn = '1'
*
ERROR
at line 1:
ORA-02292:
integrity constraint (RHM.SYS_C00127894) violated - child record
found
UPDATE command creating a referential integrity violation
=========================================================
SQL>
-- Referential integrity violation
SQL>
SQL>
UPDATE Takes SET catalogn = 1000 WHERE catalogn = 1;
UPDATE
Takes SET catalogn = 1000 WHERE catalogn = 1
*
ERROR
at line 1:
ORA-02291:
integrity constraint (RHM.SYS_C00127887) violated - parent key not
found
INSERT command creating a check constraint violation
====================================================
SQL>
-- Check constraint violation
SQL>
SQL>
INSERT INTO Photo VALUES(100,'Plate','C',8,'1/4',100,'Y','100.jpg',300);
INSERT
INTO Photo VALUES(100,'Plate','C',8,'1/4',100,'Y','100.jpg',300)
*
ERROR
at line 1:
ORA-02290:
check constraint (RHM.SYS_C00127853) violated
UPDATE command creating a check constraint violation
====================================================
SQL>
-- Check constraint violation
SQL>
SQL>
UPDATE Customer SET st_addr_bill = 'CustomerAddrs1' WHERE idname = '1';
UPDATE
Customer SET st_addr_bill = 'CustomerAddrs1' WHERE idname = '1'
*
ERROR at
line 1:
ORA-02290:
check constraint (RHM.SYS_C00127866) violated
SQL>
@trigs
SQL>
-- Recalculate the transaction amount when a customer
SQL>
-- buys a new photo
SQL>
-- At the same time, make the photo not available any more.
SQL>
CREATE TRIGGER CalcTrAmount
2
AFTER INSERT ON Includes
3
REFERENCING NEW AS N
4
FOR EACH ROW
5
DECLARE
6 p
Photo.price%TYPE;
7 a
Transaction.amount%TYPE;
8
BEGIN
9 a
:= 0;
10 SELECT price INTO p FROM Photo WHERE :N.catalogn =
Photo.catalogn;
11 SELECT T.amount INTO a FROM Transaction T WHERE :N.idn =
T.idn;
12 a
:= a + p;
13 UPDATE Transaction SET amount = a WHERE :N.idn = idn;
14 UPDATE Photo P
15 SET P.available = 'N' WHERE P.catalogn = :N.catalogn;
16
END;
17 .
SQL>
RUN;
1
CREATE TRIGGER CalcTrAmount
2
AFTER INSERT ON Includes
3
REFERENCING NEW AS N
4
FOR EACH ROW
5
DECLARE
6 p Photo.price%TYPE;
7 a Transaction.amount%TYPE;
8
BEGIN
9 a := 0;
10 SELECT price INTO p FROM Photo WHERE
:N.catalogn = Photo.catalogn;
11 SELECT T.amount INTO a FROM Transaction T WHERE
:N.idn = T.idn;
12 a := a + p;
13 UPDATE Transaction SET amount = a WHERE :N.idn
= idn;
14 UPDATE Photo P
15 SET P.available = 'N' WHERE P.catalogn =
:N.catalogn;
16* END;
Trigger
created.
SQL>
SQL>
SQL>
-- When we insert in Landscape make sure the same photo is not
SQL>
-- a portrait
SQL>
CREATE TRIGGER LorP
2 AFTER
INSERT ON Landscape
3
REFERENCING NEW AS N
4
FOR EACH ROW
5
DECLARE
6 c
Portrait.catalogn%TYPE;
7
BEGIN
8 SELECT catalogn INTO c FROM Portrait WHERE catalogn =
:N.catalogn;
9 IF
(c = :N.catalogn) THEN
10 DELETE FROM Portrait
WHERE catalogn = :N.catalogn;
11 END
IF;
12
13
EXCEPTION
14 WHEN NO_DATA_FOUND THEN NULL;
15
END;
16 .
SQL>
RUN;
1
CREATE TRIGGER LorP
2
AFTER INSERT ON Landscape
3
REFERENCING NEW AS N
4
FOR EACH ROW
5
DECLARE
6 c Portrait.catalogn%TYPE;
7
BEGIN
8 SELECT catalogn INTO c FROM Portrait WHERE
catalogn = :N.catalogn;
9 IF (c = :N.catalogn) THEN
10 DELETE FROM Portrait WHERE catalogn =
:N.catalogn;
11 END IF;
12
13 EXCEPTION
14 WHEN NO_DATA_FOUND THEN NULL;
15* END;
Trigger
created.
SQL>
SQL>
SQL>
-- and viceversa
SQL>
CREATE TRIGGER PorL
2
AFTER INSERT ON Portrait
3
REFERENCING NEW AS N
4
FOR EACH ROW
5
DECLARE
6 c
Landscape.catalogn%TYPE;
7
BEGIN
8 SELECT catalogn INTO c FROM Landscape WHERE catalogn =
:N.catalogn;
9 IF
(c = :N.catalogn) THEN
10 DELETE FROM Landscape WHERE catalogn = :N.catalogn;
11 END
IF;
12
EXCEPTION
13 WHEN NO_DATA_FOUND THEN NULL;
14
END;
15 .
SQL>
RUN;
1
CREATE TRIGGER PorL
2
AFTER INSERT ON Portrait
3
REFERENCING NEW AS N
4
FOR EACH ROW
5
DECLARE
6 c Landscape.catalogn%TYPE;
7
BEGIN
8 SELECT catalogn INTO c FROM Landscape WHERE catalogn
= :N.catalogn;
9 IF (c = :N.catalogn) THEN
10 DELETE FROM Landscape WHERE catalogn =
:N.catalogn;
11 END IF;
12
EXCEPTION
13 WHEN NO_DATA_FOUND THEN NULL;
14* END;
Trigger
created.
SQL>
SQL>
SQL>
COMMIT;
Commit
complete.
SQL>
spool off
EXECUTE SEVERAL ACTIONS TO SHOW THE TRIGGERS WORKING
====================================================
TRIGGER
CalcTrAmount
--------------------
1.-
Insert a new transaction 10.
2.-
that will comprise of two photos, 27 and 28.
3.- After
inserting this two new photos in Includes the field amount in
transaction
10 gets updated to 1200 (from the original 0) and the 2 photos
get
status non-available
SQL>
@exe_trigs
SQL>
-- Fire trigger CalcTrAmount
SQL>
SQL>
INSERT INTO Transaction VALUES(10, '1234345', 'V', '10-OCT-00', '04-AUG-01',
0);
1 row
created.
SQL>
SELECT * FROM Transaction;
IDN CC_NO C CC_EXPD
TRDATE AMOUNT
----------
---------- - --------- --------- ----------
0 1268680389 V 05-MAY-00
02-FEB-99 1541
1 1328525187 A 10-OCT-00
07-JUL-97 800
2 706332892 D 10-OCT-00 04-APR-01
1200
3 456332892 D 01-JAN-00
04-AUG-01 400
4 706565792 D 01-OCT-01 08-APR-01
70
10 1234345 V 10-OCT-00 04-AUG-01
0
6 rows
selected.
SQL>
SELECT catalogn, available, price FROM Photo;
CATALOGN A PRICE
----------
- ----------
23 N 400
24 Y 300
25 Y 320
26 Y 320
27 Y 800
28 Y 400
29 N 200
29 rows
selected.
SQL>
SQL>
INSERT INTO Includes VALUES(27, 10);
1 row
created.
SQL>
INSERT INTO Includes VALUES(28, 10);
1 row
created.
SQL>
SELECT * FROM Transaction;
IDN CC_NO C CC_EXPD
TRDATE AMOUNT
----------
---------- - --------- --------- ----------
0 1268680389 V 05-MAY-00
02-FEB-99 1541
1 1328525187 A 10-OCT-00
07-JUL-97 800
2 706332892 D 10-OCT-00 04-APR-01
1200
3 456332892 D 01-JAN-00 04-AUG-01
400
4 706565792 D 01-OCT-01 08-APR-01
70
10 1234345 V 10-OCT-00
04-AUG-01 1200
6 rows
selected.
SQL>
SELECT catalogn, available, price FROM Photo;
CATALOGN A PRICE
----------
- ----------
23 N 400
24 Y 300
25 Y 320
26 Y 320
27 N 800
28 N 400
29 N 200
29 rows
selected.
TRIGGER
LorP
------------
1.-
Insert in Landscape a photo that is already in Portrait. Photo number 3.
2.-
After the trigger fires that number is deleted from Portrait.
SQL>
SQL>
SQL>
SQL>
-- Fire trigger LorP
SQL>
SQL>
SELECT * FROM Landscape;
CATALOGN
----------
1
2
6
7
8
9
11
12
13
14
15
CATALOGN
----------
28
29
13 rows
selected.
SQL>
SELECT * FROM Portrait;
CATALOGN
----------
3
16
18
19
20
23
24
25
8 rows
selected.
SQL>
INSERT INTO Landscape VALUES(3);
1 row
created.
SQL>
SELECT * FROM Landscape;
CATALOGN
----------
1
2
6
7
8
9
11
12
13
14
15
CATALOGN
----------
28
29
3
14 rows
selected.
SQL>
SELECT * FROM Portrait;
CATALOGN
----------
16
18
19
20
23
24
25
7 rows
selected.