0
CREATE TRIGGER trg_alis AFTER INSERT ON alis_acik_emirler
FOR EACH ROW
BEGIN

    DROP TABLE alis2;

    CREATE TABLE alis2 (
        emirID int NOT NULL AUTO_INCREMENT,
        userID int,
        fiyat int,
        adet int,
        doldurulan_adet int,
        kalan_adet int,
        market_tipi int,
        PRIMARY KEY(emirID)
    );

END;

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 ''DROP TABLE alis2'' at line 6

I can't drop table inside trigger. How can I do it?

DROP TABLE IF EXISTS alis2;

I tried this but it doesn't work.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Canydor
  • 1
  • 3
  • 1
    How can you drop a table in a trigger? That is not the question. *Why* would you want to do such a thing? I cannot readily think of a scenario where it would make sense. – Gordon Linoff Apr 29 '18 at 11:12
  • Very Very bad design... If you use the multi insert `INSERT ... VALUES(1),(1),(1)` the `FOR EACH ROW` within the trigger will cause the trigger code to be executed three times meaning the table wil be dropped three times and created three times.. – Raymond Nijland Apr 29 '18 at 11:29

1 Answers1

0

I cannot figure out a good reason why you would want to drop a table in a trigger. But you are dropping and recreating the table. Perhaps you just want to truncate the table/remove all the rows:

delete a from alis2 a;

This will remove all the rows in the table.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thaks for answer. I tried it but it gives an 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 ' ' at line 6 How can I fix it? – Canydor Apr 29 '18 at 13:58
  • `TRUNCATE TABLE` causes an [implicit commit](https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html), so that's out, too. OP is trying to do something that fundamentally does not belong in a trigger. – Michael - sqlbot Apr 29 '18 at 18:09
  • @Michael-sqlbot . . . Do you think `delete from alis2` would work -- if that is what the OP intends? – Gordon Linoff Apr 30 '18 at 01:29
  • Yes, that should be fine, if that's what's really needed. – Michael - sqlbot Apr 30 '18 at 02:12