Create a Trigger in SQL

Hi there,

I hope you’re doing good!

I’m taking the SQL for Data Analysis course and I’m having trouble with the Trigger creation part.

The Task2 required to create a function that returns a trigger. Here is my code below:

create function avg_qty() returns trigger as $avg_amount$
  declare avg_amount integer;
  begin 
    select avg(qty) into avg_amount from order_info;
    insert into avg_qty_log(order_id, avg_qty) values (new.order_id, avg(qty));
    return new;
end;
$avg_amount$ language plpgsql;

I tried to insert the average of the sales amount into the table avg_amount that we created in the Task1. And then, I inserted to new values we just added, with the identifiable and the average.

The query is not right according to the Task.
Can someone give me a clue?

Thanks by advance!

Cheers,
Jacques

Hey Jacques!

Your code is almost right! There’s just one issue - the second value you are inserting should be avg_amount, rather than avg(qty), since you used select avg(qty) into avg_amount.

Best,
Lorraine

Hi Lorraine,

Thanks for getting me back again :slight_smile:

I tried your option, which make more sens but I can still not make it…

create function avg_qty() returns trigger as $avg_amount$
  declare avg_amount integer;
  begin 
    select avg(qty) into avg_amount from order_info;
    insert into avg_qty_log(order_id, avg_qty) values (new.order_id, avg_amount);
    return new;
end;
$avg_amount$ language plpgsql;

I also tried to insert the most new recent value of the average of the amount as ... values (new.order_id, new.avg_amount) but this is not working :frowning:

Thanks by advance Lorraine :slight_smile:

Best
Jacques

Hey Jacques,

Are you running psql smalljoins < Functions.sql in the terminal before you start the tasks?

What is the output when you run \df in the SQL Viewer?

image

Hey Lorraine!

I hope you’re well today :slight_smile:

Oh yeah, thanks, this is working now! That’s true that I didn’t run the code in the terminal before. But I don’t understand why we have to do it, because we don’t call any of these functions in the new one we’re creating. Or, this is maybe to activate them, because they are all related to each other?

That’s something it was difficult to understand to me in the theoretical course before to be honest :frowning:

Thanks again for your time :slight_smile:

Best,
Jacques