MENU


Compound Statements

A compound statement, such as BEGIN...END, is a block of SQL statements treated as a single unit. Compound statements are the building blocks for stored PROCEDUREs, stored FUNCTIONs, EVENTs, and TRIGGERs.

PROCEDURE, FUNCTION

CREATE [DEFINER = user]
  PROCEDURE [IF NOT EXISTS] sp_name ([proc_parameter[,...]])
  [characteristic ...] routine_body

CREATE [DEFINER = user]
  FUNCTION [IF NOT EXISTS] sp_name ([func_parameter[,...]])
  RETURNS type
  [characteristic ...] routine_body

proc_parameter: [IN | OUT | INOUT] param_name type
func_parameter: param_name type
type: any valid MySQL data type

characteristic:
  COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}
  | SQL SECURITY {DEFINER | INVOKER}

ALTER {PROCEDURE|FUNCTION} routine [characteristic ...]
DROP {PROCEDURE|FUNCTION} [IF EXISTS] routine

routine_body is any valid SQL routine statement, typically a BEGIN...END compound statement. A procedure is invoked with the CALL keyword.


An IN parameter passes a value into a procedure; any modification to it inside the procedure is not visible outside. An OUT parameter passes a value from the procedure out to the caller's variable. An INOUT parameter passes a value in, and any modification to it is visible outside the procedure once the call returns. Function parameters are always regarded as IN parameters, and a stored function cannot return a result set.

LANGUAGE SQL is currently ignored, since only SQL routines are supported. A routine is DETERMINISTIC if it always produces the same result for the same input parameters – the optimizer can execute such routines faster. The default is NOT DETERMINISTIC; a routine containing NOW() or RAND() is nondeterministic. CONTAINS SQL, the default, indicates the routine contains no statements that read or write data. NO SQL indicates the routine contains no SQL statements at all. READS SQL DATA indicates the routine contains statements that read data (SELECT) but do not write it. MODIFIES SQL DATA indicates the routine contains statements that write data (INSERT, DELETE, UPDATE). SQL SECURITY specifies whether the routine executes with the privileges of the account named in the DEFINER clause, or with the privileges of the invoking user. As of MySQL 8.4, the SET_ANY_DEFINER and ALLOW_NONEXISTENT_DEFINER dynamic privileges (introduced in 8.2, and now the sole mechanism for this, since the broader SET_USER_ID privilege they replaced was removed in 8.4) give finer-grained control over which accounts may set an arbitrary or nonexistent account as the DEFINER of a stored procedure, function, event, or trigger, instead of requiring the SUPER privilege. See Privileges.

The following are not permitted inside stored routines: LOCK TABLES/UNLOCK TABLES, ALTER VIEW, LOAD DATA/LOAD TABLE, and any statement not permitted in SQL prepared statements (except SIGNAL, RESIGNAL, and GET DIAGNOSTICS). To begin a transaction inside a stored program, use START TRANSACTION. Stored functions additionally disallow commit/rollback statements, statements that return a result set, FLUSH statements, recursive function calls, and statements that modify a table already being used by the statement that invoked the function.

Stored Procedure vs. Function
ReturnsZero or more values, via OUT/INOUT parameters or a result setA single scalar value
Can use transactions?YesNo
Can output to parameters?Yes (OUT, INOUT)No – parameters are always IN
Can call the other kind?Can call a functionCannot call a procedure
Usable in SELECT, WHERE, HAVING?No – invoked with CALLYes
Supports exception handling (DECLARE...HANDLER)?YesYes

The worked example below invokes a procedure that fills a table with 0-8 random integers in a given range, and a function that returns the average of the integers stored in that table. Notice the use of DELIMITER to temporarily change the statement delimiter so that semicolons inside the routine body do not terminate the CREATE statement early.

JavaScript Stored Programs

MySQL 9.0 adds a second stored-routine language alongside SQL: CREATE FUNCTION/PROCEDURE ... LANGUAGE JAVASCRIPT writes a routine body in JavaScript instead of SQL, executed inside the server by the MySQL Multilingual Engine (MLE) component. This is a MySQL Enterprise Edition feature only – it is not available in MySQL Community Edition. See the worked example below for the basic shape of a JavaScript function.

