MySQL - It is possible to call functions to execute trigger operations?

Question

In my schema I have many tables with common fields that I need to fill. This are basicaly control fields like this ones:

id_db_user
date_creation
date_last_update

I have done two triggers to fill this fields:

-- Trigger DDL Statements
delimiter |
CREATE TRIGGER control_insert BEFORE INSERT ON EZT_SOCIETIES

  FOR EACH ROW BEGIN

    SET NEW.id_db_user    = current_user;

    SET NEW.date_creation = current_timestamp;

  END;
| delimiter ;


delimiter |
CREATE TRIGGER control_update BEFORE UPDATE ON EZT_SOCIETIES

  FOR EACH ROW BEGIN

    SET NEW.date_last_update = current_timestamp;

  END;
| delimiter ;

My question is: Can I write some function to call the operations inside the trigger? Something like this:

function fn_control_insert
    SET NEW.id_db_user    = current_user;
    SET NEW.date_creation = current_timestamp;

and then call this function in the trigger like:

delimiter |
CREATE TRIGGER control_insert BEFORE INSERT ON EZT_SOCIETIES

  FOR EACH ROW BEGIN

    call fn_control_insert;

  END;
| delimiter ;

This is possible to do in MySQL?

Best Regards,

Miguel Pérez · Accepted Answer · 2012-06-06 08:25:24Z

4

No, it's not possible, unfortunately, nor you can pass NEW and OLD as parameters, nor function parameters can be OUT or INOUT (i.e. writable), so the short answer is no.

You can call a function from a trigger (provided the function doesn't violate any of the MySQL trigger restrictions such as modifying the table the trigger is attached to), but you'll have to pass the function every individual NEW or OLD field you need to read, and the function will be unable to write to them — all you can do from a function is return a value.

answered Jun 6, 2012 at 8:25

Miguel Pérez

5043 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

André Over a year ago

Hi Miguel, thanks for the reply. I've been reading about this subject. It is an option to use a PROCEDURE and call that PROCEDURE in a AFTER INSERT/UPDATE TRIGGER passing the NEW values to the PROCEDURE? Thanks

Miguel Pérez Over a year ago

You can, but INOUT and OUT parameters won't work, and the procedure cannot be used to workaround the MySQL limitations on triggers such as recursion.

Devart · Accepted Answer · 2012-06-06 08:49:33Z

0

Declare date_creation field as TIMESTAMP with 'ON UPDATE CURRENT_TIMESTAMP' clause. The field will be updated automatically with CURRENT_TIMESTAMP value on INSERT or UPDATE statements. More information - Automatic Initialization and Updating for TIMESTAMP.
Set just NEW.id_db_user value in control_insert trigger - SET NEW.id_db_user = current_user;.
Remove control_update trigger.

answered Jun 6, 2012 at 8:49

Devart

120k23 gold badges172 silver badges189 bronze badges

Collectives™ on Stack Overflow

MySQL - It is possible to call functions to execute trigger operations?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related