Trigger that Inserts to another table when new record added

Question

I have 2 tables. The Data of profit table will come from the bet table. Therefore, automating INSERT to profit table whenever new record got added on bet table will be convenient.

How can I make sure the trigger will insert only data that doesn't exist on the profit table?

Here's what I did so far

DELIMITER $$
DROP TRIGGER IF EXISTS tsuika $$
CREATE TRIGGER keisan BEFORE INSERT ON profitdb
FOR EACH ROW BEGIN
INSERT INTO `profitdb`(`BetID`,`DateTime`, `PlayerID`,
`Profit`,`SubAgentID`,`SubAgentRisk`,`AgentID`,`AgentRisk`)
SELECT `betdb`.`BetID`,`betdb`.`DateTime`,`betdb`.`PlayerID`,
(`BetAmount`-Payout`),`playerdb`.`SubAgentID`,`subagentdb`.`Risk`,
`agentdb`.`AgentID`,`agentdb`.`Risk` FROM `betdb` LEFT JOIN `playerdb` ON
`betdb`.`PlayerID` = `playerdb`.`PlayerID` LEFT JOIN `subagentdb` ON
`subagentdb`.`SubAgentID` = `playerdb`.`SubAgentID` LEFT JOIN `agentdb` ON
`agentdb`.`AgentID` = `playerdb`.`AgentID`
END
$$
DELIMITER;

I referred to this link MySQL trigger On Insert/Update events regarding making triggers with INSERT but still getting this error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 5

Ravinder Reddy · Answer 1 · 2014-06-25T06:19:50.677

0

You must end all executable statements with default delimiter, i.e. ';' semi-colon in a code block. You missed a semi-colon after select statement.

And you are trying to drop an irrelevant trigger 'tsuika'. It should be the current trigger you are trying to create or re-define. Change it to 'keisan'.

DELIMITER $$

DROP TRIGGER IF EXISTS keisan; $$ -- <--- this was with wrong trigger name. corrected.

CREATE TRIGGER keisan BEFORE INSERT ON profitdb
FOR EACH ROW BEGIN
  INSERT INTO `profitdb`(`BetID`,`DateTime`, `PlayerID`, `Profit`,  
                         `SubAgentID`,`SubAgentRisk`,`AgentID`,`AgentRisk`)
         SELECT `betdb`.`BetID`, `betdb`.`DateTime`, `betdb`.`PlayerID`,
                ( `BetAmount` - `Payout` ), `playerdb`.`SubAgentID`,
                `subagentdb`.`Risk`, `agentdb`.`AgentID`, `agentdb`.`Risk` 
           FROM `betdb` 
                 LEFT JOIN `playerdb` 
                        ON `betdb`.`PlayerID` = `playerdb`.`PlayerID` 
                 LEFT JOIN `subagentdb` 
                        ON `subagentdb`.`SubAgentID` = `playerdb`.`SubAgentID` 
                 LEFT JOIN `agentdb` 
                        ON `agentdb`.`AgentID` = `playerdb`.`AgentID`; -- <--- missed
END;

$$

DELIMITER ;

edited Jun 25 '14 at 06:19

answered Jun 25 '14 at 05:18

Ravinder Reddy

23,692
6
52
82

Your drop trigger statement is wrong. Change it to `drop trigger if exists keisan;` – Ravinder Reddy Jun 25 '14 at 06:13
I corrected the part `CREATE TRIGGER keisan...` to `CREATE TRIGGER tsuika...` but still get the error `#1235 - This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'` – leipzy Jun 25 '14 at 06:14
It means that your trigger is not dropped. Drop a relevant trigger if exists. Check my updated answer. – Ravinder Reddy Jun 25 '14 at 06:17
the table has no triggers and the intended name is tsuika, so I used tsuika. – leipzy Jun 25 '14 at 06:22
What is the output for `drop trigger tsuika;` without using `if exists`. – Ravinder Reddy Jun 25 '14 at 06:24
btw the target table named profit has triggers, does it affect this? – leipzy Jun 25 '14 at 06:25
No. It should not. By the way are you trying to define `insert trigger` on the same table in which you are executing another `insert` in the trigger body? If yes, it is not going to work. It should just have `set ...` statements. – Ravinder Reddy Jun 25 '14 at 06:28
I'm defining the trigger in the bet table, and without `drop trigger` this shows `#1360 - Trigger does not exist ` – leipzy Jun 25 '14 at 06:48
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/56260/discussion-between-ravinder-and-setsuna). – Ravinder Reddy Jun 25 '14 at 09:21

Trigger that Inserts to another table when new record added

1 Answers1