Styjun

How to check participants in at events?

Identify a stage play about a VR experience in which participants are encouraged to simulate performing horrific activities

Is a naturally all "male" species possible?

What do you call the infoboxes with text and sometimes images on the side of a page we find in textbooks?

Organic chemistry Iodoform Reaction

What should I use for Mishna study?

Is it okay / does it make sense for another player to join a running game of Munchkin?

How will losing mobility of one hand affect my career as a programmer?

How to deal with or prevent idle in the test team?

How did Monica know how to operate Carol's "designer"?

Why isn't KTEX's runway designation 10/28 instead of 9/27?

Is there a good way to store credentials outside of a password manager?

A car is moving at 40 km/h. A fly at 100 km/h, starts from wall towards the car(20 km away)flies to car and back. How many trips can it make?

Is there enough fresh water in the world to eradicate the drinking water crisis?

Superhero words!

Visiting the UK as unmarried couple

Why are all the doors on Ferenginar (the Ferengi home world) far shorter than the average Ferengi?

How do ultrasonic sensors differentiate between transmitted and received signals?

What would you call a finite collection of unordered objects that are not necessarily distinct?

Resetting two CD4017 counters simultaneously, only one resets

Is there an wasy way to program in Tikz something like the one in the image?

How to prevent YouTube from showing already watched videos?

A known event to a history junkie

Female=gender counterpart?

mysql trigger information from a one to many relation insert and update triggers

MyISAM versus InnoDB“INSERT IGNORE” vs “INSERT … ON DUPLICATE KEY UPDATE”How to 'insert if not exists' in MySQL?Insert into a MySQL table or update if existsMYSQL Trigger works if I use 'after update' but not 'after insert'?MySQL - Trigger for updating same table after insertMySQL Trigger. Should I use INSERT or UPDATEInsert row into another table via mySQL triggerTrigger insert on related table mysqlMySQL trigger issue on insert

1

i have 3 tables: a,b and c. table a is related with table b with a one to many relaion and table c is the table that get the triggered infromation from the 2 other tables.

 table a
 ------------------
 id | name
 1 | Juan 
 2 | Pete
 3 | David

 table b
 ----------------------
 id | name | table_a_id
 1 | Dog | 1
 2 | Cat | 2

Table C gets the information from Table A with a "after insert on A
for each row insert into C "also gets information From table B with a "after insert on B for each row update C".

 table c
----------------------
id | name a | name b | table_a_id |table_b_id
 1 | juan | Dog | 1 | 1
 2 | Pete | Cat | 2 | 2

The problem occurs when Pete wants to have Cat and Dog at the same time because it will just update Pete's information. What i want is a way in which i can have a new row with all the information.

 table c
 -------------------------------------------
 id | name a | name b | table_a_id |table_b_id
 1 | juan | Dog | 1 | 1
 2 | Pete | Cat | 2 | 2
---->3 | Pete | Dog | 2 | 1

This 3rd row from table C wont be created it will just update row 2 from table C.

mysql triggers

share|improve this question

asked Mar 21 at 14:37

cirlordcirlord

123

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It does not appear sensible to insert from table a to table c.I suggest a better approach would be to have a foreign key on tablea to table a based on table a id and have an insert trigger on tableb to table c.
  
  – P.Salmon
  Mar 21 at 16:10
  

  
  
  
  

add a comment | 

1

i have 3 tables: a,b and c. table a is related with table b with a one to many relaion and table c is the table that get the triggered infromation from the 2 other tables.

 table a
 ------------------
 id | name
 1 | Juan 
 2 | Pete
 3 | David

 table b
 ----------------------
 id | name | table_a_id
 1 | Dog | 1
 2 | Cat | 2

Table C gets the information from Table A with a "after insert on A
for each row insert into C "also gets information From table B with a "after insert on B for each row update C".

 table c
----------------------
id | name a | name b | table_a_id |table_b_id
 1 | juan | Dog | 1 | 1
 2 | Pete | Cat | 2 | 2

The problem occurs when Pete wants to have Cat and Dog at the same time because it will just update Pete's information. What i want is a way in which i can have a new row with all the information.

 table c
 -------------------------------------------
 id | name a | name b | table_a_id |table_b_id
 1 | juan | Dog | 1 | 1
 2 | Pete | Cat | 2 | 2
---->3 | Pete | Dog | 2 | 1

This 3rd row from table C wont be created it will just update row 2 from table C.

mysql triggers

share|improve this question

asked Mar 21 at 14:37

cirlordcirlord

123

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It does not appear sensible to insert from table a to table c.I suggest a better approach would be to have a foreign key on tablea to table a based on table a id and have an insert trigger on tableb to table c.
  
  – P.Salmon
  Mar 21 at 16:10
  

  
  
  
  

add a comment | 

i have 3 tables: a,b and c. table a is related with table b with a one to many relaion and table c is the table that get the triggered infromation from the 2 other tables.

 table a
 ------------------
 id | name
 1 | Juan 
 2 | Pete
 3 | David

 table b
 ----------------------
 id | name | table_a_id
 1 | Dog | 1
 2 | Cat | 2

Table C gets the information from Table A with a "after insert on A
for each row insert into C "also gets information From table B with a "after insert on B for each row update C".

 table c
----------------------
id | name a | name b | table_a_id |table_b_id
 1 | juan | Dog | 1 | 1
 2 | Pete | Cat | 2 | 2

