CONSTRAINTS AND TRIGGERS

 

 

-        Constraints

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

o        Trigger creation

o        Show the triggers working

 

 

 

 

 

CONSTRAINTS

 

 

 

 

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

 

 

 

 

 

TRIGGERS

 

 

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.