1290 1290 - 7 months ago 20
SQL Question

SQL On Delete Trigger

I am trying to create a trigger that when a row is deleted from a table it gets inserted in another table:

1 Create Or Replace Trigger cancel
2 After Delete
3 On OrderTable
4 For EACH ROW
5 Begin
6 Insert Into CancelledOrders Values (:old.acctNum, age, phone)
7 From OrderTable Natural Join Customer
8 Where acctid = :old.acctNum AND menuid = :old.menuNum;
9 End;
10 /
11 Show Errors;


I want to grab the
acctNum
,
age
, and
phone
. The
acctNum
is from the Order table but the
age
and
phone
is from the Customer table. Therefore I join the two tables (on the
acctid
key). So the joined result will look like this:

acctNum Age Phone


I get this error when I try to compile the Trigger:

2/2 PL/SQL: SQL Statement ignored
3/2 PL/SQL: ORA-00933: SQL command not properly ended


Does anyone know the problem?

EDIT:
Table Structure:

OrderTable:
AcctNum startOrder endOrder

Customer Table:
AcctNum age phone

Answer

You're mixing the values and select (subquery) syntax, which are for different things. You can insert from a query that does the join:

    Insert Into CancelledOrders -- (acctNum, age, phone)
    Select :old.acctNum, age, phone
    From Customer
    Where acctNum = :acctNum
    AND menuid = :old.menuNum;

It's better to specify the columns in the target table as part of the insert clause (I've left that commented out in case the names are different). And it's also better to indicate which table the values are from, both in the select list and the where clause. You also don't want (or need) to requery the table the trigger is against; you already have the data you need, and it will get a mutating-table error in some circumstances.