CODEWITHSUNDEEP
oracle-databasetriggers
MisterM
MisterM

Reputation: 39

Getting a value from a table inside a trigger

so I have to write a trigger for a movie rental store database that will log all rentals into a log table saying who rented what movie and when. FYI, this is in sql developer and Im using oracle sql.

Here is my trigger: 

create or replace trigger rentalLogging
after insert on rentals
for each row 
declare 
  customerName varchar2(50);
begin
  select c.name into customerName from customer c 
  join rentals r on c.CUST_ID = r.CUST_ID
  where c.CUST_ID = :NEW.cust_id;

  insert into log_movieRentals values 
  ('customer: ' || customerName || 'rented movie on: ' || current_date);
end;

I am getting an error when I try to insert into my rentals table saying the table is mutating and the trigger may not see it.

The reason I need to get the customer name and assign it to a variable is because my rentals table does not contain the customer name but only the customer id and I want to log the customer name in this case.

I thought of creating and using a function that takes a customer id and returns their name in this trigger but I am wondering if there's a way of getting the customer name in this trigger.

Upvotes: 0

Views: 1308

Answers (1)

Alex Poole
Alex Poole

Reputation: 191285

You don't need to include the rentals table in the query inside your trigger. All the data you need is already in the new pseudorecord, and in fact you don't use that table for anything except the join - and that would likely to cause the query to find multiple rows, if it was allowed at all.

You can just remove the join:

  select c.name into customerName from customer c 
  where c.CUST_ID = :NEW.cust_id;

Incidentally, you don't really need the local variable; you could do this in one step with:

  insert into log_movieRentals
  select 'customer: ' || c.name || 'rented movie on: ' || current_date
  from customer c
  where c.cust_id = :new.cust_id;

It is preferable to specify the target table's column name(s) as part of the table nsert statement too.

Upvotes: 3

Related Questions