MYSQL Trigger

J C
J C used Ask the Experts™
on
I have two tables. Table1 is my Task table and Table2 is my Time table. I am new to mysql and was hoping someone here could provide me an example of a trigger that...When a record is added to the Task table to have a trigger that would create a record in my time table as well. I need to pass a few variables. I need Task.TASKID to be passed to Time.TASKID as well as other values of fields in the Task table. Can anyone help with this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
something along these lines should be a start (presuming you want to update ...)
create trigger trg_update_table
after insert on table1
for each row
begin
  update table2
     set col1 = new.col1
       , col2 = new.col2
    where taskId = new.taskID
  ;
end;

Open in new window

J C

Author

Commented:
It will actually be an Insert. The record does not exist in the Time table. I am create a a record that I can link to in the Time table based on the TaskID
J C

Author

Commented:
*creating
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
well, without knowing "all" the needed details of the tables, I can only repeat:
see what I postead above, instead of UPDATE , it will be a INSERT statment, of which I presume you know the syntax.
the only detail you need to "use" is the NEW.COLUMN_NAME, where NEW is a fixed name, referring to the virtual table of the inserted/updated records in the table the trigger is created on, and the COLUMN_NAME can be any of the columns of that table,,,
J C

Author

Commented:
Can I insert directly into the Time table without needing a virtual table. I am new to mysql and databases in general so I am sorry for what is probably a stupid question. Should this work?


create trigger trg_update_table
after insert on Task
for each row
begin
  insert Time
     set TaskID = Time.TaskID
       , TechID = Time.TechID
  ;
end;
 

Open in new window

Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
that "virtual" table is a concept in the trigger, which you need. no way to get around, and it's actually the most efficient way ...
create trigger trg_update_table
after insert on Task
for each row
begin
  insert into `Time` (TaskID, TechID)
     VALUES ( NEW.TaskID, NEW.TechID)
  ;
end;
 

Open in new window

J C

Author

Commented:
Thanks a lot man, that works perfectly. Thank you for sharing your knowledge and helping a newb like me out. This is really helpful!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial