Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm working on the exercises for db-class.org where we have to write a trigger that makes new students named 'Friendly' automatically like everyone else in their grade. That is, after the trigger runs, we should have ('Friendly', A) in the Likes table for every other Highschooler A in the same grade as 'Friendly'.

This is being done in sqlite on the following dataset. Now my trigger works, but it only adds one person that 'Friendly' likes rather than all of them (those with the same grade).

Here's my trigger:

CREATE Trigger Q1
After Insert on Highschooler
For Each Row
When New.Name = 'Friendly'
Begin
    INSERT Into Likes Values (New.ID, (
    SELECT Highschooler.ID FROM Highschooler
    WHERE Highschooler.Grade = New.Grade));
End;

I suspect there's something wrong with my INSERT query, because in isolation the SELECT returns all the IDs. So how do I make sure it inserts all the IDs with matching grades?

share|improve this question
    
whats very interesting here is that SQLlite doesn't throw a SQL error IMO –  Jack Douglas Nov 17 '11 at 16:51
add comment

1 Answer 1

up vote 10 down vote accepted

On most platforms your insert would throw an error if your subselect returns more than one row, rather than just insert the 'first' returned row (and 'first' is undefined without an order by clause).

What you are probably after is an insert like this:

INSERT Into Likes 
SELECT New.ID, ID 
FROM Highschooler 
WHERE Grade = New.Grade and ID<>New.ID;
share|improve this answer
    
Yep! This solves my problem, it inserts all combinations rather than just the first. Thanks! –  Ivo Flipse Nov 17 '11 at 21:38
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.