【数据库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