mysql 触发器调用存储过程_MySQL触发器、存储过程、自定义函数、视图实例

NEW 是新值-- OLD 是旧值

INSERT 只有NEW ----UPDATE有NEW和OLD ---DELETE只有OLD

2、简单实例:

example1:

--创建表tab1

DROP TABLE IF EXISTS tab1;

CREATE TABLE tab1(

tab1_id varchar(11)

);

--创建表tab2

DROP TABLE IF EXISTS tab2;

CREATE TABLE tab2(

tab2_id varchar(11)

);

--创建触发器:t_afterinsert_on_tab1

--作用：增加tab1表记录后自动将记录增加到tab2表中

DROP TRIGGER IF EXISTS t_afterinsert_on_tab1;

CREATE TRIGGER t_afterinsert_on_tab1

AFTER INSERT ON tab1

FOR EACH ROW

BEGIN

insert into tab2(tab2_id) values(new.tab1_id);

END;

--测试一下 ：INSERT INTO tab1(tab1_id) values('0001');

--看看结果： SELECT * FROM tab1; SELECT * FROM tab2;

MySQL触发器、存储过程、自定义函数、视图实例

0.test数据库有userinfo用户信息表 和userinfolog用户信息日志表

1.建立一个userinfo表新增记录时的触发器 将新增日志加入到userinfolog

2.建立一个向userinfo表新增记录的存储过程

3.根据userinfo表的出生日期字段 我们将建立一个简单算得年龄的自定义函数

4.创建一个userinfo的视图 调用年龄函数

－－－－－－－－－－－－－

0.准备相关表

mysql> use test;

mysql> create table userinfo(userid int,username varchar(10),userbirthday date);

mysql> create table userinfolog(logtime datetime,loginfo varchar(100));

mysql> describe userinfo;

1.触发器

mysql> delimiter |

mysql> create trigger beforeinsertuserinfo

-> before insert on userinfo

-> for each row begin

-> insert into userinfolog values(now(),CONCAT(new.userid,new.username));

-> end;-> |

mysql> delimiter ;

mysql> show triggers;

2.存储过程

mysql> delimiter //

mysql> create procedure spinsertuserinfo(

-> puserid int,pusername varchar(10)

-> ,puserbirthday date

-> )

-> begin

-> insert into userinfo values(puserid,pusername,puserbirthday);

-> end;-> //

mysql> show procedure status like 'spinsertuserinfo';

mysql> call spinsertuserinfo(1,'zhangsan',current_date);

mysql> select * from userinfo;

3.自定义函数

mysql> update userinfo

-> set userbirthday='2000.01.01'

-> where userid='1';

mysql> drop function if exists fngetage;

mysql> delimiter //

mysql> create function fngetage(pbirthday date)

-> returns integer

-> begin

-> return year(now()) - year(pbirthday);

-> end-> //

4.视图

mysql> create view viewuserinfo

-> as select * ,fngetage(userbirthday) as userage from userinfo;

mysql> select * from viewuserinfo;

清除日志记录

mysql> truncate table userinfolog;

mysql> delete from userinfolog;

=======================================

DELIMITER $$;

DROP FUNCTION IF EXISTS `sakila`.`inventory_in_stock`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `inventory_in_stock`(p_inventory_id INT) RETURNS tinyint(1)

READS SQL DATA

BEGIN

DECLARE v_rentals INT;

DECLARE v_out INT;

#AN ITEM IS IN-STOCK IF THERE ARE EITHER NO ROWS IN THE rental TABLE

#FOR THE ITEM OR ALL ROWS HAVE return_date POPULATED

SELECT COUNT(*) INTO v_rentals

FROM rental

WHERE inventory_id = p_inventory_id;

IF v_rentals = 0 THEN

RETURN TRUE;

END IF;

SELECT COUNT(rental_id) INTO v_out

FROM inventory LEFT JOIN rental USING(inventory_id)

WHERE inventory.inventory_id = p_inventory_id

AND rental.return_date IS NULL;

IF v_out > 0 THEN

RETURN FALSE;

ELSE

RETURN TRUE;

END IF;

END$$

分享到：

2010-03-22 17:09

浏览 6366

分类:数据库

评论