触发器和游标的用法

 

declare @d datetime
set @d=getdate()
select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())



查找数据库中的存储过程
select name from sysobjects where xtype='TR'

如何禁用、启用触发器

禁用：alter table 表名 disable trigger 触发器名称
启用：alter table 表名 enable trigger 触发器名称

如果有多个触发器，则各个触发器名称之间用英文逗号隔开。

如果把“触发器名称”换成“ALL”，则表示禁用或启用该表的全部触发器。

AFTER 和INSTEAD OF 两种类型的触发器

使用注意：以下的Product表必须设置主键，否则无法运行正确

AFTER 触发器
仅在触发SQL语句中指定的所有操作已成功才被激发。所有的引用级联操作和约束检查也必须在激发此触发器之前成功完成
如果仅指定FOR关键字，则AFTER为默认的


INSTEAD OF 触发器
其优先级高于SQL语句

对于表或试图。每个insert、update或delete语句最多定义一个INSTEAD OF触发器
注意：该触发器不可以用于使用了WITH CHECK OPTION 的视图

create trigger DeleteSomeThing
on Product
instead of Delete
as
begin
 insert into tb (ID,名称,备注) values(123,'我是删除Product表','哈哈')
end

Create trigger UpdateThings
on Product
for UPDATE
as
if UPDATE ([客户订数])
BEGIN
 update tb set[名称]='已经更改啦' where  [名称]='我是删除Product表'
END

create trigger tr_delete on ReaderInfo
for delete
as
begin
  if exists(select 1 from BookInfo a , deleted b where a.readerid = b.readerid)
    rollback
end


inserted、deleted
这是两个虚拟表，inserted 保存的是 insert 或 update 之后所影响的记录形成的表，deleted 保存的是 delete 或 update 之前所影响的记录形成的表。例：
create trigger tbl_delete
on tbl
for delete
as
    declare @title varchar(200)
    select @title=title from deleted
    insert into Logs(logContent) values('删除了 title 为：' + title + '的记录')
说明：如果向 inserted 或 deleted 虚拟表中取字段类型为 text、image 的字段值时，所取得的值将会是 null。



触发器回滚

我们看到许多注册系统在注册后都不能更改用户名，但这多半是由应用程序决定的， 如果直接打开数据库表进行更改，同样可以更改其用户名，在触发器中利用回滚就可以巧妙地实现无法更改用户名。

use 数据库名
go
create trigger tr
on 表名
for update
as
    if update(userName)
        rollback tran


 游标种类
MS SQL SERVER 支持三种类型的游标：Transact_SQL 游标，API 服务器游标和客户游标。
（1） Transact_SQL 游标
    Transact_SQL 游标是由DECLARE CURSOR 语法定义、主要用在Transact_SQL 脚本、存储过程和触发器中。Transact_SQL 游标主要用在服务器上，由从客户端发送给服务器的Transact_SQL 语句或是批处理、存储过程、触发器中的Transact_SQL 进行管理。 Transact_SQL 游标不支持提取数据块或多行数据。
（2） API 游标
    API 游标支持在OLE DB， ODBC 以及DB_library 中使用游标函数，主要用在服务器上。每一次客户端应用程序调用API 游标函数，MS SQL SEVER 的OLE DB 提供者、ODBC驱动器或DB_library 的动态链接库（DLL）都会将这些客户请求传送给服务器以对API游标进行处理。
（3） 客户游标
    客户游标主要是当在客户机上缓存结果集时才使用。在客户游标中，有一个缺省的结果集被用来在客户机上缓存整个结果集。客户游标仅支持静态游标而非动态游标。由于服务器游标并不支持所有的Transact-SQL 语句或批处理，所以客户游标常常仅被用作服务器游标的辅助。因为在一般情况下，服务器游标能支持绝大多数的游标操作。
    由于API 游标和Transact-SQL 游标使用在服务器端，所以被称为服务器游标，也被称为后台游标，而客户端游标被称为前台游标


STR ( float_expression [ , length [ , decimal ] ] )
 float_expression
    带小数点的近似数字 (float) 数据类型的表达式。
length
    总长度。它包括小数点、符号、数字以及空格。默认值为 10。
decimal
    小数点后的位数。decimal 必须小于或等于 16。如果 decimal 大于 16，则会截断结果，使其保持为小数点后具有十六位。


truncate table tb

declare @i int
set @i=1
while @i<100
begin
insert into tb (ID,名称,备注) values(@i,REPLACE('名称'+str(@i,2,0),'   ','')  ,REPLACE('备注'+str(@i,2,0),'   ','') )
set @i=@i+1
end

游标的例子：

  --声明一个游标
     Declare curStudentFee Cursor
       for
       Select StudentFeeID From StudentFee      

     --声明两个费用变量
     Declare @mBorrowBookAllFee Money  --总费用
     Declare @iStudentFeeID     Int    --借书结算号

     --初始化  
     Set @mBorrowBookAllFee=0
     Set @iStudentFeeID=0

     --打开游标
     Open curStudentFee  

     --循环并提取记录
     Fetch Next From curStudentFee Into @iStudentFeeID     
     While ( @@Fetch_Status=0 )    
     begin

       --从借书记录中计算某一学生的借书总记录的总费用
       Select @mBorrowBookAllFee=Sum(BorrowBookAllFee)
         From BorrowRecord
         Where StudentFeeID=@iStudentFeeID     

       --更新到汇总表。
       Update StudentFee Set BorrowBookAllFee=@mBorrowBookAllFee
         Where StudentFeeID=@iStudnetFeeID           

       Fetch Next From curStudentFee Into @mFee
     end

     --关闭游标    
     Close curStudentFee

     --释放游标
     Deallocate curStudentFee

