Trigger means procedural code that is automatically executed in response to certain events on a particular table or view in a database

Your Ads

Trigger means procedural code that is automatically executed in response to certain events on a particular table or view in a database. Create trigger requires the trigger privilege for the table associated with the trigger.

TRIGGER syntax:

CREATE TRIGGER name

[BEFORE | AFTER] [INSERT | UPDATE | DELETE]

ON tablename

FOR EACH ROW statement

BEFORE or AFTER on trigger is action time to indicate when trigger activities statement activated.

INSERT or UPDATE or DELETE is event indicates the kind of statement that activates the trigger.

How to drop the TRIGGER ?, use DROP TRIGGER order following with table name and trigger name. And the syntax is like this:

DROP TRIGGER tablename.triggername;

Here they are the example case use trigger:

For inserting prosess:

1 CREATE trigger tr_input before INSERT ON user

2 FOR each

3 ROW

4 BEGIN

5 INSERT INTO master_user( id, name, pass )

6 VALUES (

7 NEW.id, NEW.name, NEW.pass

8 );

9 END$$

For updating process:

1 CREATE trigger tr_update before UPDATE ON user

2 FOR each

3 ROW

4 BEGIN

5 UPDATE master_user set id=new.id, name=new.name, pass=new.pass

6 where id=old.id;

7 END$$

For deleting process:

1 CREATE trigger tr_delete before DELETE ON user

2 FOR each

3 ROW

4 BEGIN

5 DELETE from master_user where id=old.id;

6 END$$

The SELECT privilege for the subject table if references to table colums occur via OLD.col_name or NEW.col_name

The UPDATE privilege for the subject table columns are targets of SET NEW.col_name=value assignment.

Note: in this case I use two table user and master_user. And the scenario is when I input some data or something else into user which automatically updated into master_user.

Tags: delete trigger, insert trigger, privilege, trigger, trigger mysql, update trigger

read more

CREATE PROCEDURE and CREATE FUNCTION

Filed Under: MySQL

Jun.15, 2010

CREATE PROCEDURE syntax

CREATE

[DEFINER = { user | CURRENT_USER }]

PROCEDURE sp_name ([proc_parameter[,...]])

[characteristic ...] routine_body

proc_parameter:

[ IN | OUT | INOUT ] param_name type

CREATE FUNCTION syntax

CREATE

[DEFINER = { user | CURRENT_USER }]

FUNCTION sp_name ([func_parameter[,...]])

RETURNS type

[characteristic ...] routine_body

func_parameter:

param_name type

CREATE PROCEDURE and CREATE FUNCTION require the CREATE ROUTINE privilege. They might also require the SUPER privilege, depending on the DEFINER value.

DROP PROCEDURE and DROP FUNCTION Syntax: this statement is used to drop a stored procedure or function.

DROP {PROCEDURE | FUNCTION} [IF EXISTS] procedure_or_function_name

Specifying a parameter as IN, OUT, or INOUT is valid only for a PROCEDURE. For a FUNCTION, parameters are always regarded as IN parameters. An IN parameter passes a value into a procedure. An OUT parameter passes a value from the procedure back to the caller. Its initial value is NULL within the procedure, and its value is visible to the caller when the procedure returns. An INOUT parameter is initialized by the caller, can be modified by the procedure, and any change made by the procedure is visible to the caller when the procedure returns.

Lets see a simple stored procedure using OUT parameter:

1 CREATE PROCEDURE jumlah( out juml int ) BEGIN SELECT count( * )

2 INTO juml

3 FROM FUNCTIONS;

4 END

Note: The example uses the mysql client delimiter command to change the statement delimiter from ; to // while the procedure is being defined. This allows the ; delimiter used in the procedure body to be passed through to the server rather than being interpreted by mysql itself.

And now how to CALL the procedure? Lets see this query:

1 CALL jumlah(@a);

2 SELECT @a ;

If you want to see the that procedure is exists on your server, you can try to Export as a sql and check the structure especially Add CREATE PROCEDURE / FUNCTION. On the bottom you will see the procedure you have been created:

–

– Procedures

–

DELIMITER $$

–

CREATE DEFINER=`root`@`localhost` PROCEDURE `jumlah`( out juml int )

BEGIN SELECT count( * )

INTO juml

FROM FUNCTIONS;

END$$

–

DELIMITER ;

–

And then the syntax to drop the procedure if you want to trash it:

DROP PROCEDURE IF EXISTS jumlah

Lets see a simple function:

1 CREATE FUNCTION fungsi(

2 aCHAR( 10 )

3 ) RETURNS CHAR( 20 ) DETERMINISTIC RETURN CONCAT( 'Fungsi, ', a, '.' ) ;

Then try to execute that query like this to see the result:

1 SELECT fungsi('test only');

