varchar and int syntax in sql

Question

CREATE TRIGGER leave_trigger
 AFTER INSERT ON leaveapplication
 FOR EACH ROW
 IF employeemaster.empcode='".$empcode."' THEN
 UPDATE employeemaster SET '".$leave_type=$leave_type."'-$numberofleave //I am facing problem here
 END IF;

Please help me correcting it syntactically GIVEN $leave_type is varchar and $numberofleave is integer.

Maks3w · Accepted Answer · 2012-09-13 07:59:56Z

1

You need save the field name in a different var

UPDATE employeemaster SET '".$fieldName."=".$leave_type."'-$numberofleave

answered Sep 13, 2012 at 7:59

Maks3w

6,5197 gold badges40 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Fluffeh · Accepted Answer · 2012-09-13 08:01:15Z

0

I am a little confused with what you are trying to do. Are you trying to create a trigger that will automatically do the following query?

UPDATE 
    employeemaster 
        SET leave_type=leave_type-".$numberofleave." 
        where empcode='".$empcode."'

Then I think you want to write the trigger as this:

CREATE TRIGGER leave_trigger
    AFTER INSERT ON leaveapplication
    FOR EACH ROW
        UPDATE employeemaster SET leave_type=leave_type-NEW.numberofleave where empCode=NEW.empCode
    END;

In the trigger, you don't want to use any variables from PHP with specifics. You want to insert the data into one table (leaveApplication) and then have the trigger use the data in that table to update the employeemaster. If you hardcode the data in the trigger, you will either need to create a trigger for every row of data in the employeemaster or the trigger won't work as expected.

edited Sep 13, 2012 at 8:01

answered Sep 13, 2012 at 7:49

Fluffeh

33.6k16 gold badges69 silver badges80 bronze badges

1 Comment

Fluffeh Over a year ago

@Aragorn Can you give this a whirl? I am guessing at some column names though, so you might want to adapt to your actual names.

Collectives™ on Stack Overflow

varchar and int syntax in sql

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related