0

with SQLITE I have 2 tables Employee (id,name,phone,taskname) and Tasks (id, name,date,Employee_id)

I want to add a trigger on After update of a row to get the taskname from the tasks table where employe_id is the same. I think that this will need to use SELECT INTO NEW.taskname but I can't seem to get it right. I tried.

CREATE TRIGGER "gettaskname"
AFTER UPDATE
ON "Employee"
BEGIN
SELECT Tasks.name FROM Tasks INTO NEW.task_name WHERE NEW.id = Tasks.Employee_id;
END;

I am getting a syntax error

Aness
  • 610
  • 1
  • 7
  • 24
  • 1
    Your double quotes aren't balanced. – Shawn Nov 20 '19 at 10:52
  • 1
    And what is `SELECT ... INTO ...`? That's not valid syntax. See https://www.sqlite.org/lang_select.html – Shawn Nov 20 '19 at 10:54
  • Hi @Shawn thanks for your reply. I updated the post it is not a quote problem, yes doesn't work I need something similar but with SQLite Any idea? – Aness Nov 20 '19 at 10:55
  • *to get the taskname* and do what with that taskname? – forpas Nov 20 '19 at 12:18
  • Hi @forpas this is a simplified example of a problem I am facing. my current project is too complex to post it involves a couple tables and some VCL component with very special restriction in front end. Basically I love embedding data (business) logic on backend via triggers but it is tricky with Sqlite. – Aness Nov 20 '19 at 12:29
  • 3
    You need to explicitly create the table. Then use an [INSERT... SELECT](https://www.sqlite.org/lang_insert.html) statement. There are some variations that might be useful like UPSERTS. It is worth familiarizing yourself with the overall [syntax specifics in sqlite](https://www.sqlite.org/lang.html). – C Perkins Nov 20 '19 at 12:45
  • @C Perkins Thanks for the link I will check up the doc. – Aness Nov 20 '19 at 12:48

1 Answers1

0

Thank you very much for your awesome comments. I found this thread creating variable inside trigger but it didn't work for me and i got sytax errors for using PRAGMA and CREATE TEMP TABLE .

This worked for me:

CREATE TRIGGER "gettaskname"
AFTER UPDATE
ON "Employee"
BEGIN
UPDATE Employee SET task_name = (SELECT Tasks.name FROM Tasks WHERE NEW.id = Tasks.Employee_id) WHERE id = NEW.id;
END;
Aness
  • 610
  • 1
  • 7
  • 24
  • *no body did answer* because you did not explain what you want to do. You posted code implying that you want to **insert** rows in the table but what you actually wanted was to **update** the rows of the table. – forpas Nov 21 '19 at 09:06