Like procedure above you can see the function by Export as sql and the bottom look view:

CREATE DEFINER=`root`@`localhost` FUNCTION `fungsi`(a CHAR(10)) RETURNS char(20) CHARSET latin1

DETERMINISTIC

RETURN CONCAT(‘Fungsi, ‘,a,’.')

And then the syntax to drop the procedure if you want to trash it:

1 DROP FUNCTION IF EXISTS fungsi

Well improve your self with aother case and develop yours.

Tags: create function., create procedure, function syntax, procedur syntax

read more

Regular Expressions

Filed Under: MySQL

Jun.15, 2010

A regular expression describes a set of strings. A regular expressions for the REGEXP operator may use any of the following special characters and constructs:

• . match any character (including carriage return and new line)

1 SELECT function_name

2 FROM `functions`

3 WHERE function_name

4 REGEXP 'anchor..'

• ^ match the beginning of a string

1 SELECT function_name

2 FROM `functions`

3 WHERE function_name

4 REGEXP '^a';

• $ match the end of a string

1 SELECT function_name

2 FROM `functions`

3 WHERE function_description

4 REGEXP 't$';

• [characters] match any characters or using range

1 SELECT function_name

2 FROM `functions`

3 WHERE function_name

4 REGEXP '^[a-d]';

Still using function table, let’s see the other developing using regex.

To view function_name which has 4 characters:

1 SELECT function_name

2 FROM `functions`

3 WHERE function_name

4 REGEXP '^....$';

Or we can write like this:

1 SELECT function_name

2 FROM `functions`

3 WHERE function_name

4 REGEXP '^.{4}$';

Hmm I think is enough for introduction of REGEXP .

Tags: regexp, regular expression

read more

LIKE and NOT LIKE

Filed Under: MySQL

Jun.14, 2010

The like and not like have two search symbols. The underscore _ character that looks for one character and the percentage % character that looks for zero or more characters. I use function table which has function_name, function_name and function_description fields. Lets see the example:

1 SELECT *

2 FROM `functions`

3 WHERE function_name LIKE 'a%'

4 LIMIT 0 , 30

Above query will only pick out result that provide a TRUE result according to the WHERE equation. We can see that equation will equal the LIKE value plus some possible extra characters afterwards.

The LIKE search is not case sensitive, so it will accept anything starting with ‘a’ as well.

So how LIKE search can make a different lowercase or uppercase letters? by adding BINARY word after LIKE.

1 SELECT *

2 FROM `functions`

3 WHERE function_name LIKE BINARY "a%"

4 LIMIT 0 , 30;

And the change the query like below to see the different:

1 SELECT *

2 FROM `functions`

3 WHERE function_name LIKE BINARY "A%"

4 LIMIT 0 , 30;

Queries using the LIKE or NOT LIKE parameters may be a bit slower than a normal query search considering they are a broader value and do not take advantage of any indexing.

Note: If you want to have an underscore or percentage character actually be part of the search value, put an escape slash \ in front of the character.

The underscore wildcard can be used a number of times to find a specific number of characters. Example, this would be used in an equation to return a value of ‘Stan’ plus 3 characters.

1 SELECT *

2 FROM `functions`

3 WHERE function_name LIKE BINARY "mdat___"

4 LIMIT 0 , 30;

The underscore and percentage characters (also known as wildcard) can be used in front, at the end, or both ends of a value.

Tags: like, like and not like, mysql, not like, select

read more

Operator Precedence

Filed Under: MySQL

Jun.14, 2010

The precedence of operators determines the order of evaluation. To override this order and group terms explicity, use parentheses. Lets see the example below:

1 Select 6+2-4*1, (6+2-4)*1;

Operator are shown bellow from lowest to the highest precedence. Operators shown together on a line have the same precedence. Lets take a look:

:=

||, OR, XOR

&&, AND

NOT

BETWEEN, CASE, WHEN, THEN, ELSE

=, <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN

|

&

<<, >>

-, +

*, /, DIV, %, MOD

^

- (unary minus), ~ (unary bit inversion)

!

BINARY, COLLATE

Note: If the HIGH_NOT_PRECEDENCE SQL mode is enabled, the precedence of NOT is the same as that of the ! operator.

Tags: operator precedence

read more

• Search

Search for:

• Calendar

June 2011

M T W T F S S

« Jul

1 2 3 4 5

6 7 8 9 10 11 12

13 14 15 16 17 18 19

20 21 22 23 24 25 26

27 28 29 30

• Recent Posts

o CodeIgniter 2.0

o TRIGGER

o Connecting Multiple Database

o Paid to Write and Review

o CREATE PROCEDURE and CREATE FUNCTION

• Archives

o July 2010

o June 2010

o May 2010

o April 2010

Your Ads