The problem occurs when Pete wants to have Cat and Dog at the same time because it will just update Pete's information. What i want is a way in which i can have a new row with all the information.

 table c
 -------------------------------------------
 id | name a | name b | table_a_id |table_b_id
 1 | juan | Dog | 1 | 1
 2 | Pete | Cat | 2 | 2
---->3 | Pete | Dog | 2 | 1

This 3rd row from table C wont be created it will just update row 2 from table C.

mysql triggers

share|improve this question

asked Mar 21 at 14:37

cirlordcirlord

123

 table a
 ------------------
 id | name
 1 | Juan 
 2 | Pete
 3 | David

 table b
 ----------------------
 id | name | table_a_id
 1 | Dog | 1
 2 | Cat | 2

 table c
----------------------
id | name a | name b | table_a_id |table_b_id
 1 | juan | Dog | 1 | 1
 2 | Pete | Cat | 2 | 2

 table c
 -------------------------------------------
 id | name a | name b | table_a_id |table_b_id
 1 | juan | Dog | 1 | 1
 2 | Pete | Cat | 2 | 2
---->3 | Pete | Dog | 2 | 1

share|improve this question

asked Mar 21 at 14:37

cirlordcirlord

123

share|improve this question

asked Mar 21 at 14:37

cirlordcirlord

123

asked Mar 21 at 14:37

cirlordcirlord

123

asked Mar 21 at 14:37

cirlordcirlord

123

1 Answer
1

active

oldest

votes

0

instead of having a physical copy of table c, you should create a "virtual table", or a view.

For example:

CREATE VIEW table_c AS (
 SELECT
 t1.id AS a_id,
 t1.`name` AS a_name,
 t2.id AS b_id,
 t2.`name` AS b_name
 FROM table_a t1
 LEFT JOIN table_b t2
 ON (t2.table_a_id = t1.id)
) 

Now you can fetch rows from this "table" like this:

SELECT * FROM table_c

share|improve this answer

answered Mar 21 at 21:36

JaneJane

1,127518

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  thanks Jane! this might work, but if new information is in t1 and t2 it will show that information?
  
  – cirlord
  Mar 22 at 15:01
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @cirlord yes. It should show all rows that your example table_c shows. If you are having problems setting it up update your code with what you tried and I can walk you through it.
  
  – Jane
  Mar 22 at 16:01
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  okay, sounds good thanks for your help!
  
  – cirlord
  Mar 22 at 20:19
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55282915%2fmysql-trigger-information-from-a-one-to-many-relation-insert-and-update-triggers%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

instead of having a physical copy of table c, you should create a "virtual table", or a view.

For example:

CREATE VIEW table_c AS (
 SELECT
 t1.id AS a_id,
 t1.`name` AS a_name,
 t2.id AS b_id,
 t2.`name` AS b_name
 FROM table_a t1
 LEFT JOIN table_b t2
 ON (t2.table_a_id = t1.id)
) 

Now you can fetch rows from this "table" like this:

SELECT * FROM table_c

share|improve this answer

answered Mar 21 at 21:36

JaneJane

1,127518

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  thanks Jane! this might work, but if new information is in t1 and t2 it will show that information?
  
  – cirlord
  Mar 22 at 15:01
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @cirlord yes. It should show all rows that your example table_c shows. If you are having problems setting it up update your code with what you tried and I can walk you through it.
  
  – Jane
  Mar 22 at 16:01
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  okay, sounds good thanks for your help!
  
  – cirlord
  Mar 22 at 20:19
  

  
  
  
  

add a comment | 

0

instead of having a physical copy of table c, you should create a "virtual table", or a view.

For example:

CREATE VIEW table_c AS (
 SELECT
 t1.id AS a_id,
 t1.`name` AS a_name,
 t2.id AS b_id,
 t2.`name` AS b_name
 FROM table_a t1
 LEFT JOIN table_b t2
 ON (t2.table_a_id = t1.id)
) 

Now you can fetch rows from this "table" like this:

SELECT * FROM table_c

share|improve this answer

answered Mar 21 at 21:36

JaneJane

1,127518

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  thanks Jane! this might work, but if new information is in t1 and t2 it will show that information?
  
  – cirlord
  Mar 22 at 15:01
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @cirlord yes. It should show all rows that your example table_c shows. If you are having problems setting it up update your code with what you tried and I can walk you through it.
  
  – Jane
  Mar 22 at 16:01
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  okay, sounds good thanks for your help!
  
  – cirlord
  Mar 22 at 20:19
  

  
  
  
  

add a comment | 

instead of having a physical copy of table c, you should create a "virtual table", or a view.

For example:

CREATE VIEW table_c AS (
 SELECT
 t1.id AS a_id,
 t1.`name` AS a_name,
 t2.id AS b_id,
 t2.`name` AS b_name
 FROM table_a t1
 LEFT JOIN table_b t2
 ON (t2.table_a_id = t1.id)
) 

Now you can fetch rows from this "table" like this:

SELECT * FROM table_c

share|improve this answer

answered Mar 21 at 21:36

JaneJane

1,127518

CREATE VIEW table_c AS (
 SELECT
 t1.id AS a_id,
 t1.`name` AS a_name,
 t2.id AS b_id,
 t2.`name` AS b_name
 FROM table_a t1
 LEFT JOIN table_b t2
 ON (t2.table_a_id = t1.id)
) 

SELECT * FROM table_c

share|improve this answer

answered Mar 21 at 21:36

JaneJane

1,127518

answered Mar 21 at 21:36

JaneJane

1,127518

answered Mar 21 at 21:36

JaneJane

1,127518