MSSQL 触发器

---触发器练习

--在SQL Server里面也就是对某一个表的一定的操作，触发某种条件，从而执行的一段程序
 CREATE TABLE Student 
 ( 
    SID INT PRIMARY KEY       --学号 
 ) 

 CREATE TABLE BorrowRecord
    (
      --学生借书记录表 
      BorrowRecord INT IDENTITY(1, 1) ,       --流水号   
      StudentID INT ,                    --学号 
      BorrowDate DATETIME ,                --借出时间 
      ReturnDAte DATETIME,                --归还时间 
       
      
    )
--EXEC sp_rename 'student.sid', 'studentid', 'COLUMN'
INSERT BorrowRecord VALUES(1,GETDATE(),GETDATE())
INSERT BorrowRecord VALUES(1,GETDATE(),GETDATE())

INSERT BorrowRecord VALUES(2,GETDATE(),GETDATE())

INSERT BorrowRecord VALUES(3,GETDATE(),GETDATE())

------------Insert----------------------------
if (object_id('tr_insert', 'TR') is not null)    
drop trigger tr_insert
go
CREATE TRIGGER tr_insert
ON BorrowRecord
FOR insert
as
DECLARE @studentid INT;
SELECT @studentid=studentid FROM INSERTED
INSERT INTO student VALUES(@studentid) ;
go
INSERT BorrowRecord VALUES(11,GETDATE(),GETDATE())
----------------Update---------------------------
if (object_id('truStudent', 'TR') is not null)    
drop trigger truStudent
go
create TRIGGER truStudent 
    ON student --在Student表中创建触发器 
    FOR UPDATE --为什么事件触发
AS
    IF UPDATE(studentid) 
        BEGIN
            UPDATE  BorrowRecord
            SET     StudentID = i.studentid
            FROM    BorrowRecord br ,
                    DELETED d ,
                    INSERTED i
            WHERE   br.StudentID = d.studentid

        END
--实例
UPDATE student SET studentid=2 WHERE studentid=7
SELECT * FROM BorrowRecord
SELECT * FROM student
----------------delete---------------------------
if (object_id('deletri', 'TR') is not null)    
drop trigger deletri
go
CREATE TRIGGER deletri
ON student
FOR DELETE
AS 
DELETE BorrowRecord FROM BorrowRecord br,DELETED d
WHERE br.StudentID=d.studentid
--实例
DELETE FROM student WHERE studentid=7
SELECT * FROM BorrowRecord