SQL Triggers

SQL Trigger
       - A SQL trigger is an SQL  statements or sets of statements which is stored to be activated or fired when an event associating with a database table occurs.

Take note that though trigger is implemented in MySQL has all features in standard SQL but there are some restrictions to be aware of.

These restrictions are the followings:

      1. It is not allowed to create a trigger for views or temporary table.
      2. It is not allowed to use transaction in a trigger.
      3. Return statements is disallowed in a trigger.
      4. Creating a trigger for a database table causes the query cache invalidated.
      5. All trigger for a database table must have unique name

CREATING A TRIGGER:

SYNTAX:
(BEFORE|AFTER)_tableName_(INSERT|UPDATE|DELETE)

Setting up Replication in MYSQL

Replication
    - The use of replication is in order to configure MYSQL database updated on a remote server whenever the local client is updated. Moreover it allows you to create an exact copy of a database to another server. This server you my call would be the Slave which takes all the updates from the Master

STEPS IN SETTING UP MYSQL:

1. Let us first configure the master
   1.1 Insert this lines in your my.ini or my.cnf:
                   skip-networking
                   bind-address=127.0.0.1

             Note: If this two lines is already at your my.ini or my.cnf file and has a comment tag (#)                     just remove it. (Ex. #skip-networking) or you can also skip this step.
   1.2 Again insert this into my.ini or my.cnf
                   log-bin=/var/log/mysql/mysql-bin.log
                   binlog-do-db=exampledb
                   server-id=1
    1.3 Then RESTART mysql
    1.4 Create a user with replication privileges
           >mysql -u root -p
           > grant replication slave on *.* to 'slave _user'@'%'identified by 'slave_pass';
           >flush privileges;
           >use exampledb;
           >flush tables with read lock
           >show master status;
           >quit;
      1.5 Write down the file, position and binlog_do_db

              master-host=192.168.1.1
              master-user=example_user
              master_password=example_password
              server-id=3
             master-connection-retry=60
             replicate-do-db=exampledb
      1.6 Two possibilities to get existing tables and data from exampledb from master to slave
             1.6.1 make a database dump
   
                 mysql -u root -proot --opt exampledv> c:\foldername\exampledb.sql

                Note: change the path to your prepared save location
                     1.6.1.1 Transfer this file to your slave server!

             1.6.1 load data from master
      1.7 Unlock tables in exampledb
                 >unlock tables;
                 >quit;

2. Let us no configure the slave
      2.1 create a database named after the database created in master
            >mysql -u root -proot -p
           >create database exampledb;
           >quit;
        2.2 Copy the exampledb data fro the master to slave
              >mysql -u root exampledb <C:\foldername\exampledb.sql
        2.3 Insert this in your my.ini or my.cnf file
             server-id=2
             master-host=192.168.1.2
              master=user=slave_user
             master-password=slave_password
           master-connect-retry=60
           replicate-do-db=exampledb

      (server-id can be any number as long as it is not the same as the server id of the master)

        2.4 Restart MySQL
        2.5 If load data from master;
                 >load data from master;
                >quit;
         2.6 check if exampledb is available on slave exampledb
         2.7 Finally we must do this

               >mysq; -u root -proot
               >slave stop
         2.8 Replace the values
            CHANGE MASTER TO
           master_host='192.168.1.2',
           master_user='slave_user',
           master_password='slave_post',
           master_log_file='mysql-bin.006',
           master_log_pos=183;
         2.9 Start Slave;
         2.10 quit;


SAMPLE PRESENTATION:

MASTER:

-----Sample my.ini

Create a user:
CREATE USER example IDENTIFIED BY '12345';

Show master status;

SLAVE:
Sample my.ini of slave

load data from master;




                  

MYSQL STORED PROCEDURE

What is Stored Procedure
      It is a segment of declarative SQL code, it is stored in the database catalog. It can be done by a program, trigger or even another stored procedure

What are the advantages of stored procedure?

1. It increase performance of application
2. It reduces the traffic between application and database.
3. It is reusable and transparent to any application which will be using or use this
4. Last but not the least it is secured.



What are the disadvantages of stored procedure?

1. It makes the database server high load  in both memory and processors.
2. It only contains SQL declarative statements
3. Difficult to debug.
4. Not easy to write and maintain

HOW TO CREATE STORED PROCEDURE:

DELIMITER //

                        CREATE PROCEDURE ShEmployees()

                        BEGIN

                        SELECT * FROM employee;

                        END //

                        DELIMITER ;

 
CALLING THE STORED PROCEDURE:
SYNTAX:

CALL STORED_PROCEDURE_NAME();

Example:
CALL ShEmployees(); 

STORED PROCEDURE PARAMETERS:

     By the use of parameters it makes  the stored procedure more flexible and useful.

3 modes:

      IN - default mode, means it can be passed into stored procedure

      OUT - it indicates that stored  procedure can change this parameter and pass back to the calling program.

     INOUT - combination of IN and OUT mode; you can pass parameter into stored procedure and get it back with the new value from calling program

 SYNTAX:

•      MODE param_name param_type(param_size)