MySQL 9.2 extends JavaScript stored programs further, still Enterprise-only: JavaScript code can call SQL user-defined functions, stored procedures, and variables; a JS transaction API (START TRANSACTION, COMMIT, ROLLBACK, and SET AUTOCOMMIT, callable from JavaScript) lets a routine manage transactions directly; and CREATE LIBRARY, DROP LIBRARY, and SHOW CREATE LIBRARY let a block of reusable JavaScript be defined once as a library module and imported into multiple JS routines, instead of duplicating the same code in each one.

DECLARE, SET, RESET

DECLARE must appear inside, and at the beginning of, a BEGIN...END block. Variable and condition declarations must appear before cursor or handler declarations.

DECLARE var_name [, var_name] ... type [DEFAULT value]

Declares one or more local variables, optionally with a default value.



DROP PROCEDURE IF EXISTS proc;
DELIMITER //
CREATE PROCEDURE proc()
BEGIN
   DECLARE a,b,c INT DEFAULT 2;
   SELECT a,b,c;
END //
DELIMITER ;
CALL proc();

abc
222
SET variable = expr [, variable = expr] ...

variable:
  @user_var_name
  | param_name
  | local_var_name
  | [SESSION | @@SESSION. | @@] system_var_name
  | {GLOBAL | @@GLOBAL.} system_var_name
  | {PERSIST | @@PERSIST.} system_var_name
  | {PERSIST_ONLY | @@PERSIST_ONLY.} system_var_name

Assigns a value to a user-defined or system variable.


A user-defined global variable begins with the symbol @. A function/procedure parameter or local variable is never prefixed with @. Any change to a SESSION system variable lasts until the session closes; a change to a GLOBAL variable lasts until the server restarts. SET PERSIST sets a global system variable's value and also persists it across server restarts (not every global system variable can be persisted). Some system variables exist as both SESSION and GLOBAL. To view current values, use SHOW [GLOBAL|SESSION] VARIABLES [LIKE 'pattern' | WHERE expr].

The key difference between the two: SET assigns a value to a user-defined or system variable within any SQL statement and is accessible from any statement within the session; DECLARE introduces a local variable inside a stored procedure, function, event, or trigger, accessible only within the body where it is declared.


CREATE PROCEDURE p(increment INT)
BEGIN
    DECLARE counter INT DEFAULT 0;
    WHILE counter < 10 DO
      -- ... do work ...
      SET counter = counter + increment;
    END WHILE;
END;
SET GLOBAL max_connections = 1000;
SET @@GLOBAL.max_connections = 1000;
SET SESSION sql_mode = 'TRADITIONAL';
SET LOCAL sql_mode = 'TRADITIONAL';
SET @@SESSION.sql_mode = 'TRADITIONAL';
SET @@LOCAL.sql_mode = 'TRADITIONAL';
SET @@sql_mode = 'TRADITIONAL';
SET sql_mode = 'TRADITIONAL';
SET PERSIST max_connections = 1000;
SET @@PERSIST.max_connections = 1000;
SET @@SESSION.max_join_size = DEFAULT;
SET @@SESSION.max_join_size = @@GLOBAL.max_join_size;
SET @x = 1, SESSION sql_mode = '';
SET GLOBAL sort_buffer_size = 1000000, SESSION sort_buffer_size = 1000000;
SET @@GLOBAL.sort_buffer_size = 1000000, @@LOCAL.sort_buffer_size = 1000000;
SET GLOBAL max_connections = 1000, sort_buffer_size = 1000000;
SET @@GLOBAL.sort_buffer_size = 50000, sort_buffer_size = 1000000;

Equivalent and combined forms of SET for user variables and system variables at various scopes.



SET @a = 900 + 99;

SELECT @a,
     @@system_time_zone,
     @@global.autocommit;
RESET PERSIST [[IF EXISTS] system_var_name]

Removes persisted global system variable settings from the mysqld-auto.cnf option file in the data directory. A removed variable is no longer initialized from mysqld-auto.cnf at server startup. With no variable named, all persisted settings are removed.



-- a quick way to inspect a numbered listing of rows in MySQL Workbench,
-- incrementing a user-defined variable per row
SET @a=0;
SELECT @a:=@a+1 FROM INFORMATION_SCHEMA.TABLES;

