触发器

--触发器模型
CREATE TRIGGER 触发器名称 ON 作用的表或视图名称 FOR 操作类型INSERT/DELETE/UPDATE
AS
 SQL语句
RETURN

--触发器示例

CREATE TRIGGER MY_TRIGGER ON MY_TABLE FOR INSERT,DELETE,UPDATE
AS
 DECLARE @insert_count INT
 DECLARE @delete_count INT
 SELECT @insert_count=COUNT(*) FROM INSERTED
 SELECT @delete_count=COUNT(*) FROM DELETED
 --插入操作
 IF @insert_count>0 AND @delete_count=0 
 BEGIN
 INSERT INTO NEW_TABLE  SELECT * FROM INSERTED
 END
 --删除操作
 IF @insert_count=0 AND @delete_count>0 
 BEGIN
 DELETE FROM NEW_TABLE WHERE ID IN (SELECT ID FROM DELETED)
 END
 --更新操作
 IF @insert_count=@delete_count AND @insert_count>0 
 BEGIN
 DELETE FROM NEW_TABLE WHERE ID IN (SELECT ID FROM DELETED)
 INSERT INTO NEW_TABLE SELECT * FROM INSERTED
 END
RETURN

--查看存储过程

sp_helptext 触发器名称

--修改存储过程

ALTER TRIGGER 触发器名称 ON 作用的表或视图名称 FOR 操作类型INSERT/DELETE/UPDATE
AS
 SQL语句
RETURN

--删除存储过程

DROP TRIGGER 过程名