4

Please help me with syntax for MySQL 5.6.

For some reason I get the error:

ERROR 1064 (42000): 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 '('WRONG SOCIAL: ', new.social);
        end if;
        if not (new.photo is nul'

When trying to run this SQL code:

create table table_social (
        sid varchar(250) primary key,
        auth char(32) not null,
        social integer,               -- to be checked by triggers
        female boolean,
        given varchar(250) not null,
        family varchar(250) null,
        photo varchar(1000) null,     -- to be checked by triggers
        place varchar(250) null,
        ip varchar(250) not null,
        uid integer not null references 
            table_users(uid) on delete cascade
);

delimiter //

create trigger social_insert_trigger before insert on table_social
for each row begin
        if new.social < 0 OR new.social > 6 then
                signal sqlstate '45000' 
                set message_text = concat('WRONG SOCIAL: ', new.social);
        end if;
        if not (new.photo is null OR new.photo regexp '^https?://') then
                signal sqlstate '45000' 
                set message_text = concat('WRONG PHOTO: ', new.photo);
        end if;
end
//

create trigger social_update_trigger before update on table_social
for each row begin
        if new.social < 0 OR new.social > 6 then
                signal sqlstate '45000' 
                set message_text = concat('WRONG SOCIAL: ', new.social);
        end if;
        if not (new.photo is null OR new.photo regexp '^https?://') then
                signal sqlstate '45000' 
                set message_text = concat('WRONG PHOTO: ', new.photo);
        end if;
end
//

I tried removing concat, replacing single quotes by double quotes, splitting signal line in two (as above) and back - and still can not find out, what is wrong.

| improve this question | | | | |
  • 1
    I don't have an instance to check this, but is it your delimiter? you have ^https?://, maybe try using $$ as a delimiter? – dwjv Nov 18 '15 at 12:56
  • 1
    You have the delimiter set to // and there is some occurrences of // in your code... ^https?://, for example – Philᵀᴹ Nov 18 '15 at 12:57
  • No, I have changed delimiter to $$$ and it still errors out. – Alexander Farber Nov 18 '15 at 13:15
12

Test this code:

drop table if EXISTS table_social;
create table table_social (
        sid varchar(250) primary key,
        auth char(32) not null,
        social integer,               -- to be checked by triggers
        female boolean,
        given varchar(250) not null,
        family varchar(250) null,
        photo varchar(1000) null,     -- to be checked by triggers
        place varchar(250) null,
        ip varchar(250) not null,
        uid integer not null references 
            table_users(uid) on delete cascade
);

delimiter //

create trigger social_insert_trigger before insert on table_social
for each row begin
        if new.social < 0 OR new.social > 6 then
                set @message_text = concat('WRONG SOCIAL: ', new.social);
                signal sqlstate '45000'
                set MESSAGE_TEXT = @message_text;
        end if;
        if not (new.photo is null OR new.photo regexp '^https?://') then
                set @message_text = concat('WRONG PHOTO: ', new.photo);
                signal sqlstate '45000' set message_text = @message_text;
        end if;
end
//

create trigger social_update_trigger before update on table_social
for each row begin
        if new.social < 0 OR new.social > 6 then
                set @message_text = concat('WRONG SOCIAL: ', new.social);
                signal sqlstate '45000' 
                set message_text = @message_text;
        end if;
        if not (new.photo is null OR new.photo regexp '^https?://') then
                set @message_text = concat('WRONG PHOTO: ', new.photo);
                signal sqlstate '45000' 
                set message_text = @message_text;
        end if;
end
//

it not understand CONCAT() in SIGNAL SQLSTATE SET message_text

| improve this answer | | | | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.