LOOP, REPEAT, WHILE, CASE, IF

Labels allow a set of statements to be executed repeatedly.

[begin_label:] BEGIN [statement_list] END [end_label]
[begin_label:] LOOP statement_list END LOOP [end_label]
[begin_label:] REPEAT statement_list UNTIL search_condition END REPEAT [end_label]
[begin_label:] WHILE search_condition DO statement_list END WHILE [end_label]

begin_label can be given without end_label. If end_label is present it must match begin_label; end_label cannot be given without begin_label.


Inside a labeled construct, ITERATE jumps execution back to the beginning of the construct, and LEAVE exits the block of statements. The worked example below sums increments to 100 using a LOOP with ITERATE and LEAVE. RETURN terminates a stored function and returns its value.

Unlike a CASE expression, a CASE statement cannot have an ELSE NULL clause, and it is terminated with END CASE instead of END. IF...THEN...ELSE...END IF provides ordinary conditional branching. See the worked example for both constructs.

CURSOR

A cursor is a result set scoped to a stored routine. It cannot be updated, and it can only be traversed forward, one row at a time, without skipping rows. Cursor declarations must appear after variable and condition declarations, and before handler declarations.

DECLARE cursor_name CURSOR FOR select_statement;

Declares a cursor over the result set of select_statement. Use OPEN to start reading, FETCH ... INTO var_list to read the next row, and CLOSE to release it. A CONTINUE HANDLER FOR NOT FOUND is the idiomatic way to detect that a cursor has been exhausted, since a cursor does not raise an exception at end-of-data by itself.


CONDITION, HANDLER

A HANDLER specifies what to do when a CONDITION occurs.

DECLARE condition_name CONDITION FOR condition_value

condition_value:
  mysql_error_code
  | SQLSTATE [VALUE] sqlstate_value

Gives a name to a MySQL error code or SQLSTATE value so it can be referred to later in a handler declaration.


DECLARE handler_action HANDLER
  FOR condition_value [, condition_value] ...
  statement

handler_action: CONTINUE | EXIT | UNDO

condition_value:
  mysql_error_code
  | SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
CONTINUE lets execution continue after the handler runs. EXIT terminates the enclosing BEGIN...END compound statement. UNDO is not supported. NOT FOUND controls what happens when a cursor reaches the end of its data set. Use SHOW WARNINGS or SHOW ERRORS to inspect conditions or errors directly.

SQL statements produce diagnostic information; GET DIAGNOSTICS retrieves either statement or condition information.

GET [CURRENT | STACKED] DIAGNOSTICS
{
  statement_information_item [, statement_information_item] ...
  | CONDITION condition_number
    condition_information_item [, condition_information_item] ...
}

statement_information_item_name: NUMBER | ROW_COUNT

condition_information_item_name:
  CLASS_ORIGIN | SUBCLASS_ORIGIN | RETURNED_SQLSTATE | MESSAGE_TEXT | MYSQL_ERRNO
  | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME
  | CATALOG_NAME | SCHEMA_NAME | TABLE_NAME | COLUMN_NAME | CURSOR_NAME
CURRENT, the default, retrieves information from the current diagnostic area. STACKED retrieves information from the second diagnostic area, available only while inside a condition handler.

RESIGNAL passes on the error condition information available while a condition handler is executing, so the handler can both react to the error and still surface it – without RESIGNAL, executing another SQL statement inside the handler destroys the information that triggered it. RESIGNAL may also modify the information before passing it on. A bare RESIGNAL means "pass on the error unchanged"; RESIGNAL with a condition value means "push a (possibly modified) condition into the current diagnostics area".

RESIGNAL [condition_value] [SET signal_information_item [, signal_information_item] ...]
SIGNAL condition_value [SET signal_information_item [, signal_information_item] ...]

condition_value: SQLSTATE [VALUE] sqlstate_value | condition_name
signal_information_item: condition_information_item_name = simple_value_specification

SIGNAL raises an error or warning, handing information to a handler, an outer part of the application, or the client. condition_information_item_name accepts the same names as GET DIAGNOSTICS above (except NUMBER/ROW_COUNT).


EVENT

The event scheduler thread must be turned on for scheduled events to run; setting @@global.event_scheduler to DISABLED (rather than OFF) prevents the state from being changed again at runtime.

