Why doesn’t INSERT AFTER Trigger add all results

sqlitetrigger

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?

Best Answer

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;

Related Solutions

Sql-server – Getting new and updated data with an archive column

How about making use of the OUTPUT virtual table? Set your transaction isolation level correctly (snapshot/serializable) so that you only see the rows as of the moment your process begins.

Use the following for your OLE DB Source

UPDATE T SET Archive = 1 OUTPUT DELETED.* FROM Table T WHERE T.Archive = 0;

That updates everything in a nice atomic operation with a side effect of generating the target output into your data flow buffers. Route that to your destination and it's done. Nice and neat

How to commit transaction on an after update event trigger

Why do you expect B to be updated, when you inserted a row with a different id? Actually, your INSERT ALL does not insert anything into B.

The above works fine after fixing your trigger and sample data:

create or replace trigger updB  
after update on B  
for each row  
declare  
v varchar2(5);  
begin  
  v := :new.val;
end;  
/

This is how you reference the new val in the trigger, not with a select. Your original trigger would run into the infamous ORA-04091 "table is mutating" error.

SQL> insert all into A values ('a', 'false') into b values ('a', 'false') select * from dual;

2 rows created.

SQL> commit;

Commit complete.

SQL> update A set val = 'true';
Cek cek: false

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from a;

ID         VAL
---------- ----------
a          true

SQL> select * from b;

ID         VAL
---------- ----------
a          true

For the output:

Your updA trigger runs in an autonomous transaction. When you select val from A, your original update is still not finished (commited), you will see the old value. Fixing your trigger for A:

create or replace trigger updA  
after update on A  
for each row  
declare  
pragma autonomous_transaction;  
cek varchar2(5);  
begin  
commit;  
  cek := :new.val;  
dbms_output.put_line('Cek cek: ' || cek);  
update B set val = :new.val where id = :new.id;  
commit;  
end;  
/

Best Answer

Related Solutions

Sql-server – Getting new and updated data with an archive column

How to commit transaction on an after update event trigger

Related Question