>-----Original Message----- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] >On Behalf Of Clemens Ladisch >Sent: Thursday, March 06, 2014 11:29 AM >To: sqlite-users@sqlite.org >Subject: Re: [sqlite] Instead Of Insert Trigger Error
>Tilsley, Jerry M. wrote: >> I have the schema: >> CREATE TABLE panel_tracker (ov_id numeric, mt_acct char(12), orm_id >> numeric, panel_code char(5)); >Really? You don't have a "ov_systems" table? Actually I do have that table, but it's a small and I'm only doing a basic select on it and was pretty sure that wasn't the problem. Therefore, no need to include the schema for that in the e-mail. I just include the schema for the area of concern. >> I would like to create the following INSTEAD OF INSERT trigger: >> create trigger insteadInsertPanelTracker instead of insert on >> panel_tracker begin set @ov_id = select ov_id from ov_systems where >> mt_mnemonic=NEW.ov_id; insert into panel_tracker values (@ov_id, >> NEW.mt_acct, NEW.orm_id, NEW.panel_code); end ; >This is not valid in SQLite. >There are no variables; you would have to put that into a subselect. >And SQLite does not allow you to put INSTEAD OF triggers on a table. The documentation on Sqlite.org seems to indicate otherwise with its flow chart for the CREATE TRIGGER function, otherwise I would not have tried that as the SQL for other databases actually has a different syntax. >If you really want to do this, you have to create a view for that table, and >create INSTEAD OF triggers for all of INSERT/UPDATE/ >DELETE: At this point it is probably just easier to do an initial call to the database to lookup the value I want, then send in the insert. I thought a trigger would be a nice way to keep my other code cleaner, but I guess not. >create trigger insteadInsertPanelTracker instead of insert on panel_tracker >begin > insert into the_actual_panel_tracker_table > values ((select ov_id from ov_systems where mt_mnemonic=NEW.ov_id), > NEW.mt_acct, NEW.orm_id, NEW.panel_code); end; >> Disclaimer**** >> This email is confidential ... Thanks for your input anyway. Jerry ________________________________ Disclaimer**** This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of St. Claire Regional Medical Center. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing or copying of the email is strictly prohibited. If you received this email in error please notify the St. Claire Regional Helpdesk by telephone at 606-783-6565. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
