SQL及其使用(1)

SQL及其使用(1)

3.1、触发器

3.1.1、触发器的作用

•  安全性。可以基于数据库的值使用户具有操作数据库的某种权利。

             可以基于时间限制用户的操作，例如不允许下班后和节假日修改数据库数据。

             可以基于数据库中的数据限制用户的操作，例如不允许股票的价格的升幅一次超过10%。

•   审计。可以跟踪用户对数据库的操作。  

            审计用户操作数据库的语句。

            把用户对数据库的更新写入审计表。

•   实现复杂的数据完整性规则

            实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。与规则不同，触发器可以引用列或数据库对象。例如，触发器可回退任何企图吃进超过自己

           保证金的期货。

•  提供可变的缺省值。

•  实现复杂的非标准的数据库相关完整性规则。触发器可以对数据库中相关的表进行连环更新。例如，在auths表author_code列上的删除触发器可导致相应删除在其它表中的与之匹配的行。

             在修改或删除时级联修改或删除其它表中的与之匹配的行。

             在修改或删除时把其它表中的与之匹配的行设成NULL值。

             在修改或删除时把其它表中的与之匹配的行级联设成缺省值。

             触发器能够拒绝或回退那些破坏相关完整性的变化，取消试图进行数据更新的事务。当插入一个与其主健不匹配的外部键时，这种触发器会起作用。例如，可以在

             books.author_code 列上生成一个插入触发器，如果新值与auths.author_code列中的某值不匹配时，插入被回退。

•  同步实时地复制表中的数据。

•  自动计算数据值，如果数据的值达到了一定的要求，则进行特定的处理。例如，如果公司的帐号上的资金低于5万元则立即给财务人员发送警告数据。

3.1.2、触发器的基本语法

1、触发器的创建

CREATETRIGGER <触发器名称>  --触发器必须有名字，最多64个字符，可能后面会附有分隔符.它和MySQL中其他对象的命名方式基本相象.

{BEFORE | AFTER }  --触发器有执行的时间设置：可以设置为事件发生前或后。

{INSERT | UPDATE | DELETE }  --同样也能设定触发的事件：它们可以在执行insert、update或delete的过程中触发。

ON <表名称>  --触发器是属于某一个表的:当在这个表上执行插入、 更新或删除操作的时候就导致触发器的激活. 我们不能给同一张表的同一个事件安排两个触发器。

FOR EACH ROW  --触发器的执行间隔：FOR EACH ROW子句通知触发器 每隔一行执行一次动作，而不是对整个表执行一次。

<触发器SQL语句>  --触发器包含所要触发的SQL语句：这里的语句可以是任何合法的语句， 包括复合语句，但是这里的语句受的限制和函数的一样。

create trigger trigger_name{after | before} { insert | update | delete } on table_namefor each rowbegin       SQL语句/SQL变量end;

2、触发器的删除：

drop trigger if exists trigger_name

3、触发器之new old 方法

"NEW . column_name"或者"OLD . column_name".这样在技术上处理（NEW | OLD . column_name）新和旧的列名属于创建了过渡变量（"transition variables"）。

对于INSERT语句,只有NEW是合法的；对于DELETE语句，只有OLD才合法；而UPDATE语句可以在和NEW以及OLD同时使用。

3.1.3、示例

1、使用示例

测试数据库：

create table shares(idvarchar(10),price int);create table sharescopy(id varchar(10),price int);

示例1：同步实时复制数据表

create trigger after_insert_shares afterinsert on shares for each rowbegin   insert into sharescopy values(new.id,new.price);end;

示例2：不允许股票的价格的升幅一次超过10%

create trigger before_update_shares before updateon shares for each rowbegin       ifnew.price >old.price*1.1 then              setnew.price = old.price*1.1;       endif;end;

示例3：不允许下班后和节假日修改数据库数据

create trigger change_shares before updateon shares for each rowbeginif (now() >str_to_date('2017-5-29 14:20:00','%Y-%m-%d %T')) theninsert into xxx values(1);end if;end;

MySQL 不像其它有些数据库可以在触发器中抛出异常来中断当然触发器的执行以阻止相应的SQL语句的执行。在MySQL的目录版本中还无法直接抛出异常。可通过执行错误的SQL来中断执行。

           

2、MySql触发器的问题

mysql 触发器的问题Can't update table 'tbl' in storedfunction/trigger because it is already used by state。如果你在触发器里面对刚刚插入的数据进行了 insert/update,则出现这个问题。因为会造成循环的调用.

如下面的语句会出现上述的错误：

create trigger before_update_shares beforeupdate on shares for each rowbegin       ifnew.price >old.price*1.1 then              updateshares set new.price = old.price*1.1 where id =new.id;       endif;end;

正确的用法是示例2

3、MySql中 delimiter

delimiter的作用告诉MySQL解释器，该段命令是否已经结束了，mysql是否可以执行了。默认情况下，delimiter默认是分号;。在命令行客户端中，如果有一行命令以分号结束，那么回车后，mysql将会执行该命令。但有时候，不希望MySQL这么做。因为可能输入较多的语句，且语句中包含有分号（如上面触发器的创建），这种情况下，就需要事先把delimiter换成其它符号，一般是//或$$。

       如果直接用上述的命令创建触发器，由于delimiter默认是分号，上述语句会出现语法错误，可做如下修改。

delimiter //create trigger after_insert_shares after insert on shares for each row begin    insert into sharescopy values(new.id,new.price);end;//delimiter ; //              将delimiter改为默认的分号;