事务、存储过程和触发器和函数

1、事务

Begin Trans用于开始一个事物；Rollback Trans用于回滚事务；Commit Trans用于提交所有的事务处理结果，即确认事务的处理。

事务处理可以将一组操作视为一个整体，只有全部语句都成功执行后，事务处理才算成功；若其中有一个语句执行失败，则整个处理就算失败，并恢复到处里前的状态。 BeginTrans和CommitTrans用于标记事务的开始和结束，在这两个之间的语句，就是作为事务处理的语句。

判断事务处理是否成功，可通过连接对象的Error集合来实现，若Error集合的成员个数不为0，则说明有错误发生，事务处理失败。Error集合中的每一个Error对象，代表一个错误信息。

事务具有原子性、一致性等等特性。

注意：

begin...end代表一个语句块，类似于C/c++/java中的{}，不是一个事务。

2、存储过程

sqlserver:不可以在某个数据库下创建（error----'CREATE/ALTER PROCEDURE' 必须是查询批次中的第一个语句。）

--定义：

Create proc insert_book
   @param1 char(10),@param2 varchar(20),   @param3 money,

   @param4 money output

with encryption ---------加密
as
   insert book(编号,书名，价格）
        Values(@param1,@param2,@param3)
   select @param4=sum(价格) from book
go


--执行：
exec insert_book '003','Delphi',$100,@total_price

mysql:可以在某个数据库下创建

--定义

create procedure insert_Student (_name varchar(50),_age int ,out _id int)
begin
 insert into student value(null,_name,_age);
 select max(stuId) into _id from student;
end;
 

--执行
call insert_Student('wfz',23,@id);
select @id;

一个存储过程可以包含多个事务，一个事务中也可以包含多个存储过程。

3、触发器

触发器和普通的存储过程的区别是：触发器是当对某一个表进行操作。诸如：update、insert、delete这些操作的时候，系统会自动调用执行该表上对应的触发器。

注意的是：触发器本身就是一个事务，所以在触发器里面可以对修改数据进行一些特殊的检查。

如果不满足可以利用事务回滚，撤销操作。

sqlserver:

（只有 之后触发 和 代替 instead of 触发，若要实现之前触发，可以在代替触发中的最后执行原本语句----不会递归）

    其中after触发器要求只有执行某一操作insert、update、delete之后触发器才被触发，且只能定义在表上。而instead of触发器表示并不执行其定义的操作（insert、update、delete）而仅是执行触发器本身。既可以在表上定义instead of触发器，也可以在视图上定义。 

    触发器有两个特殊的表：插入表（instered表）和删除表（deleted表）。这两张是逻辑表也是虚表。有系统在内存中创建者两张表，不会存储在数据库中。而且两张表的都是只读的，只能读取数据而不能修改数据。这两张表的结果总是与被改触发器应用的表的结构相同。当触发器完成工作后，这两张表就会被删除。Inserted表的数据是插入或是修改后的数据，而deleted表的数据是更新前的或是删除的数据。（mysql中也有吗？？？？？？？）

--定义

--创建insert插入类型触发器
if (object_id('tgr_classes_insert', 'tr') is not null)
    drop trigger tgr_classes_insert
go
create trigger tgr_classes_insert
on classes
    for insert --插入之后触发
as
    --定义变量
    declare @id int, @name varchar(20);
    --在inserted表中查询已经插入记录信息
    select @id = id, @name = name from inserted;
     insert into student values(@name, @id);
    print '添加学生成功！';
go

执行：
--插入数据
insert into classes values('5班', getDate());

 

mysql

（有之前和之后两种触发器，没有instead of触发器？？？？）

mysql的insert触发器或者是update触发器的被插入数据是用NEW对象来存储的，它不是一张表，也不是一个变量，它是一个对象，其中的内部成员变量就是每个字段，

这样就容易理解了：NEW.columnName可以取得新的要插入的记录的列。

其中还有一个是OLD对象，这个对象是你要删除的那条记录。使用方法和作用NEW差不多，但存储的记录不同。

mysql没有mssql中的INSERTED 和 DELETED表，却有NEW和OLD两个记录对象，但是，这两个对象分别只能存储一条记录，而不能存储多条记录，因为它们不是表啊。

--定义：

create trigger insertArticle_Trigger after insert on article1 for each row

begin
   update board1 set articleCount=articleCount+1 where id= NEW.bid;
end; 

--执行

insert into board1 value (null,'test',0);

4、函数

sqlserver

Create function 函数名（参数）

Returns 返回值数据类型

[with {Encryption | Schemabinding }]

[as]

begin

SQL语句(必须有return )

End

----------------------------------------

CREATE FUNCTION dbo.Max
(
@a int,
@b int
)
RETURNS int AS
BEGIN
DECLARE @max int
IF @a>@b SET @max=@a
ELSE SET @max=@b
Return @max
END

CREATE FUNCTION func(@selection int)
RETURNS @table TABLE
(
SID char(4) primary key not null,
SName nvarchar(4) null
)
AS
BEGIN
IF @selection = 0
INSERT INTO @table (SELECT SID,SName FROM student0)
ELSE
INSERT INTO @table (SELECT SID,SName FROM student1)
Return
END

mysql

--定义

mysql>
mysql> delimiter $$
mysql>
mysql> CREATE FUNCTION myFunction
    ->    (in_string      VARCHAR(255),
    ->     in_find_str    VARCHAR(20),
    ->     in_repl_str    VARCHAR(20))
    ->
    ->   RETURNS VARCHAR(255)

    -> BEGIN
    ->   DECLARE l_new_string VARCHAR(255);
    ......
    ->   RETURN(l_new_string);
    ->
    -> END$$

--执行：
mysql> delimiter ;
mysql> select myFunction('ABC','A','Z');

--删除
mysql> drop function myFunction;
Query OK, 0 rows affected (0.00 sec)

函数中的语句，为了安全，是不是应该写在事务中？？（函数若执行一半，出错中止，但已经执行的一半是取消（回滚）还是保持？？？）
函数这一块还需仔细总结 TODO