1 CREATE TRIGGER StopSelfTitled 2 AFTER INSERT OR UPDATE OF albumName ON Album 3 FOR EACH ROW 4 WHEN (NEW.albumName=NEW.groupName) 5 BEGIN 6 7 /* i would have just renamed the album, but Oracle doesn't seem to like 8 the mutating table deal... */ 9 10 RAISE_APPLICATION_ERROR(-20000, 'Statement halted: rename album to 11 ''Self-Titled'''); 12* END; Trigger created. 1 CREATE TRIGGER StopBadModDate 2 AFTER INSERT OR UPDATE OF modified ON Tablature 3 DECLARE 4 5 /* without the use of subqueries in the WHEN clause, the use 6 of triggers seems to get somewhat limited without very expensive 7 operations like the following... this trigger basically acts like 8 a constraint, but across a few tables, ensuring that a mod date on 9 a piece of tab doesn't occur before the album that it's on was 10 released. */ 11 12 currYear Album.year%TYPE; 13 currGroup TabFor.groupName%TYPE; 14 currSong TabFor.songName%TYPE; 15 modified Tablature.modified%TYPE; 16 17 CURSOR modCursor IS 18 (SELECT modified,groupName,songName 19 FROM Tablature,TabFor 20 WHERE Tablature.url = TabFor.url); 21 22 BEGIN 23 open modCursor; 24 LOOP 25 FETCH modCursor INTO modified,currGroup,currSong; 26 EXIT WHEN modCursor%NOTFOUND; 27 28 SELECT year INTO currYear 29 FROM Album 30 WHERE albumName = 31 (SELECT DISTINCT albumName 32 FROM OnAlbum 33 WHERE groupName=currGroup AND songName=currSong); 34 35 IF(currYear > modified) THEN 36 RAISE_APPLICATION_ERROR(-20000, 'Statement halted because modification comes before song released'); 37 END IF; 38 END LOOP; 39 close modCursor; 40* END StopBadModDate; Trigger created. /* the trigger allows this query */ INSERT INTO Album VALUES ('Far', 'Tin Cans With Strings to You', '15-MAR-1996', 12, 'Enharmonik Studios'); 1 row created. /* but not this one */ INSERT INTO Album VALUES ('Portishead', 'Portishead', '09-OCT-1998', 11, 'London, somwhere...'); INSERT INTO Album VALUES ('Portishead', 'Portishead', '09-OCT-1998', 11, * ERROR at line 1: ORA-20000: Statement halted: rename album to 'Self-Titled' ORA-06512: at "SEVLS.STOPSELFTITLED", line 6 ORA-04088: error during execution of trigger 'SEVLS.STOPSELFTITLED' /* once again, the trigger allows this update */ UPDATE Tablature SET modified='14-MAY-2000' WHERE url='ftp://www.olga.net/w/willhaven/crosshairs.tab'; 1 row updated. /* but not this */ UPDATE Tablature SET modified='12-DEC-1936' WHERE url='ftp://www.olga.net/w/willhaven/crosshairs.tab'; UPDATE Tablature SET modified='12-DEC-1936' * ERROR at line 1: ORA-20000: Statement halted because modification comes before song released ORA-06512: at "SEVLS.STOPBADMODDATE", line 34 ORA-04088: error during execution of trigger 'SEVLS.STOPBADMODDATE'