0

I'm trying to execute this query in a database through phpmyadmin

create trigger avoid_duplicated_sharing
before insert on sharingevents
for each row
begin
  if ( select count(*) from sharingevents where shared_note_id = NEW.shared_note_id AND shared_to = NEW.shared_to  > 0 ) then
      delete from sharingevents where shared_note_id = NEW.shared_note AND shared_to = NEW.shared_to
  END IF;
END

But phpmyadmin gives me the following error:

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

Two questions:

  • What's wrong with my script?
  • After a BEFORE INSERT trigger, Will INSERT operation be performed? In case it doesn't I will have to remove INSERT INTO SharingEvents (SELECT * FROM NEW);
Maximetinu
  • 149
  • 3
  • 12
  • You're not turning the insert it into an update, you're turning it into the same insert. – Barmar Sep 13 '17 at 22:19
  • You're right, I've changed the query to: `delete from sharingevents where (shared_note = NEW.shared_note_id AND shared_to = NEW.shared_to);` – Maximetinu Sep 14 '17 at 08:25
  • What's wrong with `INSERT INTO ... ON DUPLICATE KEY UPDATE` or `INSERT IGNORE` queries? Do they not fit your use case? – Mjh Sep 14 '17 at 08:40
  • @Mjh as I said I cannot modify the application, so I can't make it execute a different query than INSERT INTO – Maximetinu Sep 14 '17 at 08:43
  • This whole approach is destined to fail, because triggers aren't allowed to modify the same table they're triggered on. – Barmar Sep 14 '17 at 15:10

3 Answers3

0

Use exists:

if (exists (select 1 from sharingevents where shared_note_id = new.shared_note_id AND shared_to = new.shared_to) > 0)  then
    insert into sharingevents (shared_note_id,shared_to,permission_level) 
        values (NEW.shared_note_id,NEW.shared_to,NEW.permission_level);
end if;

Or, better yet, add a unique index on sharingevents(shared_note-id, shared_to) and then use:

    insert into sharingevents (shared_note_id, shared_to, permission_level) 
        values (NEW.shared_note_id, NEW.shared_to, NEW.permission_level)
        on duplicate key update shared_note_id = values(shared_note_id);

This will ignore any updates where the pairs already exist in the table. No if required.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Same error :/ `#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'then insert into sharingevents (shared_note_id,shared_to,permission_level) ' at line 5` – Maximetinu Sep 13 '17 at 21:08
  • Parentheses are unbalanced, and you don't need to use `> 0` to test the result of `exists()`. – Barmar Sep 13 '17 at 22:15
  • Your second recommendation is good, but presumably would require changing application code that he doesn't want to touch, so he's automating it with the trigger. – Barmar Sep 13 '17 at 22:20
  • @Barmar . . . However, the first has race conditions. The table should have the unique index/constraint regardless. – Gordon Linoff Sep 14 '17 at 12:15
0

count(shared_note_id, shared_to) is invalid syntax. You can only put multiple column names inside COUNT() when you use count(DISTINCT ...). In your case, you don't need to put column names at all, just use COUNT(*) to count the number of rows matching the condition.

See count(*) and count(column_name), what's the diff? for more information about when you should put column names in COUNT()

Unfortunately, fixing the syntax errors won't really solve your problem, because you can't use a trigger to make a change to the same table. From the FAQ:

Can triggers access tables?
A trigger can access both old and new data in its own table. A trigger can also affect other tables, but it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

You'll need to recode the callers to use INSERT ... ON DUPLICATE KEY UPDATE, or something equivalent, to accomplish this.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • I've done that change and yes, it was a mistake! Thanks. But even then it doesn't work. Now it gives me an error at the END IF – Maximetinu Sep 14 '17 at 08:35
0

I solve it with the following code:

delimiter $$

create trigger avoid_duplicated_sharing
before insert on sharingevents
for each row
begin
    if ( select count(*) from sharingevents where shared_note_id = NEW.shared_note_id AND shared_to = NEW.shared_to  > 0 ) then
        delete from sharingevents where shared_note_id = NEW.shared_note_id AND shared_to = NEW.shared_to;
    end if;
END$$

The problem was the delimiter.

Even so, my trigger doesn't work. When the application inserts duplicated primary keys MySQL throws the following error:

#1442 - Can't update table 'sharingevents' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Maximetinu
  • 149
  • 3
  • 12