CODEWITHSUNDEEP
phpmysql
Dandan
Dandan

Reputation: 21

trigger after insert on table 1, insert on table 2 with where condition column from table 1

My trigger is not working at all.

DELIMITER $$
CREATE TRIGGER leave_credit_nt
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
INSERT INTO nonteaching_leavecredit (IdNum, Date_Hired, Emp_Stat, Date_Leave_Ac, Vac_Leave, Sick_Leave)
VALUES (new.IdNum, new.Date_Hired, new.Emp_Stat, new.Date_Hired + INTERVAL 1 YEAR, 0, 0)
WHERE employees.Emp_Type = 'NON-TEACHING';
END $$

DELIMITER

I want that the trigger will insert values into my second table only those with employees.emp_type = 'NON-TEACHING'. Can someone help me get this trigger working?

emp_type is a column in my table employees.

Upvotes: 2

Views: 38

Answers (2)

JRD
JRD

Reputation: 1987

You can add conditional logic to your trigger with:

if new.Emp_Type = 'NON-TEACHING' then
    INSERT INTO nonteaching_leavecredit (IdNum, Date_Hired, Emp_Stat, Date_Leave_Ac, Vac_Leave, Sick_Leave)
    VALUES (new.IdNum, new.Date_Hired, new.Emp_Stat, new.Date_Hired + INTERVAL 1 YEAR, 0, 0);
end if;

Upvotes: 0

Harshit
Harshit

Reputation: 5157

Your INSERT query is wrong. MySQL INSERT Syntax does not support the WHERE clause so your query as it stands will fail.

You cant use WHERE condition with INSERT query. If you are using WHERE condition, it means you already have that row in the table. So , you should use UPDATE query instead of INSERT.

Upvotes: 3

Related Questions