CODEWITHSUNDEEP
oracle-databaseplsqldatabase-trigger
Gerardo Hernandez
Gerardo Hernandez

Reputation: 5

Creating trigger with 'instead of delete'

I want to treat a delete statement as an update but it throws this error, and I don't know what it means.

create or replace trigger Miembros_V_IOD
   instead of delete on Miembros_V 
   for each row
Begin
   update Miembros set (end_date = sysdate)
   where Miembros.nick = :old.nick
   and Miembros.club = :old.club;
end;

LINE/COL ERROR
-------- ----------------------------------------------------------
2/4      PL/SQL: SQL Statement ignored
2/34     PL/SQL: ORA-00907: missing right parenthesis

Upvotes: 0

Views: 60

Answers (1)

Alex Poole
Alex Poole

Reputation: 191265

The error is pointing to the = sign. You're getting a PL/SQL error - albeit one caused by the inner SQL - and for a trigger the line numbers in PL/SQL errors start from the DECLARE (if you have one) or BEGIN, not from the start of the overall CREATE statement. So the 2/34 refers to character 34 of the second line of the PL/SQL part, which is:

   update Miembros set (end_date = sysdate)

... which is the =.

You shouldn't have the parenthesis around (end_date = sysdate):

create or replace trigger Miembros_V_IOD
instead of delete on Miembros_V 
for each row
begin
    update Miembros set end_date = sysdate
    where Miembros.nick = :old.nick
    and Miembros.club = :old.club;
end;
/

View MIEMBROS_V created.

db<>fiddle

The syntax diagram in the documentation shows that parentheses can go around a list of columns on the left-had side of the equals sign, or around a subquery on the right; but not around the whole set clause. Because you have set (end_date it's expecting that to either have a comma or closing parenthesis next, i.e. set (end_date) = ... - hence the ORA-00907 being thrown when it didn't see that.

Upvotes: 2

Related Questions