CODEWITHSUNDEEP
oracle-databaseplsql
user1378863
user1378863

Reputation: 189

Trigger that inserts into a table when a new account is opened

customers:
+------------+--------------+ 
| cid        | Name         | 
+------------+--------------+ 
| 1          | Bob          |
| 2          | John         | 
| 3          | Jane         | 
+------------+--------------+ 
accounts:
+------------+--------------+ 
| aid        | type         | 
+------------+--------------+ 
| 1          | Checking     |
| 2          | Saving       | 
| 3          | Checking     | 
+------------+--------------+ 
transactions:
+------------+--------------+--------------+--------------+ 
| tid        | cid          | aid          | type         |
+------------+--------------+--------------+--------------+ 
| 1          | 1            | 1            | Open         |
| 2          | 2            | 3            | Open         |
| 3          | 1            | 2            | Open         |
| 4          | 2            | 3            | Deposit      |
+------------+--------------+--------------+--------------+

I am trying to write a trigger that writes to a logs table when a new account is successfully opened.

Right now I have this:

CREATE OR REPLACE TRIGGER acc_opened
BEFORE INSERT ON transactions
FOR EACH ROW
DECLARE
    c_name customers.name%TYPE;
BEGIN
    IF :new.type = 'Open' THEN
        SELECT name into c_name
        FROM customers c
        WHERE c.cid = :new.cid;

        INSERT INTO logs (who, what) VALUES (c_name, 'An account has been opened');
END;
/

The code that I have doesn't work and don't know where to go from here.

The trigger completes, but when it fires, I get this error message:

PLS-00103: Encountered the symbol "END" when expecting one of the following: (begin case declare exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execut commit forall merge pipe purge

Upvotes: 1

Views: 229

Answers (1)

Justin Cave
Justin Cave

Reputation: 231741

As with your previous question, if you want to refer to a particular column of the new row of data, you need to use the :new pseudo-record. So, at a minimum, 

SELECT cid 
  INTO c_id
  FROM transactions t
 WHERE t.aid = aid;

would need to be

SELECT cid 
  INTO c_id
  FROM transactions t
 WHERE t.aid = :new.aid;

Beyond that, are you sure that the row exists in the transactions table before the row is inserted into the accounts tale? Assuming that you have normal foreign key constraints, I would generally expect that you would insert a row into the accounts table before inserting the row into the transactions table.

The name transactions also seems pretty odd. If that is really just mapping the customer ID to the account ID, transactions seems like a rather poor name. If that table actually stores transactions, I'm not sure why it would have a customer ID. But if it does store transactions, there must be some other table that maps customers to accounts.

In your updated trigger, you are missing the END IF statement

CREATE OR REPLACE TRIGGER acc_opened
  BEFORE INSERT ON transactions
  FOR EACH ROW
DECLARE
  c_name customers.name%TYPE;
BEGIN
  IF :new.type = 'Open' 
  THEN
    SELECT name 
      into c_name
      FROM customers c
     WHERE c.cid = :new.cid;

     INSERT INTO logs (who, what) 
       VALUES (c_name, 'An account has been opened');
  END IF;
END;

Upvotes: 0

Related Questions