1290 1290 - 1 year ago 76
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
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
, and
. The
is from the Order table but the
is from the Customer table. Therefore I join the two tables (on the
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?

Table Structure:

AcctNum startOrder endOrder

Customer Table:
AcctNum age phone

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download