My SQL Trigger Tutorial

My SQL Trigger Tutorial

TRIGGERS:-

                                     A Trigger is a named database object which defines some action that the database should take when some databases related event occurs. Triggers are executed when you issues a         data manipulation command like INSERT, DELETE, UPDATE on a table for which the trigger has been created. They are automatically executed and also transparent to the user. But for creating the trigger the user must have the CREATE TRIGGER privilege. In this section I will describe you example of create and drop the triggers.

So Stick with me...

1. CREATE TABLE IN MySQL:-

       First We create  a table on which We will create TRIGGER.

       We create a table called "one" as shown below.

Create Table "one"

2. CREATE TRIGGER IN MySQL:-

       Now  We will create  a BEFORE UPDATE TRIGGER on table "one" Which We earlier created.

       

Create  update TIGGER On Table "one"

3. TEST TRIGGER IN MySQL:-

       

Now  We will Test  a BEFORE UPDATE TRIGGER Which We earlier created on table "one".

Test BEFORE UPDATE TRIGGER  on TABLE one.

                   Here, you can see If Anyone try to update any name with "NIRAV" in table "one", the BEFORE UPDATE TRIGGER  will fired automatically by MySQL and it will changed name "NIRAV" to "ABCD" as per defined in TRIGGER updTriggerOne.

I hope it will be helpful to you in Understanding TRIGGER in MySQL.

Thank  You.!

Please Leave Comments If this found helpful to you.

Thanks.....& Enjoy...

hostgator coupon code

MySQL Stored Function Tutorial 2

MySQL Stored Function Tutorial 2

1. Create Another Stored Function In MySQL 
               (Here I am  using MySQl Query Browser)

DELIMITER $$


DROP FUNCTION IF EXISTS `test`.`updateOneFunction` $$
CREATE FUNCTION `test`.`updateOneFunction` (tname VARCHAR(30), 
                                         tid INT) RETURNS INT
BEGIN

             UPDATE ONE SET name=tname WHERE ID=tid;

           RETURN 1;
END $$


DELIMITER ;

_____________________________________________________________________________

2. Now Write Java Code To Call It in Your Application

        try

      {

                cn=DriverManager.getConnection("jdbc:mysql://localhost:3306/" +

                               "test","root","root");

                  st=cn.createStatement();

               st.executeUpdate("SELECT updateOneFunction('"+name+"','"+id+"')");

              rs=st.executeQuery("select * from one WHERE id='"+id+"'");

               rs.next();

                System.out.println("Updated Name = "+rs.getString(2));

     }catch(Exception e){

                e.printStackTrace();

     }

____________________________________________________________________

                                      Thank You...

MySQL Stored Function 1

MySQL Stored Function Tutorial 1

1. Create The Below Stored Function In MySQL 
               (Here I am  using MySQl Query Browser)

DELIMITER $$


DROP FUNCTION IF EXISTS `test`.`updateOneFunction` $$


CREATE FUNCTION `test`.`updateOneFunction` (tname VARCHAR(30), tid INT)
                                         RETURNS INT
BEGIN

  UPDATE ONE SET name=tname WHERE ID=tid;

  RETURN 1;
END $$


DELIMITER ;

1. Now Write Java Code To Call It in Your Application

        try

{

cn=DriverManager.getConnection("jdbc:mysql://localhost:3306/" +

"test","root","root");

st=cn.createStatement();

st.executeUpdate("SELECT updateOneFunction('"+name+"','"+id+"')");

rs=st.executeQuery("select * from one WHERE id='"+id+"'");

rs.next();

System.out.println("Updated Name = "+rs.getString(2));

}catch(Exception e){

e.printStackTrace();

}

}

MySQL Stored Procedure Tutorial 1

MySQL Stored Procedure Tutorial

                1. Create Procedure in MySQL As Below: (Here I am 

              using MySQL Query Browser).

              

                      Procedure In MySQL 'getData' on table 'one' of database 

               'test' with structer (id int, surname varchar(30))

_______________________________________________________________________________               

                DELIMITER $$

                            DROP PROCEDURE IF EXISTS `test`.`getData` $$

                            CREATE DEFINER=`root`@`localhost` PROCEDURE `getData`(IN tname         VARCHAR(25))

                            BEGIN

                                        SELECT * FROM one WHERE name=tname;

                            END $$

               DELIMITER ;

_________________________________________________________________________________

           

              So This was the code of procedure creation.. Now we will call it from java code...

              2. Following is the java code to call Procedure From 

              MySQL Database. 

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

public class testProcedure {

public static void main(String s[])

{

Connection cn;

Statement st;

            try

{

cn=DriverManager.getConnection("jdbc:mysql://localhost:3306/" +

"test","root", "root");

st=cn.createStatement();

st.execute("call getData('Ankit')");

ResultSet rs=st.getResultSet();

while(rs.next())

{

System.out.println("id= "+rs.getString(1));

System.out.println("surname= "+rs.getString(2));

}

}catch(Exception e){

e.printStackTrace();

}

}

My SQL Procedure Call Tutorial In Java

MySQL Stored Procedure Tutorial

                1. Create Procedure in MySQL As Below: (Here I am 

              using MySQL Query Browser).

              
                      Procedure In MySQL 'getData' on table 'one' of database 

               'test' with structer (id int, surname varchar(30))

_______________________________________________________________________________               
                DELIMITER $$


                            DROP PROCEDURE IF EXISTS `test`.`getData` $$
                            CREATE DEFINER=`root`@`localhost` PROCEDURE `getData`(IN tname         VARCHAR(25))
                            BEGIN
                                        SELECT * FROM one WHERE name=tname;
                            END $$


               DELIMITER ;
_________________________________________________________________________________
           
              So This was the code of procedure creation.. Now we will call it from java code...



              2. Following is the java code to call Procedure From 
              MySQL Database. 






import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;


public class testProcedure {


public static void main(String s[])
{
Connection cn;
Statement st;

            try
{
cn=DriverManager.getConnection("jdbc:mysql://localhost:3306/" +
"test","root", "root");
st=cn.createStatement();
st.execute("call getData('Ankit')");

ResultSet rs=st.getResultSet();

while(rs.next())
{
System.out.println("id= "+rs.getString(1));
System.out.println("surname= "+rs.getString(2));
}
}catch(Exception e){
e.printStackTrace();
}

}