sql触发器与游标解析

select * from tbuser
--DDL
go
create trigger mysaft
on database
for drop_table,alter_table
as
print '触发器mysaft阻止了你的修改表'
rollback
go


select * from tbuser
----给表添加一个列by wzf 2014-12-15
alter table tbuser add isEnable bit
---disabel
go
disable trigger mysaft on database
enable  trigger mysaft on database
----
go
---删除一个列
alter table tbuser drop column isEnable


----修改一个列的类型
alter table tbuser alter column isEnable money




----DML语句
select * from tbuser


drop table tbuser_history
--查询tbuser表中的数据,创建并且插入表tbuser_history表中
select * into tbuser_history from tbuser


insert into tbuser_history values
('wusong','武松','F','1879-10-2',
'wusong',GETDATE(),'武松打虎',1)




---初始化表，
truncate table tbuser_history
--DML
---插入的用户的年龄必须大于18岁
go
create trigger afterinst
on tbuser_history
after insert
as
declare @age int
select @age = datediff(yyyy,birthday,getdate()) from inserted
if(@age<18)
begin
print '用户的年龄太小了'
rollback
end
go
insert into tbuser_history values
('wuson1g','武松1','F','1990-10-2',
'wusong1',GETDATE(),'武松打虎',1)
select * from tbuser_history


----after update
alter table tbuser add salary money
alter table tbuser_history add salary money


select * from tbuser
select * from tbuser_history
go
--员工薪水增加触发器，薪水必须低于1W并且增加之后薪水不能超过12000
create trigger tbusercheckupdate
on tbuser
after update
as
declare @salary money
--deleted中装的是原来的薪水
select @salary = salary from deleted
if(@salary > 10000)
begin
print '薪水已经过万不能增加'
rollback
end
--企图修改后的薪水
select @salary = salary from inserted
if(@salary >= 12000)
begin
print '薪水已经过12000修改失败'
rollback
end

select * from tbuser


update tbuser set salary += 7000 where id = 1
-----
--如果对tbuser表进行删除操作，则删除数据以外并且将
---需要删除的行查入到tbuser_history
go
create trigger istof
on tbuser
instead of delete
as
declare @id int
select @id=id from deleted
insert into tbuser_history(loginid,realname,sex,birthday,loginpwd,createdt,memo,isenable,salary)  
select loginid,realname,sex,birthday,loginpwd,createdt,memo,isenable,salary from deleted
delete from tbuser where id = @id
---
drop trigger istof
delete from tbuser where id = 2


select * from tbuser
select * from tbuser_history




----------游标
declare usercursor  cursor scroll
for
select * from tbuser
---打开游标
open usercursor
---取值
fetch first from usercursor
while(@@FETCH_STATUS=0)
begin
fetch next from usercursor
end
--关闭游标 
close usercursor
--销毁游标
deallocate usercursor




select * from tbuser_history
go
----将年龄小于26岁的员工 薪水添加1000
declare @birth date
--定义游标变量
declare @changeSalary cursor
--给游标变量赋值
set @changeSalary = cursor scroll dynamic
for
select birthday from tbuser_history
--打开游标
open @changeSalary
--取游标中的值，并且赋给变量
fetch first from @changeSalary into @birth
while(@@FETCH_STATUS = 0)
begin
if(datediff(YYYY,@birth,GETDATE()) < 26)
--更改游标当前行的数据
update tbuser_history set salary += 1000 
where current of @changeSalary
--datediff(YYYY,birthday,GETDATE())
--游标下行 
fetch next from @changeSalary into @birth

end
close @changeSalary
deallocate @changeSalary

go

-----游标

create trigger safety
on database
for drop_table ,alter_table
as
print '触发器禁止删除或者修改表'
rollback


drop table employeeHistory


--
select * from employee
--新增员不能小于18岁
go
create trigger t_ckempage
on employee
for update
as
declare @age date
select @age = empbirth from inserted
if(datediff(YYYY,@age,getdate())<18)
begin
print '员工年龄小于18周岁'
rollback
end
else
print '员工年龄合法,允许插入'



select * from employee
update employee set empbirth = '1998-10-1' where empno = 'E002'
--禁用
go
disable trigger t_ckempage on employee
go
enable  trigger t_ckempage on employee


------
--游标
--1.定义游标,可滚动
go
declare my_cursor cursor scroll
for
select * from employee
---2.使用游标
go
--1.打开
open  my_cursor
--2.提取数据,提取第一行
fetch first from my_cursor
--循环取值
while(@@FETCH_STATUS=0)
begin
fetch next from my_cursor
end
--3.关闭游标
close my_cursor
--4.销毁资源
deallocate my_cursor

----where current of 修改数据
go
declare emp_cursor cursor scroll dynamic
for
select empno,empname,empbirth from employee


open emp_cursor
declare 
@empno varchar(10),
@empname varchar(10),
@emphire date
fetch first from emp_cursor into @empno,@empname,@emphire
while(@@FETCH_STATUS=0)
begin
---入职超过10年的员工+500
if(DATEDIFF(yyyy,@emphire,getdate())>=10)
update employee set salary += 500 where current of emp_cursor
--提取下一行
fetch next from emp_cursor into @empno,@empname,@emphire
end
close  emp_cursor
deallocate emp_cursor
go
select * from employee


---游标可以先声明在赋值
go
declare @mycursor cursor
set @mycursor = CURSOR 


----fetch absolute @rowcount from auth_cur 
 --将变量@rowcount标识的行设置为当前行