CODEWITHSUNDEEP
mysqltriggers
user1133648
user1133648

Reputation:

How to create a trigger for before/after delete with update/insert in mysql?

I have two tables like as:

fee_master(id,cTId,feeType,amount,startDate,lastDate,fine_last_date,fine,status) 
payroll(id,emId,date,loan,netSalary)

I am trying to create a trigger like as:

DELIMITER $$
DROP TRIGGER IF EXISTS test
DELIMITER $$;
 CREATE TRIGGER test
    BEFORE DELETE ON fee_master
    FOR EACH ROW
          UPDATE payroll SET loan=OLD.amount,netSalary=OLD.fine WHERE id=18;
DELIMITER $$;

delete from fee_master where id='18';

When I have run this trigger, the data is deleted from fee_master, but payroll is not updated, also I have tried to insert payroll but not working.Every times the data is deleted from fee_master.

If I change the update and delete query position with trigger then It is ok. Actually, It is not working on trigger operation. What is the problem ?

Upvotes: 0

Views: 1570

Answers (2)

Kabir Hossain
Kabir Hossain

Reputation: 3105

May be you are new on triggering.

According to your question, I recommend you first read basics of triggering from here http://www.sitepoint.com/how-to-create-mysql-triggers/

Remind that, It is a stored process. You do not need to run the trigger every times,I hope you are confuse here. After creating a trigger, You have to run the master query then the trigger automatically run the next operation.

Your code is ok. And the code of Barmar also ok.The main problem your understanding.

Upvotes: 0

Barmar
Barmar

Reputation: 781096

Your syntax for UPDATE is incorrect. Multiple assignments are separated by ,, not AND.

UPDATE payroll SET loan=OLD.amount, netSalary=OLD.fine WHERE id=18;

Upvotes: 1

Related Questions