-3

I have to implement a trigger which would:

7) Show the DDL for how a trigger can be used to transfer all rental copies from a store being deleted from the Store information table to the central store
8) Show how this trigger can be extended to make sure that the central store is never deleted from the database

So far I have done this:

CREATE OR REPLACE TRIGGER stores
BEFORE DELETE ON stores
FOR EACH ROW
BEGIN
IF DELETING WHERE cvr = 123456789 THEN

Raise_Application_Error (
num => -20050,
msg => 'You can not delete Main Store.');
END IF;
IF DELETING THEN
UPDATE store_id=123456789 ON movies WHERE isActive = 0
END IF;
END;

so main store is with cvr which is written, but it gives me a compilation error. Any help? Thanks in advance.

Renatas Valatka
  • 1
  • 6

1 Answers1

0

You have two errors in your code.

  1. there is no "DELETING WHERE" expression, you have to use two boolean exceptions like this:

    IF DELETING AND :old.cvr = 123456789 THEN...

:old.cvr refers to value of cvr column in deleted record

  1. Syntax of UPDATE clause is

    UPDATE table_name SET column_name1 = value1, column_name1 = value2 WHERE where_clause;

in your case probably somethink like this:

UPDATE movies 
    set store_id = 123456789
  WHERE store_id = :old.cvr

I guess, I don't know required functionality

Petr Pribyl
  • 3,425
  • 1
  • 20
  • 22
  • on UPDATE sytax I also need to check that store_id which is old.cvr, if isActive, so I would avoid changing all movie store locations. Can I somehow reffer on UPDATE that it would check if stores isActive equals to 0? – Renatas Valatka Mar 18 '15 at 16:35
  • yes, you can. Your where clause can be like this: `WHERE store_id = :old.cvr AND isActive = 0` – Petr Pribyl Mar 19 '15 at 10:25