CODEWITHSUNDEEP
mysqlsql
Tivie
Tivie

Reputation: 18923

Using local variables in a trigger

In this trigger:

  CREATE TRIGGER `after_insert_normas_revision`
    AFTER INSERT ON `normas_revision` FOR EACH ROW
    BEGIN

      DECLARE VAR_P_RHID INT;
      DECLARE VAR_P_VERSION INT;

      SELECT MAX(a.`rhID`)
      INTO VAR_P_RHID
        FROM `revision_history` a
        WHERE a.nID = new.`nID`;


      IF VAR_P_RHID IS NULL THEN
        SET VAR_P_VERSION = 0;
      ELSE
        SELECT `version`
          INTO VAR_P_VERSION
          FROM `revision_history` a
          WHERE a.`rhID` = VAR_P_RHID;
      END IF;

      DECLARE VAR_NEW_VERSION INT;
      SET VAR_NEW_VERSION = VAR_P_VERSION + 1;


      INSERT INTO `revision_history`
        (`revID`, `nID`, `userID`, `timestamp`, 
         `prev_rhID`, `version`, `action`, `comment`)
      VALUES
        (new.`revID`, new.`nID`, new.`userID`, CURRENT_TIMESTAMP, VAR_P_RHID, VAR_NEW_VERSION, 'UPDATE', new.`_rev_comment`);

    END

I'm getting this error

Schema Creation Failed: 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 'DECLARE VAR_NEW_VERSION INT; SET VAR_NEW_VERSION = VAR_P_VERSION + 1; ' at line 23:

If I remove 

DECLARE VAR_NEW_VERSION INT; 
SET VAR_NEW_VERSION = VAR_P_VERSION + 1

or change local variables to session variables (prepending with @) then the code runs allright.

Q. Why does this happen?

Upvotes: 0

Views: 892

Answers (2)

Devart
Devart

Reputation: 121922

The problem is in the syntax. It is incorrect.

All declarations must be at the begining of BEGIND...END clause. User variables you can set wherever you want in your code.

Upvotes: 2

Marc Alff
Marc Alff

Reputation: 8395

All the DECLARE should be at the start of the trigger.

This is not a C++ like language where you can mix declarations and statements, but more like C, where all declarations must be done before all statements.

Upvotes: 2

Related Questions