CREATE [DEFINER = {user | CURRENT_USER}]
  EVENT [IF NOT EXISTS] event_name
  ON SCHEDULE schedule
  [ON COMPLETION [NOT] PRESERVE]
  [ENABLE | DISABLE | DISABLE ON SLAVE]
  [COMMENT 'comment']
  DO event_body;

schedule:
  AT timestamp [+ INTERVAL interval] ...
  | EVERY interval [STARTS timestamp [+ INTERVAL interval] ...] [ENDS timestamp [+ INTERVAL interval] ...]

interval: quantity {YEAR|QUARTER|MONTH|DAY|HOUR|MINUTE|WEEK|SECOND|YEAR_MONTH|
  DAY_HOUR|DAY_MINUTE|DAY_SECOND|HOUR_MINUTE|HOUR_SECOND|MINUTE_SECOND}

ALTER [DEFINER = {user | CURRENT_USER}] EVENT event_name
  [ON SCHEDULE schedule] [ON COMPLETION [NOT] PRESERVE]
  [RENAME TO new_event_name] [ENABLE | DISABLE | DISABLE ON SLAVE]
  [COMMENT 'comment'] [DO event_body]

DROP EVENT [IF EXISTS] event_name
DEFINER records the user who defines the event. ON COMPLETION PRESERVE keeps the event definition after it expires instead of dropping it (ON COMPLETION NOT PRESERVE is the default). DISABLE stops the event from running (ENABLE is the default); DISABLE ON SLAVE stops it from running on a replica.

TRIGGER

CREATE [DEFINER = user]
  TRIGGER [IF NOT EXISTS] trigger_name
  trigger_time trigger_event
  ON tbl_name FOR EACH ROW
  [trigger_order]
  trigger_body

trigger_time: {BEFORE | AFTER}
trigger_event: {INSERT | UPDATE | DELETE}
trigger_order: {FOLLOWS | PRECEDES} other_trigger_name

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name
INSERT activates the trigger via INSERT, LOAD DATA, and REPLACE statements. UPDATE activates it via UPDATE statements. DELETE activates it via DELETE and REPLACE statements. Cascaded foreign key actions (see Indexes) do not activate triggers. A table cannot have two triggers with the same trigger name and the same action time (e.g. two BEFORE INSERT triggers).

Column values are accessed inside a trigger body with OLD.colName or NEW.colName. Multiple triggers can share the same trigger event and action time; by default they activate in creation order. To control the order explicitly, add FOLLOWS or PRECEDES other_trigger_name after FOR EACH ROW – FOLLOWS activates the new trigger after the named one, PRECEDES activates it before. See DECLARE, SET, RESET above for the use of local and user-defined variables inside triggers. 

DROP PROCEDURE IF EXISTS initialize_tbl;
DROP FUNCTION IF EXISTS tAverage;

DELIMITER //
CREATE PROCEDURE initialize_tbl
    (IN pmin INT, IN pmax INT, OUT cnt INT)
    NOT DETERMINISTIC
    MODIFIES SQL DATA
BEGIN
    DROP TABLE IF EXISTS tbl;
    CREATE TABLE tbl(num INT, INDEX (num));
    INSERT INTO tbl VALUES
        (rand()*100),(rand()*100),(rand()*100),
        (rand()*100),(rand()*100),(rand()*100),
        (rand()*100),(rand()*100);
    DELETE FROM tbl WHERE num<pmin OR num>pmax;
    SET cnt = (SELECT count(num) FROM tbl);
END //

CREATE FUNCTION tAverage()
    RETURNS INT
    DETERMINISTIC
    READS SQL DATA
BEGIN
    RETURN (SELECT AVG(num) FROM tbl);
END //
DELIMITER ;

CALL initialize_tbl(30,70,@C);

SELECT @C, tAverage(), num FROM tbl;

Sample output (rand()-based, so values vary per run):
@CtAverage()num
34730
34753
34759

-- MySQL 9.0+, requires the Multilingual Engine (MLE) component and
-- MySQL Enterprise Edition -- not available on Community Edition servers.

CREATE FUNCTION gcd(a INT, b INT)
    RETURNS INT
    NO SQL
    LANGUAGE JAVASCRIPT
