Database Trigger in PHPMyAdmin

[TheNoLifer]

Does anyone have any experience with Triggers in PHPMyAdmin? I have a "dummy" table for lookups in my AppGini which I'm trying to pre-populate. Each part of the SQL works independently - but I can't figure out how to create a trigger. Something is off in my syntax I think.

What this does (or is meant to do!) is whenever someone adds a row via AppGini to my ContentItem_Persons table, run a truncate on the Relationship_List table, then insert some values, then do a sort of cross-apply in SQL, a 1 = 1 join which will build my table with one row for every one of the values I just inserted. It works independently in testing.

But when I run this trigger I get 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 'CREATE TRIGGER person_relationships
AFTER INSERT ON ContentItem_Persons
FOR EA' at line 2

What on earth does that mean!?

DELIMITER $$
DROP TRIGGER IF EXISTS person_relationships;
CREATE TRIGGER person_relationships
AFTER INSERT ON ContentItem_Persons
FOR EACH ROW BEGIN
TRUNCATE TABLE relationship_list;
INSERT INTO relationship_list (relationship)
VALUES
('Husband of'),
('Wife of'),
('Mother of'),
('Father of'),
('Daughter of'),
('Son of'),
('Brother of'),
('Sister of'),
('Grandmother of'),
('Grandfather of'),
('Granddaughter of'),
('Grandson of'),
('Employee of'),
('Employer of'),
('Heir of'),
('Trustee of');
INSERT INTO relationship_list (person, person_id, relationship)
(SELECT CONCAT_WS(' ', ContentItem_Persons.id, ' - ', ContentItem_Persons.title, ContentItem_Persons.forename, ContentItem_Persons.surname) AS person, ContentItem_Persons.id AS person_id, relationship_list.relationship FROM relationship_list LEFT OUTER JOIN ContentItem_Persons ON 1 = 1);
END; $$
DELIMITER ;


I have tried all sorts of syntax - single quotes on trigger name, etc. Totally stumped.

Appreciate any comments or suggestions!

[AhmedBR]

I cannot help you with Trigger (as I have never used them) but this is how I do it to keep everything in AppGini itself (makes things easy to control):

Create a Function with this trigger in cannot help you with Trigger (as I have never used them) but this is how I do it:

1. Create a function in __global.php

2. Call this function in Hook after Insert in table ContentItem_Persons table

Would be easier to find the error.

hope this helps or till someone gives you an answer about trigger.

[TheNoLifer]

Hi Ahmed!

Thank you! I didn't think about using AppGini to do this. I will look into that as a solution also!

Alasdair

[AhmedBR]

(sorry for the messed up post up there, I must have clicked something before submitting, but you got the idea).
By the way you can also use the formula directly in Hook after insert if you will not need it in other places.

[TheNoLifer]

No problem! Thank you for your help. I edited the hook and added my code to truncate the table and load the rows after an insert into the Persons table and it's working really well!

Cheers,

A.