Can't create TRIGGER

Question

I need to create a script to delete some data in a database, the table doesn't have the CASCADE constraint and I don't have the right to edit them. So I'm trying to create a TRIGGER to simulate a Cascade delete on a table (and I will remove it just after the script get executed).

Here is my trigger:

CREATE TRIGGER delete_workspace_on_delete_result
BEFORE DELETE ON RESULT
FOR EACH ROW
BEGIN
  DELETE FROM WORKSPACE WHERE workspace_result_id = :old.id;
END;
/

I don't understand why it's not working, I just follow the Oracle documentation but I have this error:

Error report -
ERROR: syntax error at or near "BEGIN"
  Position: 87

I'm not used to Oracle but can't find a way to make this work by my own.

Littlefoot · Accepted Answer · 2020-07-09 08:00:17Z

I don't see anything wrong, it works. Though:

won't work on Oracle 11g or lower because trigger name is too long (can be max 30 characters)
- will work on 12c and above
consider using create or replace; if it exists and you try to create it again, it'll fail

Demo on 11g:

SQL> CREATE TABLE workspace (workspace_result_id NUMBER);

Table created.

SQL> CREATE TABLE result (id NUMBER);

Table created.

SQL> CREATE TRIGGER delete_workspace_on_delete_result
  2     BEFORE DELETE
  3     ON RESULT
  4     FOR EACH ROW
  5  BEGIN
  6     DELETE FROM WORKSPACE
  7           WHERE workspace_result_id = :old.id;
  8  END;
  9  /
CREATE TRIGGER delete_workspace_on_delete_result
               *
ERROR at line 1:
ORA-00972: identifier is too long


SQL> CREATE TRIGGER delete_workspace_on_del_result
  2     BEFORE DELETE
  3     ON RESULT
  4     FOR EACH ROW
  5  BEGIN
  6     DELETE FROM WORKSPACE
  7           WHERE workspace_result_id = :old.id;
  8  END;
  9  /

Trigger created.

SQL>

Collectives™ on Stack Overflow

Can't create TRIGGER

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related