sql 触发器

转自： 点击打开链接

启用/禁用触发器：

1 --禁用触发器
2 disable trigger trigger_Stu_InsteadOf on Student;    -- trigger_Stu_InsteadOf 触发器名称
3 --启用触发器
4 enable trigger trigger_Stu_InsteadOf on Student;    -- trigger_Stu_InsteadOf 触发器名称

查询已存在的触发器：

1 -- 查询已存在的触发器
2 select * from sys.triggers;
3 select * from sys.objects where type = 'TR';
4 select * from sysobjects where xtype='TR'

 1 -- sys.trigger_events 触发器事件对象视图
 2 select * from sys.trigger_events 
 3 
 4 -- 查看触发器触发事件对象    
 5 select a.type_desc,b.* from sys.trigger_events a 
 6 inner join sys.triggers b on a.object_id = b.object_id
 7 where b.name = 'trigger_Stu_Insert';
 8 
 9 -- 查询创建触发器的 T-SQL 文本
10 exec sp_helptext 'trigger_Stu_Insert'

if(OBJECT_ID('insert_into_agv_task') is not null)
drop trigger insert_into_agv_task
go

create trigger insert_into_agv_task
on IF_AGV
after insert
as

declare @from_station varchar(10);
declare @to_station varchar(10);
declare @line_code varchar(10);
declare @carry_number varchar(10);
declare @id uniqueidentifier

select @line_code=line_code,@carry_number=carry_number ,@id=id from inserted --查出车编码和产线编码--
select @to_station=card_num from card_num where card_num.line_code=@line_code


        insert into agv_task(id,from_station,to_station,car_mac,call_from,valid,receive_date,task_type) values(NEWID(),'0180',@to_station,@carry_number,'Scanning','y',GETDATE(),'IssueToLineByScan');


insert into IF_AGV(id,line_code,carry_number,line_flag,line_createdate) values(NEWID(),'SSB-10-2','AGV1','Y',GETDATE()); 	

select * from sysobjects where xtype='TR'

drop trigger insert_into_agv_task