I'm trying to create some database triggers post database created, by adding them to a sql file that is called from sqldb.map.
However, to create triggers, I need to use the delimiter command to change the delimiter to something besides the semi-colon. This fails, and the result trigger creation fails, both with the standard, you have a syntax error message.. I can run a simple sql statement this way, but it doesn't like delimiter.
Any thoughts?
delimiter //
CREATE TRIGGER `SHOW_ADD_NEWS_ITEM`
AFTER INSERT ON `show`
FOR EACH ROW
BEGIN
declare username varchar(255);
IF NEW.privacy_key IS NOT NULL AND NEW.privacy_key <> 'PRIVATE' and (NOT EXISTS (select id from feed_friend_activity
where location_identifier = NEW.uuid and user_id = NEW.user_id and event_type = 'published show')) THEN
select concat(user.first_name,user.last_name) into username from user where user.id = NEW.user_id;
insert into feed_friend_activity
(location_identifier,user_id,event_type,user_name,item_name,created_on)
values (NEW.uuid,NEW.user_id,'published show',username,NEW.name,NEW.created_on);
END IF;
END
//
