CODEWITHSUNDEEP
mysqltriggers
count_campeador
count_campeador

Reputation: 163

mysql error, right syntax to use near ''

I have a problem with this SQL code. I want to create a trigger, here it is.

CREATE TRIGGER `blog_after_insert` AFTER UPDATE ON `agent`

FOR EACH ROW 
BEGIN     
DECLARE costvalue INTEGER default 0;

IF (OLD.PacketStatusType_Id!=NEW.PacketStatusType_Id) THEN

        IF (OLD.Packet_Cost IS NULL) THEN
            SET costvalue = OLD.Packet_Cost;
        ELSE
            SET costvalue = OLD.Packet_SMSCount;
        END IF;

           IF (costvalue IS NOT NULL) THEN
               CALL tcksms_packet_update_proc(OLD.Transaction_Id,OLD.PacketStatusType_Id,costvalue,-1);
           ELSE
               CALL tcksms_packet_update_proc(OLD.Transaction_Id,OLD.PacketStatusType_Id,0,-1);
           END IF;


        DECLARE COST_VALUE_NEW INT DEFAULT 0;

        IF (NEW.Packet_Cost IS NULL) THEN
            SET COST_VALUE_NEW = NEW.Packet_Cost;
        ELSE
            SET COST_VALUE_NEW = NEW.Packet_SMSCount;
        END IF;

           IF (COST_VALUE_NEW IS NOT NULL) THEN
               CALL tcksms_packet_update_proc(NEW.Transaction_Id,NEW.PacketStatusType_Id,COST_VALUE_NEW,1);
           ELSE
               CALL tcksms_packet_update_proc(NEW.Transaction_Id,NEW.PacketStatusType_Id,0,1);
           END IF;

        ELSE



        END IF;

   END IF;
END

But i get this;

Error

SQL query:

CREATE TRIGGER `blog_after_insert` AFTER UPDATE ON `agent` FOR EACH ROW BEGIN DECLARE costvalue INTEGER default 0;

MySQL said: Documentation > #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 5

That line is I tried deleting spaces, made combinations of apostrophes (' and `), deleted and rewrited some lines manually but can't make it work, what is wrong with this code?

Upvotes: 1

Views: 2620

Answers (1)

Shadow
Shadow

Reputation: 34285

You need to use the delimiter command before and after the create trigger command, otherwise the semicolons terminating the commands within the trigger body will confuse mysql. See mysql's documentation on defining stored programs, which also has examples how to use the delimiter command.

Upvotes: 2

Related Questions