欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

初探sqlserver触发器 博客分类: T-SQL编程 sqlserver触发器新人 

程序员文章站 2024-03-11 15:13:07
...
触发器对表进行插入、更新、删除的时候会自动执行的特殊存储过程。触发器一般用在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;