触发器（菜鸟级）

create trigger C_vpss_FactoryStockInsert
--入库库存增
on vpss_FactoryStock_m
after insert
as
    declare   @ft_id   varchar(100)
    declare   @po_no   varchar(100)
    declare   @amount   varchar(100)
    select    @ft_id=ft_id,@po_no=po_no, @amount=amount   from   Inserted
    declare   @type_id   int
    select    @type_id=my_type   from   vpss_FactoryStock where ft_id=@ft_id
if exists(select 1 from vpss_FactoryStockInfo where po_no=@po_no and type_id=@type_id)
update vpss_FactoryStockInfo set amount=amount+@amount where po_no=@po_no and type_id=@type_id
else
INSERT   INTO vpss_FactoryStockInfo(po_no,amount,type_id)values(@po_no,@amount,@type_id)

create trigger C_vpss_FactoryStockUpdate
--入库库存改
on vpss_FactoryStock_m
after update
as
declare MyCURSOR cursor for
 select inserted.ft_id,inserted.po_no,inserted.amount as amountInsert,deleted.amount as amountDel from Inserted ,deleted where Inserted.[id]=deleted.[id]
 open MyCURSOR
    declare   @ft_id   varchar(100)
     declare   @po_no   varchar(100)
     declare   @amountInsert   int
    declare   @amountDel   int
     declare   @type_id   int
 fetch next from MyCURSOR into @ft_id,@po_no,@amountInsert,@amountDel
 while @@FETCH_STATUS=0
 begin
     select    @type_id=my_type   from   vpss_FactoryStock where ft_id=@ft_id
 print @type_id
 if exists(select 1 from vpss_FactoryStockInfo where po_no=@po_no and type_id=@type_id)
 update vpss_FactoryStockInfo set amount=amount-@amountDel+@amountInsert where po_no=@po_no and type_id=@type_id
 fetch next from MyCURSOR into @ft_id,@po_no,@amountInsert,@amountDel
 end
close MyCURSOR
deallocate MyCURSOR

create trigger C_vpss_FactoryStockDelete
--入库库存删
on vpss_FactoryStock_m
after delete
as
declare MyCURSOR cursor for
 select ft_id,po_no,amount from deleted
 open MyCURSOR
    declare   @ft_id  varchar(100)
     declare   @po_no   varchar(100)
     declare   @amount   varchar(100)
     declare   @type_id   int
 fetch next from MyCURSOR into @ft_id,@po_no,@amount
 while @@FETCH_STATUS=0
 begin
     select    @type_id=my_type   from   vpss_FactoryStock where ft_id=@ft_id
 if exists(select 1 from vpss_FactoryStockInfo where po_no=@po_no and type_id=@type_id)
 update vpss_FactoryStockInfo set amount=amount-@amount where po_no=@po_no and type_id=@type_id
 fetch next from MyCURSOR into @ft_id,@po_no,@amount
 end
close MyCURSOR
deallocate MyCURSOR

 

 

--查看触发器
select * from sysobjects where xtype='TR'

create trigger tri_Modi on ta
for delete ,insert ,update
as
if not exists(select 1 from deleted)               
   insert tb(ID,[name])select * from inserted --insert
else if exists(select 1 from inserted)and exists(select 1 from deleted)
   update tb set tb.[name]=inserted.[name] from inserted where tb.id=inserted.id --update  
else
   delete b from tb b ,deleted d where b.id=d.id --del  
go
insert ta select 1,'a'
insert ta select 2,'b'
select * from tb
/*
ID          Name       Sex
----------- ---------- ----
1           a          NULL
2           b          NULL
*/
update ta set [name]='c' where id=2
select * from tb
/* ID Name Sex ----------- ---------- ---- 1 a NULL 2 c NULL */
delete ta where id=1
select * from tb
/*
ID          Name       Sex
----------- ---------- ----
2           c          NULL
*/
 
set nocount on
if object_id('ta')is not null drop table ta
go
create table ta(ID int ,[Name] varchar(10))
if object_id('tb')is not null drop table tb
go
create table tb(ID int ,[Name] varchar(10),Sex varchar(2))
go
if object_id('tri_Modi')is not null drop trigger tri_Modi go
create trigger tri_Modi on ta
for delete ,insert ,update
as
if not exists(select 1 from deleted)               
   insert tb(ID,[name])select * from inserted --insert
else if exists(select 1 from inserted)and exists(select 1 from deleted)
   update tb set tb.[name]=inserted.[name] from inserted where tb.id=inserted.id --update  
else
   delete b from tb b ,deleted d where b.id=d.id --del  
go
insert ta select 1,'a'
insert ta select 2,'b'
select * from tb
/*
ID          Name       Sex
----------- ---------- ----
1           a          NULL
2           b          NULL
*/
update ta set [name]='c' where id=2
select * from tb
/* ID Name Sex ----------- ---------- ---- 1 a NULL 2 c NULL */
delete ta where id=1
select * from tb
/*
ID          Name       Sex
----------- ---------- ----
2           c          NULL
*/