Answers






Answers

32.1

The most important difference between a stored procedure and a trigger is that triggers cannot be called directly from programs or other stored procedures.

32.2

CREATE TRIGGER MAX1
   AFTER INSERT, UPDATE(POSITION) OF COMMITTEE_MEMBERS
      FOR EACH ROW
   BEGIN
      SELECT   COUNT(*)
      INTO     NUMBER_MEMBERS
      FROM     COMMITTEE_MEMBERS
      WHERE    PLAYERNO IN
              (SELECT   PLAYERNO
               FROM     COMMITTEE_MEMBERS
               WHERE    CURRENT DATE BETWEEN
                        BEGIN_DATE AND END_DATE
                        GROUP BY POSITION
                        HAVING COUNT(*) > 1)
      IF NUMBER_MEMBERS > 0 THEN
         ROLLBACK WORK;
      ENDIF;
   END

32.3

CREATE TRIGGER SUM_PENALTIES_250
   AFTER INSERT, UPDATE(AMOUNT) OF PENALTIES
      FOR EACH ROW
   BEGIN
      SELECT   COUNT(*)
      INTO     NUMBER_PENALTIES
      FROM     PENALTIES
      WHERE    PLAYERNO IN
              (SELECT   PLAYERNO
               FROM     PENALTIES
               GROUP BY PLAYERNO
               HAVING   SUM(AMOUNT) > 250);
      IF NUMBER_PENALTIES > 0 THEN
         ROLLBACK WORK;
      ENDIF;
   END

32.4

CREATE TRIGGER NUMBER_MATCHES_INSERT
   AFTER INSERT OF MATCHES FOR EACH ROW
   BEGIN
      UPDATE   TEAMS
      SET      NUMBER_MATCHES =
              (SELECT   COUNT(*)
               FROM     MATCHES
               WHERE    PLAYERNO = NEW.PLAYERNO)
      WHERE    PLAYERNO = NEW.PLAYERNO
   END

CREATE TRIGGER NUMBER_MATCHES_DELETE
   AFTER DELETE, UPDATE OF MATCHES FOR EACH ROW
   BEGIN
      UPDATE   TEAMS
      SET      NUMBER_MATCHES =
              (SELECT   COUNT(*)
               FROM     MATCHES
               WHERE    PLAYERNO = OLD.PLAYERNO)
      WHERE    PLAYERNO = OLD.PLAYERNO
   END



 Python   SQL   Java   php   Perl 
 game development   web development   internet   *nix   graphics   hardware 
 telecommunications   C++ 
 Flash   Active Directory   Windows