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

1、语法： 
CREATE TRIGGER <触发器名称> --触发器必须有名字，最多64个字符，可能后面会附有分隔符.它和MySQL中其他对象的命名方式基本相象. 
{ BEFORE | AFTER } --触发器有执行的时间设置：可以设置为事件发生前或后。 
{ INSERT | UPDATE | DELETE } --同样也能设定触发的事件：它们可以在执行insert、update或delete的过程中触发。 
ON <表名称> --触发器是属于某一个表的:当在这个表上执行插入、 更新或删除操作的时候就导致触发器的激活. 我们不能给同一张表的同一个事件安排两个触发器。 
FOR EACH ROW --触发器的执行间隔：FOR EACH ROW子句通知触发器 每隔一行执行一次动作，而不是对整个表执行一次。 
<触发器SQL语句> --触发器包含所要触发的SQL语句：这里的语句可以是任何合法的语句， 包括复合语句，但是这里的语句受的限制和函数的一样。 
--你必须拥有相当大的权限才能创建触发器（CREATE TRIGGER），如果你已经是Root用户，那么就足够了。这跟SQL的标准有所不同。 
mysql的触发器很有意思 
比如当前插入行的A=10,触发器里要修改为B，只要在BEFORE INSERT 里，SET NEW.A=20,就可以了 
但只能在BEFORE里使用NEW，AFTER不行 

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$$