CODEWITHSUNDEEP
mysqltriggersphpmyadmin
leipzy
leipzy

Reputation: 12764

Mysql Trigger: Tried other suggestions but still get Error #1064

I've been looking at other stackoverflow questions here but cant seem to find my fault. I think its syntax-error related. On what part did I got wrong?

DELIMITER $$
DROP TRIGGER IF EXISTS keisan
CREATE TRIGGER keisan AFTER INSERT ON profitdb
FOR EACH ROW
BEGIN
DECLARE shinAgentPercent;
SET @shinAgentPercent:=`AgentRisk`-`SubAgentRisk`;
SET NEW.`SubAgentProfit` = `Profit`*(`SubAgentRisk`/100);
SET NEW.`AgentProfit` = `Profit`*(@shinAgentPercent/100);
END;
$$

Upvotes: 0

Views: 33

Answers (2)

Ravinder Reddy
Ravinder Reddy

Reputation: 24002

Firstly, you need a closing statement indication after drop ... command.

DROP TRIGGER IF EXISTS keisan $$

Secondly, I suggest you to go with a BEFORE INSERT trigger to set expression values for other columns of the tables.

Example: 

DELIMITER $$

DROP TRIGGER IF EXISTS keisan $$

CREATE TRIGGER keisan BEFORE INSERT ON profitdb
FOR EACH ROW BEGIN
    SET @shinAgentPercent  := NEW.AgentRisk - NEW.SubAgentRisk;

    SET NEW.SubAgentProfit := NEW.Profit * ( NEW.SubAgentRisk  / 100 );
    SET NEW.AgentProfit    := NEW.Profit * ( @shinAgentPercent / 100 );
END;

$$

-- now reset the delimiter to defaut
DELIMITER ;

Upvotes: 1

lc.
lc.

Reputation: 116498

Don't you need an end-of-statement after your DROP TRIGGER?

DROP TRIGGER IF EXISTS keisan$$

Upvotes: 0

Related Questions