Open phpMyAdmin, and then click “SQL” tab to open its webpage, and then put in the sentences as below:
CREATE TRIGGER trigger1 AFTER INSERT ON table1
FOR EACH ROW BEGIN
INSERT INTO table2 SET t2_id = NEW.t1_id;
END;
Note: change “;” to “//” in the space behind “Delimiter”.
This trigger inserts a new record when table1 is inserted in a new record, and set the value of t2_id of the new record in table 2 to the value of new record of t1_id of table 1.
At the beginning, I used the sentence below, and the value of t2_id always 1. It works well until I
delete “and sum =1“.
A trigger example for deleting:
CREATE TRIGGER `trigger2` AFTER DELETE ON `table1`
FOR EACH ROW BEGIN
DELETE FROM table2 WHERE t2_id = OLD.t1_id;
UPDATE table3 SET sum = sum+1 WHERE t3_id = NEW.t1_id;
END;
The example below is to caculate the records of the old_table and transfer its result to new_table:
INSERT INTO new_table
SELECT *
FROM (select ” as id, gid, count(gid) as sum from old_table group by gid) as aa
The examples all works perfectly in my computer, but when I transferred them on the web hosting space,
I found that my account has no super privilege to use triggers and I have to delete them all.
— Trigger DDL Statements
DELIMITER $$
USE `vlifedb`$$
create trigger ins_groups
before insert on groups
for each row
begin
select * into `#temp` from actions;
while ((select count(*) from `#temp`)>0)
select top 1 id_acion=actions_id from `#temp`;
insert into rules(groups_id,actions_id) values(NEW.idgroups,id_acion);
delete `#temp` where idaction=id_action;
end while;
end
$$