Thursday, February 21, 2013

SQL Update a Multi-Table View using INSTEAD OF Triggers

Yesterday I added another SQL trick to my bag ‘o tricks. I am currently working on fixing EDI invoicing process and I needed a way to delete records from four files based on the same key. I could have wrote RPGLE Free Embedded SQL that would have been about 60 lines of code but wheres the fun in that.

I popped open the Google window and searched on “iseries sql delete from multiple tables”. the second hit directed me to IBM and the fourth to Preform the Impossible: Update a Multi-table View using INSTEAD OF Triggers. What did we did before Google, oh yeah, read a manual!

Impossible, that’s for me. It took me a few minutes to understand and a peek at IBM documentation I had to create a view and then the trigger. Once created any time I delete from the view all associated records in the four files are deleted. Very cool.

After view and trigger created I can run this SQL interactive or embedded.

delete from yourlib.ediinvw
where ihedoc = 0000046475

My new view is EDIINVW and I am deleting by EDI document control number.

To accomplish this I created the view with the below code using SQL Explorer.

create view yourlib.ediinvw as
select ih.ihedoc, id.idedoc, ss.iaedoc, si.isedoc
from yourlib.vedinih ih
join yourlib.vedinid id on id.idedoc = ih.ihedoc
join yourlib.vediniss ss on ss.iaedoc = ih.ihedoc
join yourlib.vedinis si on si.isedoc = ih.ihedoc

The four fields in the Select statement is the key field for each of the four files.

I then attempted to create the delete Trigger. SQL Explorer did not like the syntax so I copied and pasted into the green screen iSeries SQL interactive command line. My guess is that SQL Explorer has an issue with INSTEAD OF DELETE ON.

create trigger yourlib.ediinvdel
Instead of delete on yourlib.ediinvw
referencing old as EdiInvwOld
for each row mode DB2SQL
begin atomic
delete from yourlib.vedinih
where ihedoc = EdiInvwOld.ihedoc;
delete from yourlib.vedinid
where idedoc = EdiInvwOld.idedoc;
delete from yourlib.vedinid
where iaedoc = EdiInvwOld.iaedoc;
delete from yourlib.vedinid
where isedoc = EdiInvwOld.isedoc;
end;

I learned another very cool SQL trick, Thanks IT JUNGLE and Four Hundred Guru. This will come in handy over the next few weeks as I fix the EDI processes.

Have your best day!

~Richard


In programming, as in everything else, to be in error is to be reborn.  ~Alan J. Perlis

4 comments:

  1. ooops....I made a small mistake copying code and forgot to change the file names for file 3 and 4.

    create trigger r50mods/ediinvdel
    Instead of delete on ediinvw
    referencing old as EdiInvwOld
    for each row mode DB2SQL
    begin atomic
    delete from vedinih
    where ihedoc = EdiInvwOld.ihedoc;
    delete from vedinid
    where idedoc = EdiInvwOld.idedoc;
    delete from vediniss
    where iaedoc = EdiInvwOld.iaedoc;
    delete from vedinis
    where isedoc = EdiInvwOld.isedoc;
    end

    ReplyDelete
  2. ooppps again....it's tough teaching yourself how things work...the library needs to be qualified. Duh! The trigger in on the view so I have to create two triggers one for production file and one for test file. This probably will not work for test since I refresh test every night from production. SQL and the library list are don't seem to work as I know it.

    create trigger r50mods/ediinvdel
    Instead of delete on b50files/ediinvw
    referencing old as EdiInvwOld
    for each row mode DB2SQL
    begin atomic
    delete from b50files/vedinih
    where ihedoc = EdiInvwOld.ihedoc;
    delete from b50files/vedinid
    where idedoc = EdiInvwOld.idedoc;
    delete from b50files/vediniss
    where iaedoc = EdiInvwOld.iaedoc;
    delete from b50files/vedinis
    where isedoc = EdiInvwOld.isedoc;
    end

    ReplyDelete