/* Here is the file referred to as "triggers2.sql" in the following
SQLPlus session. One thing to note: it is possible for this trigger to
cause an integrity violation if it tried to set the quality to a number
greater than 10 based on our schema! An appropriate solution would be to
add the line "AND NEW.quality < 10" to the WHEN clause.
*/

CREATE TRIGGER picardKicksAss
AFTER INSERT ON featuring
FOR EACH ROW
WHEN (NEW.characterName = 'Jean-Luc Picard'
 AND NEW.seriesTitle = 'Star Trek: The Next Generation'
 AND NEW.seriesCreator = 'Gene Roddenberry')
BEGIN
UPDATE episodes
 SET quality = (quality + 1)
 WHERE title = :NEW.episodeTitle
 AND seriesTitle = 'Star Trek: The Next Generation'
 AND seriesCreator = 'Gene Roddenberry';
END picardKicksAss;
.
run;


alter trigger picardKicksAss enable;

select title,quality from episodes where title = 'The Child';
INSERT INTO 
 featuring (episodeTitle, characterName, seriesTitle, seriesCreator) 
VALUES ('The Child', 'Jean-Luc Picard', 'Star Trek: The Next Generation', 'Gene Roddenberry');
select title,quality from episodes where title = 'The Child';
INSERT INTO 
 featuring (episodeTitle, characterName, seriesTitle, seriesCreator) 
VALUES ('The Child', 'William T. Riker', 'Star Trek: The Next Generation', 'Gene Roddenberry');
select title,quality from episodes where title = 'The Child';


/************
 end of triggers2 file; beginning of SQLPlus session 
*************/

Connected to:
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production

SQL>
SQL> 
SQL> @triggers2

Trigger dropped.

  1  CREATE TRIGGER picardKicksAss
  2  AFTER INSERT ON featuring
  3  FOR EACH ROW
  4  WHEN (NEW.characterName = 'Jean-Luc Picard'
  5   AND NEW.seriesTitle = 'Star Trek: The Next Generation'
  6   AND NEW.seriesCreator = 'Gene Roddenberry')
  7  BEGIN
  8  UPDATE episodes
  9   SET quality = (quality + 1)
 10   WHERE title = :NEW.episodeTitle
 11   AND seriesTitle = 'Star Trek: The Next Generation'
 12   AND seriesCreator = 'Gene Roddenberry';
 13* END picardKicksAss;

Trigger created.


Trigger altered.


TITLE
--------------------------------------------------------------------------------
   QUALITY
----------
The Child
	 6



1 row created.


TITLE
--------------------------------------------------------------------------------
   QUALITY
----------
The Child
	 7



1 row created.


TITLE
--------------------------------------------------------------------------------
   QUALITY
----------
The Child
	 7


SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production
1:42pm elaine28:~/cs145/hw6>