AS $$
    let x = Math.abs(a);
    let y = Math.abs(b);
    while (y) {
        const t = y;
        y = x % y;
        x = t;
    }
    return x;
$$;

SELECT gcd(75, 220), gcd(75, 225);

-- 9.2+: CREATE LIBRARY packages reusable JavaScript for import into
-- multiple JS routines via a USING clause (also Enterprise-only,
-- illustrative only):
-- CREATE LIBRARY mathutils LANGUAGE JAVASCRIPT AS $$
--     export function square(n) { return n * n; }
-- $$;
-- CREATE FUNCTION squared(n INT) RETURNS INT
--     LANGUAGE JAVASCRIPT
--     USING (mathutils AS mathutils)
--     AS $$
--     return mathutils.square(n);
-- $$;

-- LOOP with ITERATE / LEAVE
DROP PROCEDURE IF EXISTS sum100;
DELIMITER //
CREATE PROCEDURE sum100(INOUT p INT)
BEGIN
    lbl: LOOP
        SET p = p + 1;
        IF p < 100 THEN ITERATE lbl; END IF;
        LEAVE lbl;
    END LOOP lbl;
END //
DELIMITER ;
SET @s=0;
CALL sum100(@s);
SELECT @s;
-- 100

-- CASE statement (no ELSE NULL, terminated with END CASE)
DROP FUNCTION IF EXISTS func;
DELIMITER //
CREATE FUNCTION func()
    RETURNS INT
    DETERMINISTIC
BEGIN
    DECLARE v INT DEFAULT 1;
    CASE v
        WHEN 2 THEN RETURN v*v;
        WHEN 3 THEN RETURN v*v*v;
        ELSE
            BEGIN
                RETURN v;
            END;
    END CASE;
END; //
DELIMITER ;
SELECT func();
-- 1

-- IF...THEN...ELSE...END IF, printing prime numbers up to 1000
DELIMITER //
CREATE PROCEDURE PRIMEFUNC(OUT foutput VARCHAR(1200))
BEGIN
    DECLARE num INT;
    DECLARE counter INT;
    DECLARE isprime INT;
    SET num = 3;
    SET foutput = "2";
    outerlabel : LOOP
        SET counter = 2;
        SET isprime = 1;
        innerlabel : LOOP
            IF counter <= sqrt(num) THEN
                IF num mod counter = 0 THEN
                    SET isprime = 0;
                    LEAVE innerlabel;
                END IF;
                SET counter = counter + 1;
                ITERATE innerlabel;
            ELSE
                LEAVE innerlabel;
            END IF;
        END LOOP innerlabel;
        IF isprime = 1 THEN
            SET foutput = CONCAT(foutput,"&",num);
        END IF;
        SET num = num + 1;
        IF num <= 1000 THEN ITERATE outerlabel;
        ELSE LEAVE outerlabel;
        END IF;
    END LOOP outerlabel;
END //
DELIMITER ;

CALL PRIMEFUNC(@fop);
SELECT @fop;
-- '2&3&5&7&11&13&17&19&23&29&31&37&41&43&47&53&59&61&67&71...'

DROP TABLE IF EXISTS test_t1, test_t2, test_t3;
CREATE TABLE test_t1 (id INT, data INT);
CREATE TABLE test_t2 (i INT);
CREATE TABLE test_t3 (a CHAR(20), b INT);
INSERT INTO test_t1 VALUES (1,50),(2,80);
INSERT INTO test_t2 VALUES (60),(70);

DROP PROCEDURE IF EXISTS curDemo;
DELIMITER //
CREATE PROCEDURE curDemo()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE a CHAR(20) DEFAULT 'row';
    DECLARE x, y, c INT;
    DECLARE cur1 CURSOR FOR SELECT id,data FROM test_t1;
    DECLARE cur2 CURSOR FOR SELECT i FROM test_t2;
    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET done = TRUE;

    OPEN cur1;
    OPEN cur2;

    read_loop: LOOP
        FETCH cur1 INTO x, y;
        FETCH cur2 INTO c;
        IF done THEN
            LEAVE read_loop;
        END IF;
        IF x < y THEN
            INSERT INTO test_t3 VALUES (a,x);
        ELSE
            INSERT INTO test_t3 VALUES (a,y);
        END IF;
    END LOOP;

    CLOSE cur1;
    CLOSE cur2;
