初探sqlserver触发器

触发器对表进行插入、更新、删除的时候会自动执行的特殊存储过程。触发器一般用在check约束更加复杂的约束上面。触发器和普通的存储过程的区别是：触发器是当对某一个表进行操作。诸如：update、insert、delete这些操作的时候，系统会自动调用执行该表上对应的触发器。SQL Server 2005中触发器可以分为两类：DML触发器和DDL触发器，其中DDL触发器它们会影响多种数据定义语言语句而激发，这些语句有create、alter、drop语句。

    DML触发器分为：
    1、 after触发器（之后触发）
        a、 insert触发器
        b、 update触发器
        c、 delete触发器

    2、 instead of 触发器 （之前触发）

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

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

对表的操作                 Inserted逻辑表           Deleted逻辑表

增加记录（insert）         存放增加的记录                  无

删除记录（delete）               无                存放被删除的记录

修改记录（update）         存放更新后的记录         存放更新前的记录

    Update数据的时候就是先删除表记录，然后增加一条记录。这样在inserted和deleted表就都有update后的数据记录了。注意的是：触发器本身就是一个事务，所以在触发器里面可以对修改数据进行一些特殊的检查。如果不满足可以利用事务回滚，撤销操作。

-----简单的
create trigger change_shiw on [user]
after insert 
as
select * from [user]



--判断数据库对象是否存在
if (exists (select * from sys.objects where name = 'stopwangwu'))
    print '存在';
go

--禁止王五发帖 更新的触发器
alter trigger stopwangwu on [bbs]
for insert
as
begin
if(select b.bbsower from bbs b,inserted i where b.bbsid=i.bbsid)=3
select * from inserted
print '王五禁ID了'
rollback 
end
---test  
go
select b.bbsower from bbs b,[user] i where b.bbsower=i.userid and b.bbsower=3
insert into bbs values(8,'ssss',3,'sssssss')

-----T-SQL

declare @name varchar(20),@result varchar(100);
set @name='张飒';
set @result='hello'+@name;
select @result;

--查询列子
declare @name varchar(20);
set @name='李四';
select * from [user] where username=@name;
----
declare @name varchar(20),@uid int;
set @uid=3;
select @name=username from [user] where userid=@uid;
select @name

---全局变量
全局变量
select @@identity;--最后一次自增的值
select identity(int, 1, 1) as id into tab from [user];--将user表的属性，以/1自增形式创建一个tab
select * from tab;
select @@rowcount;--影响行数
select @@cursor_rows;--返回连接上打开的游标的当前限定行的数目
select @@error;--T-SQL的错误号
select @@procid;
--配置函数
set datefirst 7;--设置每周的第一天，表示周日
select @@datefirst as '星期的第一天', datepart(dw, getDate()) AS '今天是星期';
select @@dbts;--返回当前数据库唯一时间戳
set language 'Italian';
select @@langId as 'Language ID';--返回语言id
select @@language as 'Language Name';--返回当前语言名称
select @@lock_timeout;--返回当前会话的当前锁定超时设置（毫秒）
select @@max_connections;--返回SQL Server 实例允许同时进行的最大用户连接数
select @@MAX_PRECISION AS 'Max Precision';--返回decimal 和numeric 数据类型所用的精度级别
select @@SERVERNAME;--SQL Server 的本地服务器的名称
select @@SERVICENAME;--服务名
select @@SPID;--当前会话进程id
select @@textSize;
select @@version;--当前数据库版本信息
 
--系统统计函数
select @@CONNECTIONS;--连接数
select @@PACK_RECEIVED;
select @@CPU_BUSY;
select @@PACK_SENT;
select @@TIMETICKS;
select @@IDLE;
select @@TOTAL_ERRORS;
select @@IO_BUSY;
select @@TOTAL_READ;--读取磁盘次数
select @@PACKET_ERRORS;--发生的网络数据包错误数
select @@TOTAL_WRITE;--sqlserver执行的磁盘写入次数

----输出函数 print在输出值不少字符串的情况下，
--需要用convert转换成字符串才能正常输出，而且字符串的长度在超过8000的字符以后，
--后面的将不会显示
select 1+1;
print 1+1;
print @@language;
--循环
declare @i int
set @i=1;
while  (@i<=100)
begin
print @i;
set @i=@i+1;
end

