触发器
create trigger tr_banj_insert
on t_banj
after insert
as
begin
declare @log varchar(50)
select @log='插入的记录:banj_mc=' +banj_mc+'banzr='+banzr from inserted --新数据
insert into t_log (biaomc,caoznr) values('t_banj',@log)
end
create trigger tr_banj_delete
on t_banj
after delete
as
begin
delete @log varchar(50)
select*from deleted --旧的数据
select @log='删除的记录:' +cast()+'banzr='+banzr from inserted
end
索引 视图
--创建索引 根据查询条件来建立
create index ind_xuns_xunh on dbo.t_xuns
(xunh)
select * from t_xuns where banj_id=1
--创建视图
create view v_st
as
select * from t_xuns
drop index view
游标
--定义游标
declare @banjid varchar(30)
declare @banjmc varchar(30)
--forward游标向前 scroll滚动的
declare cur cursor scroll dynamic for select banj_id,banj_mc from t_banj
--打开游标
open cur
fetch next from cur into @banjid,@banjmc
while @@FETCH_STATUS=0--判断是否取到数据
begin
fetch next from cur into @banjid,@banjmc
print('@banjid='[email protected] +'@banjmc'[email protected])
end
--关闭游标
close cur
--释放游标
deallocate cur
事务
--启动事务
begin tran t1
delete t_log where log_id=1 --转出钱操作
delete t_xuns where xueh ='201501'--转出钱的操作
--全局变量
if @@ERROR<>0
rollback tran t1
else
--提交事务
commit tran t1