CODEWITHSUNDEEP
sqloracle-databaseplsql
Dobhaweim
Dobhaweim

Reputation: 163

SQL trigger to decrement another table

I've created a Database for a DVD store which contains a Film table to hold info about films + copies available, and a sale table. I've created sequences for the Film and Sale table, to auto-increment the Primary keys FILM_ID and SALE_TRANSACTION_REF respectively, using the following code.

CREATE SEQUENCE Film_Seq
MINVALUE 1
START WITH 1
INCREMENT BY 1
NOCACHE;

CREATE OR REPLACE TRIGGER New_Film 
BEFORE INSERT ON FILM 
FOR EACH ROW

BEGIN
SELECT Film_Seq.NEXTVAL
INTO   :new.FILM_ID
FROM   dual;
END;
/

This seems to work handy enough, but when I try to use a trigger to decrement the COPIES_AVAILABLE attribute of the film in Film after a sale with the following code, I get an error:

CREATE OR REPLACE TRIGGER After_Sale 
AFTER INSERT ON SALE 
DECLARE
ID NUMBER(38) := Sale_Seq.CURRVAL;
BEGIN
    UPDATE FILM
    SET COPIES_AVAILABLE = COPIES_AVAILABLE - 1
    FROM FILM, SALE
    WHERE FILM.FILM_ID = SALE.FILM_ID 
    AND (SALE.SALE_TRANSACTION_REF = ID);
END;
/

I actually get two errors, the first one relates to the declare statement, which is 

4/2    PL/SQL: SQL Statement ignored

And the second one has to do with the UPDATE statement and it is

6/2    PL/SQL: ORA-00933: SQL command not properly ended

I've wrecked my brains trying to construct this statement differently, and have searched the documentation but can't seem to find the answer, and help would be greatly appreciated.

Also, looking over my question it may not be obvious but I have already created a sale_seq.

Sorry, it may not be evident what I am trying to achieve here. Whenever a Film is sold and added to the sale table, I want to automatically decrement the Copie_Available for the corresponding film in the film table, so if I left out the AND AND (SALE.SALE_TRANSACTION_REF = Sale_Seq.CURRVAL) clause the trigger will complie grand, but will decrement all copies available for all films in the film table, which is incorrect.

Upvotes: 0

Views: 1175

Answers (2)

Tom
Tom

Reputation: 11

Why save COPIES_AVAILABLE to your database. Why wouldn't you detract for instance [all copies in stock] - [all sold copies] if you want to know available copies.

Upvotes: 0

CodeBird
CodeBird

Reputation: 3858

try it like this:

CREATE OR REPLACE TRIGGER After_Sale 
AFTER INSERT ON SALE 
FOR EACH ROW
BEGIN
    UPDATE FILM
    SET COPIES_AVAILABLE = COPIES_AVAILABLE - 1
    WHERE FILM.FILM_ID = :NEW.FILM_ID
END;

Upvotes: 1

Related Questions