declare @i int,@sum int;
set @i=1;
set @sum=0;
while  (@i<=100)
	begin
		set @sum=@sum+@i;
		set @i=@i+1;
		if(@i>50)
			--break; --1275
			continue;--5050
	end
print @sum;

--case

select * ,
	case userid
		when 1 then '可乐'
		when 2 then '糖浆'
		else  'Sb'
		end as '外号'
from [user]

select * ,
	'大赢家'=
		case 
			when username like '%张%' then '中奖了'
			else 'other'
		end
from [user]
--定时
waitfor delay '00:00:03';--定时三秒后执行
print '定时三秒后执行';	
	
--update 触发 修改一条数据  就复制老数据
if (exists (select * from sys.objects where name = 'tri_inseuser'))
    print '存在,重新创建';
    drop trigger tri_inseuser;
go

create trigger tri_inseuser on [user] for update
as
declare @uid int,@uname varchar(100);
select @uid=i.userid,@uname=i.username from [user] u,deleted i where u.userid=i.userid;--老数据
insert into [user](username) values (@uname);
--test
update [user] set username='卡k卡' where userid=6;
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 


--delete删除类型触发器
if (object_id('tgr_user_delete', 'TR') is not null)
	print '存在,将重新创建';
    drop trigger tgr_classes_delete
go
create trigger tgr_classes_delete
on [user]
    for delete --删除触发
as
    print '备份数据中……';    
    if (object_id('classesBackup', 'U') is not null)
        --存在classesBackup，直接插入数据
        insert into classesBackup select userid, username from deleted;
    else
        --不存在classesBackup创建再插入
        select * into classesBackup from deleted;
    print '备份数据成功！';
go
--
--不显示影响行数
--set nocount on;
delete [user] where userid = 5;
--查询数据
select * from [user];
select * from classesBackup;
---
if(OBJECT_ID('tri_delbbsid','tr') is not null)
	drop trigger  tri_delbbsid;
go
---raiserror
create trigger  tri_delbbsid 
on bbs with encryption --加密触发器
for update
as
if update(bbsid)
	begin
	 raisError('系统提示：ID不能修改！', 16, 11);
	 rollback tran;
	end
--test
update bbs set bbsid=19 where bbsid=5;

--instead 触发器  先删除帖子 再删除 用户 备份用户信息 不备份帖子信息 
if(OBJECT_ID('tri_userinstead','tr') is not null)
	drop trigger  tri_userinstead;
go
create trigger tri_userinstead 
on  [user]
instead of delete
as
declare @userid int, @username varchar(50),@bbsid int,@bbscount int,@index int;
select @userid=u.userid,@username=u.username from [user] u,deleted d where u.userid=d.userid;
set @bbscount =( select count(*) from bbs where bbsower = @userid);
set @index = 0;
print '找到'+@username+'的帖子'+convert(varchar,@bbscount)+'条';
while(@index<@bbscount)
	begin
		select @bbsid=bbsid from bbs where @userid=bbsower;
		print '正在删除'+@username+'的帖子：'+convert(varchar,@bbsid);
		waitfor delay '00:00:01' ---进度条效果
		
		set @index=@index+1;
	end


----test
delete [user] where userid=1;
select * from [user];
--- 启用、禁用触发器
--禁用触发器
disable trigger tri_userinstead on [user];
--启用触发器
enable trigger tri_userinstead on [user];
  ---# 查询创建的触发器信息
--查询已存在的触发器
select * from sys.triggers;
select * from sys.objects where type = 'TR';

--查看触发器触发事件
select te.* from sys.trigger_events te join sys.triggers t
on t.object_id = te.object_id
where t.parent_class = 0 and t.name = 'tgr_valid_data';

--查看创建触发器语句
exec sp_helptext 'tgr_message';

---test  记录日志
if (object_id('log4user', 'U') is not null)
    drop table log4user
go
create table log4user (
logStream int identity(1,1) primary key,
action varchar(20),
createDate datetime default getDate() ---默认 就是当前的时间
)

if(OBJECT_ID('tri_log4user','tr') is not null)
	drop trigger  tri_log4user;