关注游标的要点：1、声明、打开、关闭、释放 ； 2、@@Fetch_Status 游标提取状态标志，0表示正确

比较好的例子：
CREATE TRIGGER [TRGJCDNAME] ON dbo.TABLEJCD
FOR INSERT, UPDATE
AS
   DECLARE
   @CODE VARCHAR(50),
   @XS FLOAT,
   @ROWCOUNT INT
  --检查代码是否为空
 
  //只支持1条操作，如果有多条操作则跳过
  IF @@ROWCOUNT>1 RETURN

  SELECT @CODE=LTRIM(ISNULL(CODE,'''')) FROM INSERTED
  IF @CODE=''''
  BEGIN
     ROLLBACK TRAN
     RAISERROR(''代码不允许为空或空格!'',16,1)
  END
  --检查代码是否有重复的
  SELECT @ROWCOUNT=COUNT(*) FROM TABLEJCD,INSERTED
  WHERE UPPER(TABLEJCD.CODE)=UPPER(INSERTED.CODE) AND (INSERTED.SYSTEMID=TABLEJCD.SYSTEMID)
  --如果有重复
  IF @ROWCOUNT>1
  BEGIN
      ROLLBACK TRAN
      RAISERROR(''代码重复,请修改!'',16,1)
  END
  --不允许为0或负数
  SELECT @XS=ISNULL(XS,0) FROM INSERTED
  IF @XS<=0
  BEGIN
      ROLLBACK TRAN
      RAISERROR(''不允许为0或负数!'',16,1)
  END
2.当操作多条记录时（及联删除或更新）
create trigger del_id on tablename for delete
as
  delete from tablename where id in(select id from deleted)
3.递规触发器
  create tablebudget
 (
  dept_name varchar(30) not null,
  part_name varchar(30) null,
  budget_amt money not null)
在上面的表，记录之间是关联，比如部门a,隶属于部门b，顶级部门的父部门为空，我们想到，子部门的预算更新时，其对应的父部门的预算，也要更新

create trigger update_budget
on budget for update as

if (@@rowcount>1)
begin
  print ''only on row can be update at a time''
  rollback tran
  return
end
if (select parent_name from inserted ) is null return
update
  set budget_amt=budget_amt+(select budget_amt from inserted)-
                     (select budget_amt from deleted)
where dept_name=(select parent_name from inserted)

更新时，其对应的父部门

RAISERROR 和@@error用法的不同点？

RAISERROR ( { msg_id | msg_str } { , severity , state }
    [ , argument [ ,...n ] ] )
    [ WITH option [ ,...n ] ]

severity 任何用户都可以指定0到18，只有SYSADMIN固定服务器角色成员或具备alter trace 权限的
用户才可以指定19到25之间的严重级别。如果要使用19到25之间的严重级别，则必须选择WITH LOG选项

state 为1到127之间的数字

msg_str中可以用到格式参数
类型规范                         表示

d或i                              有符号整数
o                                  无符号八进制数
s                                 字符串
u                                 无符号整数
x或X                               无符号十六进制数

用例：
declare @count int
select @count=count(*) from jobs where max_lvl>1000
if @count=0
raiserror('有%d个max_lvl>1000的工作',16,1,@count)--》可自定制返回的信息
相应于vb，err.number可以取msg_id，而err.description可以取msg_str了


如果最后的 Transact-SQL 语句执行成功，则 @@ERROR 系统函数返回 0；如果此语句产生错误，则 @@ERROR 返回错误号。每一个 Transact-SQL 语句完成时，@@ERROR 的值都会改变。
@@error 是返回错误代码，不是错误性信息

@@ROWCOUNT和@@ERROR变量的值，在执行完一条语句后总是会发生变化，所以我们将他们作为判断的依据的时候应该首先保存在局部变量中。他们反映的都是紧接着的上一条语句对他们的影响！

declare @sd int
set @sd=ABS(3.45)
 waitfor  delay  '00:00:10' ---》延时10秒钟
print @sd

 

 

 

 

 

--声明rs为局域变量，否则第二次执行会报rs已定义的错误     

declare @s varchar(max); 

DECLARE rs CURSOR local FOR select OrgCode,ExchangeNum,SaleNum,LocalProductNum,OtherProductNum,SubsidyNum,[Year],[Month] from sdds

set @s='1';

OPEN rs 

declare @OrgCode float,@ExchangeNum float,@SaleNum float,@LocalProductNum float,@OtherProductNum float,@SubsidyNum float,@Year float,@Month float

 

FETCH NEXT FROM rs INTO @OrgCode,@ExchangeNum,@SaleNum,@LocalProductNum,@OtherProductNum,@SubsidyNum,@Year,@Month

WHILE @@FETCH_STATUS = 0

BEGIN

set @s='insert into T_Subsidy(OrgCode,ExchangeNum,SaleNum,LocalProductNum,OtherProductNum,SubsidyNum,Year,Month)values('+cast(@OrgCode as nvarchar)+','+cast(@ExchangeNum as varchar)+','+cast(@SaleNum as varchar)+','+cast(@LocalProductNum as varchar)+','+cast(@OtherProductNum as varchar)+','+cast(@SubsidyNum as varchar)+','+cast(@Year as varchar)+','+cast(@Month as varchar)+')';

 print @s

FETCH NEXT FROM rs INTO @OrgCode,@ExchangeNum,@SaleNum,@LocalProductNum,@OtherProductNum,@SubsidyNum,@Year,@Month

END

 

CLOSE  rs 

 

 

GO