CODEWITHSUNDEEP
mysqlsql
user3303594
user3303594

Reputation: 61

Create MySQL Trigger on Insert

Currently I have 2 tables.

Table 1 = myit_table_customer - this table holds customer name, customer id, customer email

Table 2 = myit_table_work_order - this table holds customer name, customer id, customer email, ticket number, time, date etc...

My current problem is that whenever I create a work order, it inserts customer name, customer email, time, ticket number - auto increments, and date but it inserts (0) for the customer ID.

I would like to create a trigger IF on database insert new.email address = email address in myit_table_customer then get the customer ID for that customer from myit_table_customer and assign it to Customer_ID in myit_table_work_order

This is what I have so far:

CREATE TRIGGER tickets AFTER INSERT ON myit_table_work_order

FOR EACH ROW

  BEGIN

    IF CUSTOMER_ID IS NULL (SELECT CUSTOMER_ID FROM myit_table_customer WHERE CUSTOMER_ID = NEW.CUSTOMER_ID) THEN

      INSERT INTO myit_table_work_order (CUSTOMER_ID) VALUES (NEW.CUSTOMER_ID);

    END IF;

Thanks again.

Upvotes: 2

Views: 211

Answers (1)

fthiella
fthiella

Reputation: 49039

If I understand your question correctly, I think that you need a trigger like this one:

CREATE TRIGGER tickets
BEFORE INSERT ON myit_table_work_order
FOR EACH ROW
  SET NEW.customer_id = (SELECT id
                         FROM myit_table_customer
                         WHERE email=NEW.email);

Please see fiddle here.

Upvotes: 1

Related Questions