CODEWITHSUNDEEP
mysqltriggers
gungu
gungu

Reputation: 161

MySQL trigger to update other table

I have 2 tables:

table 1 = SAMPLE_TABLE

table 2 = RESULT_TABLE (with proposed trigger)

I would like to use a trigger in RESULT table that, when a new record is inserted into into it, will update a field in SAMPLE table called, 'status' = "complete". The field 'status' to be updated in SAMPLE is related to RESULT by:

table 1 = SAMPLE_TABLE ('client_sampleID')

=

table 2 = RESULT_TABLE ('sampleID')

This is the proposed trigger

CREATE DEFINER = `user`@`%` TRIGGER `database`.`RESULT_TABLE_BEFORE_INSERT` 
AFTER INSERT ON `RESULT_TABLE` FOR EACH ROW
BEGIN
UPDATE SAMPLE_TABLE
   SET status = 'complete'
 WHERE SAMPLE_TABLE.client_sampleID = RESULT_TABLE.sampleID;
END

My questions:

  1. is this above trigger OK?

  2. there are 100+ 'client_sampleID' (all same, entered as a batch) per 'sampleID'. Is there a more efficient way of setting the 'status' so that it happens only after encountering the first instance?

Upvotes: 0

Views: 30

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269593

You are very close. You just need to use new in the trigger:

CREATE DEFINER = `user`@`%` TRIGGER `database`.`RESULT_TABLE_BEFORE_INSERT` 
AFTER INSERT ON `RESULT_TABLE` FOR EACH ROW
BEGIN
    UPDATE SAMPLE_TABLE st
        SET status = 'complete'
        WHERE st.client_sampleID = new.sampleID;
END

Upvotes: 3

Related Questions