go
create trigger tri_log4user 
on [user]
for insert,update,delete
as
 if ((exists (select 1 from inserted)) and (exists (select 1 from deleted)))
    begin
        insert into log4user(action) values('updated');
    end
  else if (exists (select 1 from inserted) and not exists (select 1 from deleted))
    begin
        insert into log4user(action) values('insert');
    end
 else
    begin
         insert into log4user(action) values('delete');
    end 
 select * from log4user;
    
   ----test
    insert into [user] values('麦克斯韦');
    update [user] set username  = '李凯' where username='麦克斯韦';
    
    ---分页SQl
    ---① 利用top 和not  in 
    
    select top 2 * from [user] where userid not in (select top(1*2) userid from [user])  
    
    ----②利用MAX
    select top 2 * from  [user] where userid >(select max(t.userid) from (select top(1*2) userid from [user] order by userid)t);
    ---③利用ROWNUMBER  从0-4条记录
    SELECT *
	FROM
	(
	SELECT *,ROW_NUMBER() OVER (ORDER BY userid) AS RowNo
	FROM [user]
	) AS A
	WHERE RowNo >= 0 and RowNo < = 4;
	
	
	----存储过程
create table bankMoney (
id int identity(1,1) primary key,
userid varchar(20),
sex varchar(20),
Money int 
)
insert into bankMoney values('lisi','男',30);

----test
if(OBJECT_ID('sp_query_bankMoney','P') is not null)
	drop procedure  sp_query_bankMoney;
create procedure sp_query_bankMoney
as
select * from bankMoney 

exec sp_query_bankMoney

drop procedure  insert_bank;
Create procedure insert_bank @param2 varchar(20),@param3 varchar(20),@param4 int,@param5 int output
with encryption ---------加密
as
insert into bankMoney (userID,sex,Money)
Values(@param2,@param3, @param4)
select @param5=sum(Money) from bankMoney where userID='Zhangsan'

declare @total_price int
exec insert_bank 'Zhangsan','男',100,@total_price output
print '总余额为'+convert(varchar,@total_price)

----游标
go
declare @userid int ,@username varchar(20);
declare c_showuser cursor  for select * from [user]
open c_showuser
fetch next from c_showuser into @userid,@username;
while(@@FETCH_STATUS=0)
begin
print convert(varchar,@userid)
print @username;
fetch next from c_showuser into @userid,@username;
end
close c_showuser 
deallocate c_showuser

----存储过程 显示全部数据

if(OBJECT_ID('pro_showuser','P') is not null)
	drop procedure  pro_showuser;

go
create procedure pro_showuser  
as
declare @userid int ,@username varchar(20);
declare c_showuser cursor  for select * from [user]
open c_showuser
fetch next from c_showuser into @userid,@username;
while(@@FETCH_STATUS=0)
	begin
		print convert(varchar,@userid)
		print @username;
		fetch next from c_showuser into @userid,@username;
	end
close c_showuser 
deallocate c_showuser

exec pro_showuser

---带传入参数的存储 过程
WITH RECOMPILE ---不缓存
create procedure pro_inp (@startid int,@endid int)
as
select * from [user] where userid between @startid and @endid;

go
exec pro_inp 1,3
--带输出参数的
if (object_id('proc_putu', 'P') is not null)
    drop proc proc_putu
go
create proc proc_putu(
    @id int, --默认输入参数
    @bbstopic varchar(20) out, --输出参数
    @bbscontent varchar(20) output--输入输出参数
)
as
    select @bbstopic = bbstopic, @bbscontent = bbsccontent  from bbs where bbsid = @id and bbsccontent  = @bbscontent;
go

-- 
declare @id int,
        @name varchar(20),
        @temp varchar(20);
set @id = 1; 
set @temp = 'lallalalal';
exec proc_putu @id, @name out, @temp output;
select @name, @temp;
print @name + '#' + @temp;

if (object_id('pro_out4user', 'P') is not null)
    drop proc pro_out4user
go
create proc pro_out4user(@userid int ,@username varchar(20) output)
as
	select @userid=userid,@username=username from [user] where userid=@userid and username=@username;
	select userid,username from [user] where userid=@userid and username=@username;

go
declare @id int,@name varchar(20);
set @id=11;
set @name='麦克斯韦'
exec pro_out4user @id, @name output;