T-SQL触发器

/*
 * 触发器: 是一种特殊的存储过程
 *   必须依附于表或者视图
 *   不能被显式调用
 *   当对表或视图执行insert,update,delete操作自动引发
 *
 * 优点:
 *  1.自动执行
 *  2.强化约束
 *  3.级联操作
 *
 * 类型:
 *  AFTER
 *  INSTEAD OF
 *
 * 引发触发器的操作
 *  INSERT
 *  UPDATE
 *  DELETE
 */
 
 --创建触发器
 --INSERT
 CREATE TRIGGER tri_insStudent
 ON student
 AFTER INSERT
 AS
 PRINT '有新生加入了';
 GO
 
 CREATE TRIGGER tri_CheckStuName
 ON student
 AFTER INSERT,UPDATE
 AS
 IF('李四' IN(SELECT StudentName FROM inserted))
 BEGIN
  PRINT '不收李四';
  ROLLBACK TRAN;
 END
 GO
 INSERT INTO Student VALUES(444,'123','李四',0,1,'13829292','xxx','1988-1-1','q@a.a',null);
 GO
 
 --DELETE
 CREATE TRIGGER tri_DelStudent
 ON Student
 AFTER DELETE
 AS
 IF('张三' IN(SELECT StudentName FROM deleted))
 BEGIN
  PRINT '不能删除张三';
  ROLLBACK TRAN;
 END
 GO
 
 --生成毕业学生表
 SELECT *
 INTO OldStudent
 FROM Student
 WHERE 1=2;
 GO
 
 CREATE TRIGGER tri_DelStu
 ON Student
 AFTER DELETE
 AS
 INSERT INTO OldStudent SELECT * FROM deleted;
 GO
 
 DELETE FROM Student WHERE StudentName='张三';
 DELETE FROM Student WHERE StudentNO=222;
 GO
 
--UPDATE
CREATE TRIGGER tri_updStudent
ON Student
AFTER UPDATE,INSERT
AS
 DECLARE @OLDDATE DATETIME;
 DECLARE @NEWDATE DATETIME;
 SELECT @OLDDATE=BornDate FROM deleted;
 SELECT @NEWDATE=BornDate FROM inserted;
 IF(ABS(DATEDIFF(YYYY,@OLDDATE,@NEWDATE))>5)
 BEGIN
  PRINT '修改年龄不能超过5';
  ROLLBACK TRAN;
 END
GO

UPDATE Student SET BornDate='2012-1-1' WHERE StudentNo=0;