SQL Server触发器

http://www.cnblogs.com/rainman/p/3675834.html#m7

查询以及查看表的触发器

select * from sysobjects where xtype='TR'

select text from syscomments where ID= object_ID(N'触发器名')

删除触发器：

drop trigger trigger_test_insert

触发器：Instead of和after两种

create trigger trigger_test_Delete
on XDSGR
instead of Delete
as
begin
if not exists(select * from WHITM, deleted where WHITM.MATNR = deleted.MATNR)
delete from XDSGR where XDSGR.MATNR in (select MATNR from deleted)
end

SELECT * FROM [dbo].[WHITM] WHERE MATNR='NC0BAS17Z00'

SELECT * FROM XDSGR

INSERT INTO XDSGR VALUES ('218','CS77','TW11','HEUF2003010','1','1','G')

DELETE FROM XDSGR WHERE MATNR IN('HEUF2003010')

 

ALTER trigger trigger_test_insert
on XDSGR
after insert
as
if (select WHITM.INSMK from WHITM, inserted where WHITM.MATNR = inserted.MATNR )='G'
begin
SELECT 'the goods is being processed' AS MESSAGE
PRINT 'the order cannot be committed'
rollback transaction --回滚，避免加入
end

INSERT INTO XDSGR VALUES ('218','CS77','TW11','SFHS05FR009','1','1','G')