Studio Press

Just another Blogger Blog

Friends Link

Dear Friend, Please Copy Link Below, Add Me and Then Buzz on this post Comment to do Link Exchange.







A
B

C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z

Using Cursor In MySQL PLSQL : Trigger

A Cursor can be imagine as A Table in MySQL PLSQL. We can use a cursor as temp table that we process in the PLSQL.

Below i have a simple MySQLTrigger to show how cursor can be use. I use MySQL version 5.1.33.

Please Just See A Red Row of the Program, That i will Explain.


DELIMITER $$

DROP TRIGGER /*!50032 IF EXISTS */ `db_pengabdian`.`update_jum_pengabdi`$$

CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `update_jum_pengabdi` AFTER UPDATE ON `tb_mketua`
FOR EACH ROW BEGIN
DECLARE done INT DEFAULT 0;

DECLARE nip CHAR(30);
DECLARE yr YEAR;
DECLARE jum INT;

DECLARE cur1 CURSOR FOR select distinct(nip_anggota), tahun from tb_manggota union all select distinct(nip_ketua), tahun from tb_mketua;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
set jum = 0;
REPEAT
FETCH cur1 INTO nip, yr;
IF yr = old.tahun THEN
set jum=jum+1;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
set jum=jum-1;
update tb_dwh_pengabdian set pengabdian_jum_dosen_mengabdi = jum where pengabdian_tahun=old.tahun;


END;
$$

DELIMITER ;



The Red Row of the syntax is a row definiion, then a fetch (something we said to cut per row piece). And at the Green Line, you can find that if the table change on field year has a similar value with a cursor fetch, do something.

That is a simple example of using cursor in In MySQL PLSQL, Using Cursor in a Trigger.

Backup MySQL Database With SQL Yog

After Read a Post about Safely Backup MySQL Database With SQL Yog,

Bulk SQL Statement and Non Bulk Insert Statement

and

Backup MySQL Database Safely as SQL File with SQL Yog


Below is the step by step we doing a MySQL Database backup with SQL Yog :

1. Right Click A Database

Backup MySQL, SQL Yog, Bulk Insert
Figure 1.
2. Choose Back Up Database
3. Choose Backup Options
Backup MySQL, SQL Yog, Bulk Insert
Figure 2.
4. Export and done.


The Explanation of Figure 2
  • All Check on table, stored procedure and trigger mean The backup doing all backup include table, views, stored procedure, trigger, event and function
  • Structure and Data Check mean The Backup doing a Data and Structure Backup.
  • File Name is a Must.
  • Do not Check Use Bulk Insert Statement if you have a database that has great size.
There was an error in this gadget

Labels

Message For Me

Alexa Rank

SEO Stats

Followers

FEEDJIT Live Traffic Feed