END //
DELIMITER ;

CALL curDemo();
SELECT * FROM test_t3;

ab
row1
row2

-- DECLARE ... CONDITION, GET DIAGNOSTICS
DROP PROCEDURE IF EXISTS handled_call;
DELIMITER //
CREATE PROCEDURE handled_call()
BEGIN
    DECLARE no_such_table CONDITION FOR 1051;
    DECLARE CONTINUE HANDLER FOR no_such_table
    BEGIN
        -- body of handler
        SELECT 'caught missing table' AS note;
    END;
    DROP TABLE IF EXISTS a_table_that_does_not_exist;
END //
DELIMITER ;
CALL handled_call();

GET CURRENT DIAGNOSTICS @a=NUMBER, @b=ROW_COUNT;
SELECT @a, @b;

-- RESIGNAL: react to an error and still surface it
DROP TABLE IF EXISTS xx;
DELIMITER //
CREATE PROCEDURE p ()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SET @error_count = @error_count + 1;
        IF @a = 0 THEN RESIGNAL SQLSTATE '45000' SET MYSQL_ERRNO=5; END IF;
    END;
    DROP TABLE xx;
END //
DELIMITER ;
SET @error_count = 0;
SET @a = 0;
SET @@max_error_count = 2;
CALL p();
SHOW ERRORS;

LevelCodeMessage
Error1051Unknown table 'xx'
Error5Unknown table 'xx'

-- SIGNAL: raise a condition explicitly
DROP PROCEDURE IF EXISTS raise_demo;
DELIMITER //
CREATE PROCEDURE raise_demo(pval INT)
BEGIN
    DECLARE specialty CONDITION FOR SQLSTATE '45000';
    IF pval = 0 THEN
        SIGNAL SQLSTATE '01000';
    ELSEIF pval = 1 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'An error occurred';
    ELSEIF pval = 2 THEN
        SIGNAL specialty
            SET MESSAGE_TEXT = 'An error occurred';
    ELSE
        SIGNAL SQLSTATE '01000'
            SET MESSAGE_TEXT = 'A warning occurred', MYSQL_ERRNO = 1000;
    END IF;
END //
DELIMITER ;

SET @@global.event_scheduler=ON;

-- runs once, one and a half hours from now
DROP TABLE IF EXISTS event_tbl;
CREATE TABLE event_tbl (a INT);
DROP EVENT IF EXISTS myEvent;
CREATE
    DEFINER = 'root'@'localhost'
    EVENT myEvent
    ON SCHEDULE AT CURRENT_TIMESTAMP
                   + INTERVAL 1 HOUR
                   + INTERVAL 30 MINUTE
    DO
        INSERT INTO event_tbl VALUES (1);

-- runs once every hour, within a bounded period
DROP EVENT IF EXISTS myHourlyEvent;
CREATE EVENT myHourlyEvent
    ON SCHEDULE EVERY 1 HOUR
                STARTS '2026-08-16 11:00:00'
                ENDS   '2026-08-16 19:00:00'
    DO
        INSERT INTO event_tbl VALUES (1);

SET @sum=0;
DROP TABLE IF EXISTS account;
DROP TRIGGER IF EXISTS ins_sum;
DROP TRIGGER IF EXISTS ins_transaction;
CREATE TABLE account (amount DECIMAL(10,2));

CREATE TRIGGER ins_sum
    BEFORE INSERT ON account
    FOR EACH ROW SET @sum = @sum + NEW.amount;

INSERT INTO account VALUES (300),(200);
SELECT @sum;

@sum
500.00

-- a second BEFORE INSERT trigger on the same table, ordered
-- to run before ins_sum via PRECEDES
SET @deposits=0, @withdrawals=0;
CREATE TRIGGER ins_transaction BEFORE INSERT ON account
    FOR EACH ROW PRECEDES ins_sum
    SET
        @deposits = @deposits + IF(NEW.amount>0,NEW.amount,0),
        @withdrawals = @withdrawals + IF(NEW.amount<0,-NEW.amount,0);

INSERT INTO account VALUES (150),(-50);
SELECT @sum, @deposits, @withdrawals;

@sum@deposits@withdrawals
600.00150.0050.00