【数据库SQL】批量更新促发器游标的方法

--停止:
ALTER  TABLE  table_example  DISABLE  TRIGGER  trig_example  


--启用:
ALTER  TABLE  table_example  ENABLE  TRIGGER  trig_example


-- table_example 表名     trig_example 触发器名




--停止:此表所有触发器
ALTER  TABLE  table_example  DISABLE  TRIGGER  all


--启用:此表所有触发器

ALTER  TABLE  table_example  ENABLE  TRIGGER  all



if exists(select name from sysobjects where name='tr_bd_item_infommsy_delete' and type='TR')
drop trigger tr_bd_item_infommsy_delete
go
create trigger tr_bd_item_infommsy_delete on t_bd_item_info with encryption 
after delete 
as
begin
--用游标,一个一个处理 
DECLARE @fID varchar(100) 
DECLARE tmpGoods CURSOR for select item_no from deleted 
OPEN tmpGoods 
FETCH tmpGoods into @fID 
WHILE @@FETCH_STATUS=0 
BEGIN 
--操作语句 update T1 set T1_2='32h4' where ID=@fID 
delete  from hbposev9app..t_bd_item_info 
where hbposev9app..t_bd_item_info.item_no=@fID


FETCH  tmpGoods into @fID 
END 
CLOSE tmpGoods 
DEALLOCATE tmpGoods 
END   
 
--------------------------------------
CREATE TRIGGER [dbo].[Up_T] 
ON [dbo].[T1] 
FOR UPDATE 
AS 
begin 
if update(T1_1) 
BEGIN 
--用游标,一个一个处理 
DECLARE @fID int 
DECLARE tmpGoods CURSOR for select ID from deleted 
OPEN tmpGoods 
FETCH NEXT FROM tmpGoods into @fID 
WHILE @@FETCH_STATUS=0 
BEGIN 
--操作语句 
update T1 set T1_2='32h4' where ID=@fID 
FETCH NEXT FROM tmpGoods into @fID 
END 
CLOSE tmpGoods 
DEALLOCATE tmpGoods 
END 

END



-----------

---创建触发器插入操作【tr_aatestbale_inert触发器名,aatestbale原有表名,bbtestbale插入新的表】
--案例①:当aatestbale有插入数据时,就会更新另外定义的bbtestbale表的数据
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'tr_aatestbale' AND type = 'TR')
   DROP TRIGGER tr_aatestbale
   
create  TRIGGER  tr_aatestbale  --修改触发器alterTRIGGER
   ON  aatestbale
   AFTER INSERT--如是更新时除非此触发器AFTER update ,删除AFTER delete (例如是插入时会促发此触发器)
AS 
BEGIN
SET NOCOUNT ON;
----先盘点是否有插入重复,如没有则促发此触发器时,
    if not exists (select 1 from bbtestbale where item_no = (select item_no from inserted))
    begin
insert into bbtestbale(item_no,item_subno,item_name)
select item_no,item_subno,item_name from inserted 
end

END




--select item_no,item_subno,item_name into aatestbale from t_bd_appitem_info
--select item_no,item_subno,item_name into bbtestbale from t_bd_appitem_info
--select * from aatestbale  
--select * from bbtestbale


--insert into aatestbale(item_no,item_subno,item_name)
--select '0000002','pl','tsta' from t_bd_item_info  where item_no='0000002'


--原始
ALTER  TRIGGER  [iss].[QAQuestion_insert]
   ON  [iss].[QAQuestion]
   AFTER INSERT
AS 
BEGIN
SET NOCOUNT ON;


    if not exists (select 1 from QAQuestionEffect where RecNo = (select RecNo from inserted))
    begin
insert into QAQuestionEffect([RecNo]
 ,[FirstSubmitDate]
 ,[ValidFirstSubmitDate]
 ,[Expecthandledate]
 ,[Status]
 ,[effectstatus]
 ,[Handlenum]
 ,[HistoryTimeout]
 ,[TimeOutSwitch])
select [RecNo]
 ,[FirstSubmitDate]
 ,[FirstSubmitDate]
 ,dateadd(day,1,[FirstSubmitDate])
 ,'3'
 ,'1'
 ,'0'
 ,'0'
 ,'0' from inserted 
end
END

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值