How to run a stored procedure in sqlyog

Stored procedures and triggers in MySQL

Some of the tools that the MySQL database engine provides are the stored procedures , Functions and triggers used to perform transactions or operations such as inserting or modifying records.

The stored procedures are small programs developed in SQL code. A stored procedure is a series of SQL commands that are stored with the database.

The advantage of a stored procedure is that we can create it in any text editor and that it is even executed on the server by the database engine and is not accessible to the users, but only to the administrator.

A stored procedure sends its results to an application to be displayed on the screen to avoid overloading the server.

  • MYSQL stored procedures - creation, queries, and data insertion

I explained how to make them. Functions and triggers or triggers are added here. We will see an example in a property database that we will call rent and then create the tables.

- Table structure for the table "Real estate" CREATE TABLE IF THERE IS NO "Real estate" (`id` int (11) NOT NULL,` userid` int (11) DEFAULT NULL, `idtipoimueble` int (6) DEFAULT '0' , `price` decimal (10,2) DEFAULT '0.00',` comision` decimal (10,0) NOT NULL, `description` text,` dateAlta` date DEFAULT '0000-00-00', `idprovince` int ( 10) DEFAULT NULL, `idlocality` int (10) DEFAULT NULL,` address` varchar (150) DEFAULT NULL, `pisoydepto` varchar (100) STANDARD NULL,` between_calles` text, `idoperacion` int (100) DEFAULT NULL, "marked" character (3) STANDARD "no", `image1` varchar (255) DEFAULT NULL,` image2` varchar (255) DEFAULT NULL, `image3` varchar (255) DEFAULT NULL,` image4` varchar (255) DEFAULT NULL, `antiquity` varchar (100) STANDARD NULL,` mt2covered` int (11) DEFAULT NULL, `surface_lote` int (11) DEFAULT NULL,` enabled` enum ('yes',' no ') NOT NULL DEFAULT' yes ') ENGINE = MyISAM AUTO_INCREMENT = 196 DEFAULT CHARSET = latin1; - Indices of the table "Real estate" OLD TABLE "Real estate" ADD PRIMARY KEY (`id`);

We will now develop a stored procedure for each transaction to get, insert, modify, and delete a record.

We can use phpmyadmin or a manager like Heidisql which is free and works with Wine on Windows or Linux.

We create a stored procedure to consult the property table:

LIMITER // CREATE PROCEDURE pa_listainmuebles () START SELECT * FROM real estate; END // DELIMITER;

MYSQL understands that a sentence ends with a semicolon. The DELIMITER statement changes that Ending characters into any other character. Conventionally, we use // to indicate the end of the stored procedure so that MySQL does not terminate the stored procedure when the first semicolon is found.

On the Routines tab can we every transaction we create Show and from there change, execute, export or delete the code.

We use the Command CALL on the SQL tab or from a programming language such as .NET or Java. Next, let's call the stored procedure that was created with the command.

CALL pa_listainmuebles ();

Next we create a stored procedure to insert a property. To do this, we need the parameter type IN, that is, the stored procedure assigns data and input variables to perform a transaction. In this case we save them in the database.

LIMITER // CREATE PROCEDURE pa_nuevoinmueble (IN id INT, IN USER ID INT, IN DECIMAL PRICE, IN COMMISSION DECIMAL) BEGIN INSERT INTO property (ID, user ID, price, commission) VALUES (ID, user, price, commission ) END // DELIMITER;

Then we can run the stored procedure that calls and assigns the parameters.

CALL `pa_nuevoinfurniture` ('12 ',' 15 ',' 10.00 ',' 0.05 ')

We can also enter data by running phpmyadmin's routine.

Next we create the stored procedure to edit a property in the phpmyadmin editor. In this case we are only changing the price.

We can create roles from the Definitor field in which we can assign a user defined in the MySQL server, in this case the root user of the host LocalHost, so that he can access the stored procedure.
If we want to do it from SQL code we need to run the following commands:

CREATE DEFINER = `root` @` localhost` PROCEDURE `pa_editarinmueble` (IN` precionuevo` DECIMAL (10,2), IN` idinmueble` INT (11)) START UPDATE real estate SET price = pre-order WHERE id = idinmueble; END

You run it and you are ready.

Using Triggers or Triggers in MySQL

A trigger in MySQL is a series of SQL statements that depend on a stored procedure and are automatically executed when a certain event occurs in our database. These events are triggered by transactions or statements such as INSERT, UPDATE and DELETE.

For example, when a change is saved in a record, we automatically create a backup or record an audit file to see what data was changed, when and by whom. They can be used for any manipulation that affects the data to aid or generate new information.

We are going to create the following property audit table:

CREATE TABLE `audit` (` user` VARCHAR (200) NULL DEFAULT NULL, `description` TEXT NULL,` date` DATETIME NULL DEFAULT NULL) COLLATE = 'latin1_swedish_ci' ENGINE = InnoDB

We create a trigger that monitors a message when someone changes the price of a property.

CREATE DEFINER = `root` @` localhost` TRIGGER `inmuebles_after_update` AFTER UPDATE OF` real estate` FOR EACH ROW INSERT INTO-Audit (user, description, date) VALUES (user (), CONCAT ('The price of the property was changed ', NEW.id,' (', OLD.price,') from (', NEW.price,') ', NOW ())

This trigger is executed automatically after a price update. We can add additional fields on request. With OLD we enter the field with the value before the change and with NEW the newly entered value. With NOW () we give the date and the current time

We have created a trigger that contains After Update for real estate. In this case, after an update in the property table, we add the user who made the change, the new price and the previous price.

I am updating a property:

CALL `pa_editarinmueble` ('80000', '170')

Then we go to the audit table and we can see the change:

We can also see the results in a print preview report from Phpmyadmin. We can see how the data identifying the property has been saved, what changes have been made and by which user they have been made. We can also call up the date and time of the change.

Next we see another possible application, if a property is rented, then it automatically changes from status to inactive status or we set it as unavailable.

To do this we need to have a simple table to store the property that is being rented, for a practical example we are not going to take much accuracy in the data.

CREATE TABLE `rents` (` id` INT (10) NOT NULL, `idinmueble` INT (10) NOT NULL,` idinquilino` INT (11) NOT NULL, PRIMARY KEY (`id`)) COLLATE = 'latin1_swedish_ci' ENGINE = InnoDB;

Next, we'll create the stored procedure to add a new record to the rental rate table.

CREATE DEFINER = `root` @` localhost` PROCEDURE `pa_newcall` (IN` idinmueble` INT, IN` idinquilino` INT) LANGUAGE SQL DOES NOT CONTAIN DETERMINISTICALLY SQL SQL SECURITY DEFINER COMMENT '' INSERT IN `rents` (` idinmueble`, ` idinquilino`) VALUES (idinmueble, idinquilino)

And then the trigger for changing properties is activated:

CREATE DEFINER = `root` @` localhost` TRIGGER `rentals_after_insert` AFTER INSERT ON` rentals` FOR EVERY ROW UPDATE SET enabled = 'no' where id = NEW.idinfurniture

Then we call the stored procedure in which we assign the ID of the property and the ID of the client or tenant I rent.

CALL pa new car (170.11)

Next we go to the real estate table and we have to see that the activated field changes state, IF it is active it will NOT become active.

We saw the benefit of using the Triggered-with stored procedure in MySQL to:

  • Monitors and records events or activities to change data in a table.
  • Change the status of a field by enabling or denying permissions and actions on a table
  • In addition, data consistency can be maintained by taking action on events that affect one or more tables.

In another tutorial, we will continue programming conditional structures and repeating structures in stored procedures.