CODEWITHSUNDEEP
sqldatabasetriggersdb2ddl
JJ Adams
JJ Adams

Reputation: 511

Using TRIGGERS in database ddl file

So I have the following entities: a Director entity and a Movie Entity. These 2 entities have 2 relationships: a Directs relationship and a WonAward relationship. In order to make sure that a director can only win an award with a movie he/she has directed I have created the following trigger:

CREATE OR REPLACE TRIGGER CheckDirect
AFTER INSERT ON WonAward
REFERENCING NEW AS N
FOR EACH ROW

WHEN (N.DirectorID = (SELECT DirectorID FROM Directs WHERE MovieID = N.MovieID) and
    N.MovieID IN (SELECT MovieID FROM Directs WHERE DirectorID = N.DirectorID))

BEGIN
    INSERT INTO WonAward VALUES (N.DirectorID, N.MovieID, N.AwardName, N.Year, N.Budget);
END;

But I keep getting an error when I'm running it:

BEGIN
    INSERT INTO WonAward VALUES (N.DirectorID, N.MovieID, N.AwardName, N.Year, N.Budget)
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "e, 
N.Year, N.Budget)".  Expected tokens may include:  "<psm_semicolon>".  LINE 
NUMBER=10.  SQLSTATE=42601

END
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "END".  
Expected tokens may include:  "JOIN <joined_table>".  SQLSTATE=42601

I took off the semicolon from the end of the INSER statement and ran it and no change. Took off the semicolon after the END and also no change. When I run a test INSERT statement where a director gets an award for a movie he/she didn't direct, it goes through successfully which it should not.

Now if I change the TRIGGER and take out the BEGIN and END part, it runs successfully, but it ends up passing the INSERT statement that should not pass, i.e. a Director wins an award for a movie he/she didn't direct, and it fails the INSERT statements that should pass successfully with the following error:

SQL0723N  An error occurred in a triggered SQL statement in trigger 
"CHECKDIRECT".  Information returned for the error includes SQLCODE 
"-803", SQLSTATE "23505" and message tokens "1|GHADDAM.WONAWARD".  
SQLSTATE=09000

Upvotes: 0

Views: 477

Answers (1)

eugenioy
eugenioy

Reputation: 12393

This error:

SQL0811N  The result of a scalar fullselect, 
SELECT INTO statement, or VALUES INTO statement is more than one row.

Is probably caused by this line:

N.MovieID = (SELECT MovieID FROM Directs WHERE DirectorID = N.DirectorID)

Surely a director can have more than one movie?

In that case, the SELECT will return many rows instead of just one MovieID.

Perhaps you should change it to:

N.MovieID IN (SELECT MovieID FROM Directs WHERE DirectorID = N.DirectorID)

Upvotes: 1

Related Questions