Re: [sqlite] Instead Of Insert Trigger Error
On 2014/03/06 18:41, Tilsley, Jerry M. wrote: 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 ; 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. If I may offer some advice - In stead of telling us what you have done and asking what is wrong with it, why don't you simply state exactly what you want to achieve (along with all schemata), and ask what the best way to do so is? You will be amazed at the solutions some of these geniuses can come up with. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Instead Of Insert Trigger Error
>-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
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? > 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. 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: 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 ... This e-mail contains public information intended for any subscriber of this mailing list and for anybody else who bothers to read it; it will be copied, disclosed and distributed to the public. If you think you are not the intended recipient, please commit suicide immediately. These terms apply also to any e-mails quoted in, referenced from, or answering this e-mail, and supersede any disclaimers in those e-mails. Additionally, disclaimers in those e-mails will incur legal processing fees of $42 per line; you have agreed to this by reading this disclaimer. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Instead Of Insert Trigger Error
All, I have the schema: CREATE TABLE panel_tracker (ov_id numeric, mt_acct char(12), orm_id numeric, panel_code char(5)); 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 ; But I receive the following error: Error: cannot create INSTEAD OF trigger on table: main.panel_tracker Can you assist me in figuring this out? 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
